Скользящие метрики без тормозов: SQL

Привет, Хабр!

Скользящие метрики — это must-have в аналитике, но даже простые на первый взгляд вычисления могут парализовать вашу СУБД. Сегодня рассмотрим, почему стандартные оконные функции зачастую тормозят, как их можно оптимизировать через явные фреймы, когда выгоднее писать агрегаты вручную, как использовать предагрегированные таблицы и как проверять корректность расчётов на границах окон.

Почему стандартные оконные функции тормозят на больших данных

Условимся сразу: оконные функции SUM() OVER (…), AVG() OVER (…) и им подобные удобны, но за удобство приходится расплачиваться производительностью. Основная проблема — сортировка и сканирование всей партиции:

1. Полная сортировка
   Операторы оконного агрегата организуют строки в заданном порядке (через PARTITION BY + ORDER BY), а затем для каждой строки навешивают фрейм и пересчитывают агрегат. При терабайтах данных это сортировок на диске не избежать — и именно они съедают львиную долю времени.

2. Большие фреймы по умолчанию
   Если вы не ограничили фрейм, СУБД использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, т.е для каждой строки просматривает все предшествующие ей.

3. Параллельность и ресурсы
   Не все движки одинаково хорошо умеют параллелить оконные функции, и часто один большой фрейм становится точкой сериальной блокировки.

В результате — долгие EXPLAIN-планы.

Как работает оптимизация оконных агрегатов

Суть ускорения оконных агрегатов в явном ограничении размера фрейма. Два модификатора:

• ROWS BETWEEN X PRECEDING AND Y FOLLOWING — жестко по количеству строк.

• RANGE BETWEEN X PRECEDING AND Y FOLLOWING — по значению.

Пример: трёхдневное скользящее среднее по столбцу expense:

SELECT    date,   expense,   ROUND(     AVG(expense)      OVER (       ORDER BY date       ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING     )   , 2) AS moving_avg_3 FROM expenses ORDER BY date;

Здесь каждый фрейм отбирает ровно 3 строки (текущую, предыдущую и следующую) — никакого лишнего сканирования всего периода.

Если же нужен временной интервал, допустим 7 дней, используют RANGE:

SELECT   date,   SUM(amount)      OVER (       ORDER BY date        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW     ) AS sum_7d FROM transactions;

Так вы говорите СУБД: не лазь ни за какими другими строками, а только в пределах семи дней.

Когда строить агрегаты руками через LAG, SUM + CASE

Иногда и фреймы не спасают: хочется минимизировать сортировки и уйти в инкрементальные или фактические вычисления без window-движка. Здесь уже нужны LAG() + SUM() OVER + CASE:

1. Префиксная сумма
   Вычисляем нарастающий итог без удержания большого окна:

   WITH prefix AS (   SELECT     user_id,     date,     SUM(amount)        OVER (         PARTITION BY user_id          ORDER BY date          ROWS UNBOUNDED PRECEDING       ) AS prefix_sum   FROM transactions )

2. Разница префиксов
   Для скользящего окна в 7 дней достаточно вычесть префикс 7-дневной давности:

   SELECT   user_id,   date,   prefix_sum      - COALESCE(         LAG(prefix_sum, 7)            OVER (PARTITION BY user_id ORDER BY date)       , 0       ) AS sum_last_7d FROM prefix;

3. Альтернатива через CASE
   Если нужен кастомный флаг начала группы:

   WITH flagged AS (   SELECT     *,     CASE        WHEN LAG(date) OVER (PARTITION BY user_id ORDER BY date)             < date - INTERVAL '7 days'        THEN 1 ELSE 0     END AS is_new_window   FROM transactions ), grp AS (   SELECT     *,     SUM(is_new_window)        OVER (PARTITION BY user_id ORDER BY date) AS window_id   FROM flagged ) SELECT   user_id,   date,   SUM(amount)      OVER (       PARTITION BY user_id, window_id        ORDER BY date        ROWS UNBOUNDED PRECEDING     ) AS sum_per_window FROM grp;

Такой подход часто выигрывает на RDBMS, где window-фреймы медленнее простых LAG() и SUM().

pre-aggregated таблиц для ускорения расчётов

Когда ежедневные скользящие метрики — must-have, а исходная таблица — на миллиарды строк, имеет смысл заводить summary tables. Идея простая:

1. Создаем агрегацию “по дням”

   CREATE TABLE daily_summary AS SELECT   user_id,   DATE_TRUNC('day', date) AS day,   SUM(amount) AS total_amount FROM transactions GROUP BY user_id, DATE_TRUNC('day', date);

2. Обновляем инкрементально

   INSERT INTO daily_summary SELECT   user_id,   DATE_TRUNC('day', date) AS day,   SUM(amount) FROM transactions WHERE date >= CURRENT_DATE - INTERVAL '1 day' GROUP BY user_id, DATE_TRUNC('day', date);

3. Скользящее окно по summary

   SELECT   day,   SUM(total_amount)      OVER (       PARTITION BY user_id        ORDER BY day        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW     ) AS moving_7d_summary FROM daily_summary;

При этом скорость запроса приближается к размеру summary-таблицы (1–5% от исходной) — выигрыш почти в 20–100×.

Как валидировать корректность скользящих расчётов без ошибок на границах окон

Границы окон — больной вопрос. Обычно ошибки проявляются на “первых” и “последних” строках partition’а. Алгоритм верификации:

1. Тестовый датасет
   Собираем маленькую таблицу с известными значениями:

   WITH sample AS (   VALUES     ('2025-04-01'::date, 10),     ('2025-04-02', 20),     ('2025-04-03', 30),     ('2025-04-04', 40) ) AS t(date, amount) 

2. Сравнение window vs manual
   SQL для параллельного расчёта:

   , win AS (   SELECT     date,     SUM(amount)        OVER (         ORDER BY date          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING       ) AS win_sum   FROM sample ), manual AS (   SELECT     date,     CASE       WHEN date = '2025-04-01' THEN (10 + 20)/2       WHEN date = '2025-04-04' THEN (30 + 40)/2       ELSE (LAG(amount) OVER (ORDER BY date) + amount + LEAD(amount) OVER (ORDER BY date)) / 3     END AS manual_sum   FROM sample ) SELECT    w.date, w.win_sum, m.manual_sum FROM win w JOIN manual m USING (date) WHERE w.win_sum <> m.manual_sum;

   Если результат пуст — расчёты корректны.

3. Автоматизация
   Заводим такой тест в CI (dbt test, pgTAP или любой framework) и проверяем при каждом изменении.

Кроме того, проверяйте NULL на краях и учитывайте unbounded фреймы: они могут расширяться за пределы данных .

Погрузиться в процесс разработки ПО, а именно — научиться учитывать цели бизнеса и формулировать технические требования к продукту, можно на онлайн-курсе «Системный аналитик. Basic».

На странице курса можно ознакомиться с подробной программой курса, а также посмотреть записи открытых уроков от преподавателей курса.