Menu

Оконные функции PostgreSQL: OVER, PARTITION BY, ROW_NUMBER

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

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

Оконная функция вычисляет значение для каждой строки по набору связанных строк, её окну, и не сливает строки, как это делает 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) всегда попадают в рамку вместе.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать