Оконные функции и рамки окна

Оконная функция считает агрегат по группе строк, но не сворачивает их: каждая строка остаётся на месте и получает значение, посчитанное по своему окну. Это закрывает целый класс задач, которые иначе требуют self-join: предыдущее значение, накопительный итог, номер строки в группе, доля от общего.

Окно не сворачивает строки

SELECT customer_id, order_id,
       count(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders;

Результат содержит все 8000 заказов, и в каждой строке стоит число заказов её клиента. GROUP BY customer_id дал бы 1512 строк. Выбирайте окно, когда нужны и детали, и агрегат одновременно.

Окно описывается тремя частями: PARTITION BY делит строки на группы, ORDER BY задаёт порядок внутри группы, рамка задаёт, какие строки группы участвуют в расчёте для текущей строки. Первые две знают все, про третью забывают, и она главный источник ошибок.

Ранжирование: три функции и разница в ничьих

SELECT city, n,
       row_number() OVER (ORDER BY n DESC) AS rn,
       rank()       OVER (ORDER BY n DESC) AS rnk,
       dense_rank() OVER (ORDER BY n DESC) AS dense
FROM (SELECT city, count(*) AS n FROM customers WHERE city IS NOT NULL GROUP BY 1) t;

Пока значения не повторяются, все три дают одно и то же. При ничьей row_number присвоит разные номера в произвольном порядке, rank даст одинаковый номер и пропустит следующий (1, 2, 2, 4), dense_rank одинаковый без пропуска (1, 2, 2, 3). Для «топ-3» с честным учётом ничьих нужен rank или dense_rank. Для «ровно одна строка на группу» нужен row_number, и тогда ORDER BY обязан быть однозначным: добавляйте уникальный ключ, иначе выбор среди равных будет случайным и разным от запуска к запуску.

lag и lead: соседняя строка без self-join

Рост выручки месяц к месяцу:

WITH monthly AS (
  SELECT date_trunc('month', created_at)::date AS month, sum(total_amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       lag(revenue) OVER (ORDER BY month) AS prev_revenue,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
             / lag(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;
-- 2025-01-01 | 2 643 141.70 | NULL         | NULL
-- 2025-02-01 | 4 822 920.86 | 2 643 141.70 | 82.5
-- 2025-03-01 | 7 152 989.37 | 4 822 920.86 | 48.3

У первой строки предыдущего значения нет, поэтому NULL. lag(x, 2) берёт строку на две позиции назад, третий аргумент задаёт значение по умолчанию. lead смотрит вперёд. В уроке про NULL мы уже сравнивали lag(city) с текущим городом через IS DISTINCT FROM: это типичный способ найти изменения в истории.

Одно и то же окно в нескольких функциях лучше объявить один раз: WINDOW w AS (ORDER BY month) после FROM и OVER w в каждой функции.

Накопительный итог и рамка по умолчанию

SELECT month, revenue, sum(revenue) OVER (ORDER BY month) AS cumulative
FROM monthly;

Как только в окне появляется ORDER BY, агрегат считается не по всей группе, а по рамке. Рамка по умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, от начала группы до текущей строки включительно. Так получается накопительный итог.

Слово RANGE здесь важно. В режиме RANGE «текущая строка» означает все строки с тем же значением сортировки. Если у двух заказов одна дата, у обоих накопительная сумма будет включать оба. В режиме ROWS рамка считается по физическим строкам, и у первого из двух заказов сумма ещё не включает второй. Разница проявляется только при повторах в ORDER BY, поэтому её редко замечают в тестах и часто на проде.

Последствие для first_value и last_value:

SELECT order_id, created_at,
       first_value(order_id) OVER w AS first_order,
       last_value(order_id)  OVER w AS last_order
FROM orders
WHERE customer_id = 7
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);
-- 3239 | 2025-01-20 | 3239 | 3239
-- 3659 | 2025-02-15 | 3239 | 3659
-- 2787 | 2025-03-02 | 3239 | 2787

last_value вернул текущую строку, а не последний заказ клиента: рамка заканчивается на текущей строке. Чтобы получить настоящий последний, рамку нужно раскрыть до конца группы: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Тогда в каждой строке будет 1404.

Скользящее окно

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

SELECT d, n,
       round(avg(n) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS avg_7d
FROM (SELECT created_at::date AS d, count(*) AS n FROM orders WHERE created_at >= '2026-06-01' GROUP BY 1) t
ORDER BY d;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW это семь строк: шесть предыдущих и текущая. Если в данных пропущен день, семь строк покроют больше семи календарных дней. Для окна по времени, а не по числу строк, есть RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW: оно берёт строки по значению даты, и пропущенные дни в него не «доливаются». Какой вариант правильный, зависит от вопроса: «за последние 7 наблюдений» или «за последние 7 дней».

Доля от целого

Окно без PARTITION BY и без ORDER BY охватывает весь результат:

SELECT city, n, round(100.0 * n / sum(n) OVER (), 1) AS share_pct
FROM (SELECT coalesce(city, '(нет)') AS city, count(*) AS n FROM customers GROUP BY 1) t
ORDER BY n DESC;
-- Москва | 488 | 32.5

С PARTITION BY category та же конструкция даёт долю товара внутри категории. Это замена подзапросу «общий итог», который иначе пришлось бы считать отдельно и присоединять.

ntile(4) OVER (ORDER BY total_amount) делит строки на четыре равные группы, это готовые квартили для сегментации клиентов по чеку.

Окно и порядок выполнения

Напоминание из первого блока: окна считаются после WHERE, GROUP BY и HAVING, но до ORDER BY и LIMIT. Поэтому окно может использовать результат агрегата, sum(sum(amount)) OVER (...) это законная запись, а фильтровать по значению окна в том же запросе нельзя, нужен подзапрос. Оконные функции также нельзя вкладывать друг в друга и использовать внутри WHERE, HAVING и GROUP BY.

Практика