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

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

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

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

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

Полная сортировка
Операторы оконного агрегата организуют строки в заданном порядке (через PARTITION BY + ORDER BY), а затем для каждой строки навешивают фрейм и пересчитывают агрегат. При терабайтах данных это сортировок на диске не избежать — и именно они съедают львиную долю времени.
Большие фреймы по умолчанию
Если вы не ограничили фрейм, СУБД использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, т.е для каждой строки просматривает все предшествующие ей.
Параллельность и ресурсы
Не все движки одинаково хорошо умеют параллелить оконные функции, и часто один большой фрейм становится точкой сериальной блокировки.

В результате — долгие 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:

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

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
)

Разница префиксов
Для скользящего окна в 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;

Альтернатива через 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. Идея простая:

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

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);

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

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);

Скользящее окно по 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’а. Алгоритм верификации:

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

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)

Сравнение 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;

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

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

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

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

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

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

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

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

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

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

Публикации

Информация