Бывает так: нужно посчитать сумму по группе, но при этом не схлопывать строки в одну. Например, показать каждый рейс и рядом - его долю в выручке дня. Обычный GROUP BY тут не подходит, он сворачивает детали. Эту задачу в postgresql решают оконные функции (window functions). В этом уроке разберём, как работает OVER, чем PARTITION BY отличается от GROUP BY, как ранжировать строки через row_number, rank и dense_rank, заглядывать в соседние строки через lag и lead, считать нарастающие итоги и управлять рамкой окна (ROWS, RANGE, GROUPS). По дороге соберём топ рейсов и накопительную выручку на демобазе Авиаперевозки.
Как это работает
Оконная функция считает значение для каждой строки, глядя на набор соседних строк - это и есть окно. Ключевое отличие от агрегата с GROUP BY: агрегат уменьшает число строк, а оконная функция оставляет их все на месте и просто дописывает рядом ещё одну колонку с результатом.
Окно описывается в скобках после слова OVER. Внутри три необязательные части. PARTITION BY режет данные на секции (по аэропорту, по дню, по пассажиру), и функция считается отдельно в каждой секции, не перемешивая их. ORDER BY задаёт порядок строк внутри секции - он критичен для нарастающих итогов и для ранжирования. Рамка (frame) ограничивает, какие именно строки секции попадают в расчёт для текущей строки.
Порядок выполнения важен для понимания. Оконные функции вычисляются почти в самом конце обработки запроса - после WHERE, GROUP BY и HAVING, но до ORDER BY всего запроса и до LIMIT. Поэтому в WHERE нельзя сослаться на результат оконной функции напрямую: его ещё не существует. Спасает обёртка - подзапрос или CTE.
Функции делятся на несколько семейств. Ранжирующие: row_number дает сплошную нумерацию 1,2,3 без учёта равенств; rank при равных значениях ставит одинаковый ранг и потом делает пропуск (1,1,3); dense_rank тоже даёт равным одинаковый ранг, но без пропусков (1,1,2); ntile(n) делит секцию на n примерно равных корзин. Смещающие: lag смотрит на предыдущую строку, lead - на следующую, что удобно для расчёта дельты к прошлому периоду. И обычные агрегаты - sum, avg, count, max - тоже работают как оконные, если добавить OVER.
Отдельно про рамку, потому что это главный источник сюрпризов. Если в OVER есть ORDER BY, но рамка не указана явно, PostgreSQL по умолчанию берёт RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это значит сумму от начала секции до текущей строки - то, что нужно для нарастающего итога. ROWS считает строки физически, RANGE - по значению ORDER BY (строки с одинаковым ключом попадают в рамку вместе), GROUPS (появился в PG 11) шагает целыми группами равных значений.
SQL и примеры
Топ-10 рейсов по выручке. Считаем сумму билетов по каждому рейсу и нумеруем по убыванию.
Код: Выделить всё
SELECT f.flight_no,
f.scheduled_departure::date AS dep_date,
sum(tf.amount) AS revenue,
row_number() OVER (ORDER BY sum(tf.amount) DESC) AS rn
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no
ORDER BY revenue DESC
LIMIT 10;
Доля рейса в выручке своего дня. PARTITION BY режет по дате вылета, оконный sum даёт итог по дню, не схлопывая строки.
Код: Выделить всё
SELECT flight_no, dep_date, revenue,
round(100.0 * revenue
/ sum(revenue) OVER (PARTITION BY dep_date), 2) AS pct_of_day
FROM (
SELECT f.flight_id, f.flight_no,
f.scheduled_departure::date AS dep_date,
sum(tf.amount) AS revenue
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no, dep_date
) s
ORDER BY dep_date, pct_of_day DESC;
Код: Выделить всё
SELECT day,
day_revenue,
sum(day_revenue) OVER (ORDER BY day) AS running_total
FROM (
SELECT b.book_date::date AS day,
sum(b.total_amount) AS day_revenue
FROM bookings b
GROUP BY b.book_date::date
) d
ORDER BY day;
Код: Выделить всё
SELECT day, day_revenue,
lag(day_revenue) OVER (ORDER BY day) AS prev_day,
day_revenue - lag(day_revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM (
SELECT book_date::date AS day, sum(total_amount) AS day_revenue
FROM bookings GROUP BY book_date::date
) d
ORDER BY day;
Ранжирование внутри секции. Самые дорогие билеты в каждом классе обслуживания, тремя разными функциями для наглядности.
Код: Выделить всё
SELECT fare_conditions, amount,
row_number() OVER w AS rn,
rank() OVER w AS rnk,
dense_rank() OVER w AS drnk
FROM ticket_flights
WINDOW w AS (PARTITION BY fare_conditions ORDER BY amount DESC)
ORDER BY fare_conditions, amount DESC
LIMIT 30;
Частые грабли
- Ссылка на оконную функцию в WHERE или HAVING. Их там ещё нет - оборачивайте запрос в подзапрос или CTE и фильтруйте снаружи.
- Путают row_number и rank. Для дедупликации (оставить по одной строке на ключ) нужен именно row_number: rank при дублях даст несколько строк с рангом 1.
- Забывают ORDER BY в окне для нарастающего итога. Без него sum посчитает итог по всей секции одинаково для всех строк, накопления не будет.
- Молчаливая рамка по умолчанию RANGE при равных значениях ORDER BY суммирует все строки с одинаковым ключом сразу - итог скачет. Если нужна построчная логика, пишите ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW явно.
- Думают, что DISTINCT уберёт дубли до окна. Оконные функции считаются раньше DISTINCT, поэтому результат может удивить.
- NULL в lag и lead на краях секции. Если не задать значение по умолчанию третьим аргументом, получите NULL и сломаете арифметику.
- Подключитесь к демобазе: psql -d demo (или своё имя), затем SET search_path = bookings;
- Постройте топ-5 аэропортов вылета по числу рейсов: сгруппируйте flights по departure_airport, добавьте row_number() OVER (ORDER BY count(*) DESC).
- Для таблицы ticket_flights выведите fare_conditions, amount и ntile(4) OVER (PARTITION BY fare_conditions ORDER BY amount) - разбейте билеты каждого класса на 4 корзины по цене.
- Посчитайте нарастающий итог бронирований по дням из bookings с sum(...) OVER (ORDER BY book_date::date).
- Добавьте к предыдущему шагу lag, чтобы вывести разницу выручки с прошлым днём и значение по умолчанию 0 для первого дня.
- Переделайте один из запросов на блок WINDOW name AS (...) и убедитесь, что результат не изменился.
- Сравните в одном SELECT rank() и dense_rank() по amount внутри класса и найдите место, где их значения расходятся.
- Чем оконная функция принципиально отличается от агрегата с GROUP BY?
- Почему нельзя отфильтровать строки по результату row_number прямо в WHERE и как это обойти?
- В чём разница между row_number, rank и dense_rank на данных с повторами?
- Какая рамка действует по умолчанию, если в OVER указан ORDER BY, и когда стоит заменить RANGE на ROWS?
- Для чего нужен третий аргумент функций lag и lead?
- Что делает блок WINDOW и какую проблему он решает при нескольких оконных функциях?
