Оконная функция вычисляет значение для каждой строки по набору связанных строк, её окну, и не сливает строки, как это делает GROUP BY. Записывается она так: function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY делит строки на группы, а ORDER BY задаёт порядок внутри каждой группы.
Каждая строка остаётся в результате и несёт итог своего региона: 1000 для north, 500 для south. Доля каждого продавца после этого считается одним выражением (у ana 50 процентов north). Любая агрегатная функция (sum, count, avg, min, max) становится оконной, если добавить OVER. Пустой OVER () делает одним окном весь результат.
Нумерация строк: ROW_NUMBER, RANK и DENSE_RANK
Три функции ранжирования различаются только тогда, когда у строк одинаковое значение ORDER BY:
У ben и cy по 85. row_number всё равно даёт им 2 и 3; кто из них получит 2, не определено, если ORDER BY не разрешает ничью. rank даёт обоим 2 и перескакивает на 4 для dee. dense_rank даёт обоим 2 и продолжает с 3.
| Функция | Одинаковые значения | Пропуски после них |
|---|---|---|
row_number() | разные номера | нет |
rank() | одинаковый номер | да |
dense_rank() | одинаковый номер | нет |
Топ N строк в каждой группе
Оконные функции вычисляются после WHERE, поэтому фильтровать по ним напрямую нельзя. Пронумеруйте строки в подзапросе или CTE, а фильтруйте снаружи:
Запрос вернёт двух лучших продавцов каждого региона. Используйте rank() вместо row_number(), чтобы сохранить все строки, которые делят второе место. Если нужна только первая строка группы, короче будет DISTINCT ON.
Нарастающий итог и скользящее среднее
Если внутри OVER есть ORDER BY, агрегатная функция работает по рамке. По умолчанию рамка идёт от первой строки секции до текущей, и это даёт нарастающий итог. Конструкция ROWS BETWEEN задаёт другую рамку, например текущую строку и две перед ней:
К 5-му дню нарастающий итог доходит до 130, а трёхдневное среднее на 5-й день равно (30 + 20 + 50) / 3 = 33.3.
Рамка по умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Она включает все строки, у которых значение ORDER BY совпадает с текущей, поэтому две строки с одним и тем же днём получают одинаковый нарастающий итог. Напишите ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, чтобы прибавлять их по одной.
Сравнение с предыдущей строкой: LAG и LEAD
lag(col) читает значение из строки перед текущей в порядке окна, а lead(col) из строки после неё. За краем окна обе возвращают NULL, если не передать значение по умолчанию третьим аргументом.
У 1-го дня нет предыдущей строки, поэтому previous и change равны NULL. Конструкция WINDOW один раз задаёт окно по имени, чтобы им пользовались несколько функций.
first_value работает с рамкой по умолчанию, а last_value нет: рамка по умолчанию заканчивается на текущей строке, поэтому функция возвращает значение текущей строки. Добавьте ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, чтобы рамкой стала вся секция. Другие функции, которые бывают только оконными: ntile(n), percent_rank(), cume_dist() и nth_value(); все они перечислены в справочнике по оконным функциям PostgreSQL.
Какие строки учитывает агрегатная функция
FILTER (WHERE ...) работает и в оконных агрегатных функциях, чтобы получать условные итоги в каждой строке:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
Часто задаваемые вопросы
Чем GROUP BY отличается от PARTITION BY?
GROUP BY возвращает одну строку на группу. PARTITION BY внутри OVER сохраняет каждую строку и даёт ей значение, вычисленное по её группе, так что сумму строки можно показать рядом с итогом группы.
Можно ли использовать оконную функцию в WHERE?
Нет. Оконные функции выполняются после WHERE, GROUP BY и HAVING, поэтому допустимы только в списке выборки и в ORDER BY. Вычислите значение в подзапросе или CTE и фильтруйте во внешнем запросе.
Почему LAST_VALUE возвращает значение текущей строки?
Если в OVER есть ORDER BY, рамка по умолчанию заканчивается на текущей строке (и строках с тем же значением), поэтому последняя строка рамки это текущая. Добавьте ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, чтобы рамкой стала вся секция.
Чем ROWS отличается от RANGE в рамке окна?
ROWS считает физические строки: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW это текущая строка и две перед ней. RANGE работает со значением ORDER BY, поэтому строки с одинаковыми значениями (peers) всегда попадают в рамку вместе.