Чем отличается оконная рамка ROWS BETWEEN 2 PRECEDING AND CURRENT ROW от обычного PARTITION BY без указания рамки, и зачем нужно скользящее среднее?

Короткий ответ

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ограничивает окно тремя строками (текущей и двумя предыдущими по порядку сортировки) для расчёта, например, скользящего среднего, тогда как без указания рамки при наличии ORDER BY по умолчанию используется расширяющееся окно «от начала до текущей строки».

Как это работает подробнее

явная рамка ROWS BETWEEN n PRECEDING AND CURRENT ROW задаёт скользящее окно фиксированного размера, которое «едет» вместе с текущей строкой, тогда как без рамки (но с ORDER BY) окно растёт с каждой следующей строкой — это принципиально разные вычисления, хотя обе используют одну и ту же функцию, например AVG.

Пример кода

question-41.sqlКопировать

SELECT day, daily_amount,
       AVG(daily_amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_sales
ORDER BY day;

Что использовать на практике

• скользящее среднее (например, за последние 7 дней) сглаживает дневные колебания и шум, помогая увидеть тренд, но важно явно договориться о размере окна и о том, что для первых N-1 строк ряда окно будет «неполным» (содержать меньше точек, чем задано), если не предусмотреть это отдельно. Проверка: для 8-й по порядку строки вручную усреднить daily_amount этой и 6 предыдущих строк и сравнить с ma_7d.

Подводные камни

• путают ROWS и RANGE — при RANGE несколько строк с одинаковым значением сортировки считаются «на одной границе» и могут попасть в окно все сразу, что для скользящего среднего по дням с дублирующимися датами даёт иной результат, чем ожидалось; не проверяют, что в ряду нет пропущенных дат, из-за чего «скользящее среднее за 7 дней» на самом деле может усреднять данные за более длинный календарный период при наличии пропусков.