Что такое оконная функция

Проблема GROUP BY

GROUP BY суммирует строки в группы и возвращает по одной строке на группу. Но что если нужно видеть и исходные строки, и агрегат одновременно?

Задача: вывести каждую транзакцию и рядом — общую сумму по тому же счёту.

С GROUP BY это невозможно в одном запросе. Придётся делать подзапрос:

SELECT t.id, t.account_id, t.amount,
       sub.total
FROM transactions t
JOIN (
    SELECT account_id, SUM(amount) AS total
    FROM transactions
    GROUP BY account_id
) sub ON sub.account_id = t.account_id
ORDER BY t.account_id, t.id;

Это работает, но громоздко. Оконная функция делает то же самое элегантнее.

Оконная функция: определение

Оконная функция вычисляет результат для каждой строки, учитывая набор строк (окно), которые связаны с текущей строкой.

Главное отличие от GROUP BY: строки не схлопываются. Каждая строка остаётся в результате, просто рядом появляется вычисленное значение.

SELECT id, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM transactions
ORDER BY account_id, id;

 id │ account_id │ amount │ account_total
────┼────────────┼────────┼──────────────
  1 │     1      │  5 000 │    32 500   ← сумма всех транзакций счёта 1
  2 │     1      │  8 000 │    32 500   ← та же сумма
  3 │     1      │  3 200 │    32 500   ← та же сумма
...
 11 │     2      │  7 500 │    19 800   ← сумма для счёта 2

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

функция(колонка) OVER (
    PARTITION BY колонка_группировки
    ORDER BY колонка_сортировки
)

• функция() — что вычисляем: SUM, COUNT, AVG, MAX, MIN, ROW_NUMBER, RANK, LAG, LEAD и другие
• OVER() — ключевое слово, превращающее функцию в оконную
• PARTITION BY — делит строки на группы (аналог GROUP BY, но без схлопывания). Опционально.
• ORDER BY — порядок строк внутри окна. Опционально.

GROUP BY vs оконная функция

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

SELECT id, account_id, amount,
       ROUND(AVG(amount) OVER (PARTITION BY account_id), 2) AS avg_for_account,
       amount - AVG(amount) OVER (PARTITION BY account_id) AS diff_from_avg
FROM transactions
ORDER BY account_id, id;

Каждая транзакция остаётся в результате, но рядом видим среднее по своему счёту и отклонение от него.

Запомни

функция() OVER (PARTITION BY ...) = агрегат без схлопывания строк. Это главная суперсила оконных функций.