Как использовать оконные функции SQL для расчёта скользящего среднего и процентилей?

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

Оконные функции позволяют считать агрегаты (среднее, сумму, процентиль) по «окну» строк, связанному с текущей строкой, не сворачивая результат в одну строку на группу, как это делает GROUP BY; для скользящего среднего используют AVG(...) OVER (ORDER BY ... ROWS BETWEEN ... AND CURRENT ROW).

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

конструкция OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...) определяет для каждой строки своё окно соседних строк, по которому и считается функция; ROWS считает окно по числу строк, RANGE — по значениям (например, по дате).

Пример кода

SQL-запросКопировать

-- PostgreSQL: 7-дневное скользящее среднее заказов
SELECT
  order_date,
  daily_orders,
  AVG(daily_orders) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM daily_order_counts
ORDER BY order_date;

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

• На практике. скользящее среднее полезно для сглаживания шумных временных рядов перед визуализацией трендов (например, 7-дневное скользящее среднее для устранения недельной сезонности). Для расчёта процентилей внутри группы на лету используют PERCENTILE_CONT(...) OVER (PARTITION BY ...) (поддержка синтаксиса отличается между PostgreSQL, BigQuery, ClickHouse — стоит проверять документацию конкретной СУБД). Важно явно указывать порядок строк (ORDER BY) внутри окна, иначе результат может быть недетерминированным. Дополнительно стоит проверять согласованность результата оконной функции с эквивалентным расчётом через подзапрос с группировкой на небольшом контрольном наборе данных — расхождение сигнализирует об ошибке в определении окна.
• Пример. Sanity check: значение rolling_7d_avg для самой первой строки будет равно самому daily_orders (окно ещё не набрало 7 строк) — это ожидаемое краевое поведение, которое стоит явно обсудить при интерпретации начала ряда.

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

• Частые ошибки. путать ROWS и RANGE, что при пропусках в датах даёт разные окна; забывать PARTITION BY, из-за чего окно считается по всей таблице вместо нужной группы; использовать оконные функции в WHERE напрямую — большинство СУБД это не поддерживает, нужен подзапрос или CTE. Ещё одна ошибка — использовать оконные функции без понимания разницы между UNBOUNDED PRECEDING и фиксированным числом предыдущих строк, что может незаметно менять ширину окна на границах периода.