Оконные функции SQL: продвинутые техники и фреймы

Большинство разработчиков знакомы с ROW_NUMBER(), RANK() и SUM() OVER (PARTITION BY ...). Но оконные функции в PostgreSQL гораздо мощнее — в их основе лежит спецификация фрейма, которая открывает возможности для скользящих агрегатов, накопительных итогов и сложной аналитики. Разберём это подробно.

Анатомия оконной функции

Полный синтаксис выглядит так:

функция() OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN начало AND конец
)

Последняя строка — это и есть спецификация фрейма. Именно она определяет, какие строки попадают в «окно» для каждой строки результата.

ROWS vs RANGE vs GROUPS

Существуют три режима фрейма:

ROWS работает с конкретными строками — N строк до текущей, N строк после.

RANGE работает со значениями. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW означает «все строки, у которых ORDER BY значение не меньше текущего минус 7». При этом тип данных должен поддерживать вычитание.

GROUPS — менее известный режим, добавленный в PostgreSQL 11. Считает не строки, а группы строк с одинаковым значением.

-- ROWS: ровно 3 предыдущие строки
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS rolling_4day
FROM daily_revenue;

-- RANGE: все строки за последние 7 дней
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7day
FROM daily_revenue;

Разница принципиальная: если в таблице нет записи за какой-то день, ROWS BETWEEN 6 PRECEDING возьмёт 6 предыдущих имеющихся строк, а RANGE BETWEEN INTERVAL '6 days' PRECEDING корректно учтёт календарный период.

Ключевые граничные значения фрейма

UNBOUNDED PRECEDING  -- от самого начала раздела
N PRECEDING          -- N строк/значений назад
CURRENT ROW          -- текущая строка
N FOLLOWING          -- N строк/значений вперёд
UNBOUNDED FOLLOWING  -- до самого конца раздела

Пример накопительной суммы и суммы по всему разделу одновременно:

SELECT
  category,
  month,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY category
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative,
  SUM(revenue) OVER (
    PARTITION BY category
  ) AS total_in_category
FROM monthly_revenue;

FIRST_VALUE и LAST_VALUE: частая ловушка

LAST_VALUE почти всегда возвращает не то, что ожидают. Причина — фрейм по умолчанию.

Когда указан ORDER BY, фрейм по умолчанию равен RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это означает, что LAST_VALUE видит только строки до текущей включительно, а не весь раздел.

-- Неверно: LAST_VALUE вернёт текущую строку, а не последнюю в разделе
SELECT
  product_id,
  sale_date,
  LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date) AS wrong_last
FROM sales;

-- Верно: явно указываем фрейм до конца раздела
SELECT
  product_id,
  sale_date,
  FIRST_VALUE(sale_date) OVER w AS first_sale,
  LAST_VALUE(sale_date)  OVER w AS last_sale
FROM sales
WINDOW w AS (
  PARTITION BY product_id
  ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Синтаксис WINDOW w AS (...) позволяет определить окно один раз и ссылаться на него по имени — удобно, когда одно и то же окно используется несколько раз.

EXCLUDE: исключаем строки из фрейма

PostgreSQL поддерживает секцию EXCLUDE (стандарт SQL:2011):

EXCLUDE NO OTHERS      -- ничего не исключать (по умолчанию)
EXCLUDE CURRENT ROW    -- исключить текущую строку
EXCLUDE GROUP          -- исключить текущую группу (одинаковые значения ORDER BY)
EXCLUDE TIES           -- исключить строки-«ровесники», но не текущую

Практический пример: скользящее среднее без текущей точки (полезно при обнаружении аномалий):

SELECT
  sensor_id,
  measured_at,
  value,
  AVG(value) OVER (
    PARTITION BY sensor_id
    ORDER BY measured_at
    ROWS BETWEEN 5 PRECEDING AND 1 FOLLOWING
    EXCLUDE CURRENT ROW
  ) AS avg_neighbors
FROM sensor_readings;

NTILE: равномерное разбиение на группы

NTILE(n) делит строки раздела на n приблизительно равных групп:

SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_stats;
-- quartile = 1 → топ 25% покупателей

Скользящее среднее для временного ряда

Практический пример — сглаживание дневной выручки с окном 7 дней:

WITH daily AS (
  SELECT
    DATE_TRUNC('day', created_at) AS day,
    SUM(amount)                   AS revenue
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '90 days'
  GROUP BY 1
)
SELECT
  day,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) AS ma7,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2
  ) AS ma30
FROM daily
ORDER BY day;

Итоги

• Используйте ROWS когда вам важны физические строки, RANGE — когда нужно работать с диапазонами значений.
• Всегда явно указывайте фрейм для LAST_VALUE, иначе получите неожиданный результат.
• Именованные окна (WINDOW w AS (...)) делают код чище при повторном использовании.
• EXCLUDE CURRENT ROW удобен для сравнения точки с её «соседями».

Освоить оконные функции на практике можно в нашем тренажёре SQL — там есть задачи разного уровня с мгновенной проверкой.