Зачем нужны оконные функции

Оконные функции — это GROUP BY на стероидах: вместо схлопывания строк, они добавляют расчётный столбец к каждой строке, оставляя саму строку на месте.

Простой пример. Допустим у тебя таблица salaries (employee, dept, salary):

employee   dept    salary
Айгерим    data    250000
Ерлан      data    300000
Дана       data    220000
Алишер     dev     350000
Камила     dev     280000

С GROUP BY dept ты узнаешь среднее по отделу — 3 строки, теряя имена. С оконкой можно сохранить все 5 строк и рядом показать среднее.

select employee, dept, salary,
       avg(salary) over (partition by dept) as dept_avg
from salaries;

Результат:

employee   dept    salary  dept_avg
Айгерим    data    250000  256667
Ерлан      data    300000  256667
Дана       data    220000  256667
Алишер     dev     350000  315000
Камила     dev     280000  315000

Теперь видно кто выше/ниже среднего по своему отделу.

Синтаксис

function_name(arg1, ...) OVER (
  [PARTITION BY col1, col2, ...]
  [ORDER BY col3 ASC|DESC, ...]
  [frame_clause]
)

PARTITION BY — на какие группы разбить (как GROUP BY, но строки не схлопываются)
ORDER BY — порядок внутри партиции (важен для running totals, LAG/LEAD, RANK)
frame_clause — какие строки внутри партиции учитывать (см. ниже)

Главные категории оконных функций

1. Агрегаты (aggregate window functions)

SUM, AVG, COUNT, MIN, MAX — обычные агрегаты но с OVER().

-- Зарплата каждого сотрудника + сколько он "забирает" от фонда отдела
select employee, dept, salary,
       salary / sum(salary) over (partition by dept) * 100 as pct_of_dept
from salaries;

2. Ранжирование (ranking)

ROW_NUMBER, RANK, DENSE_RANK, NTILE.

Разница между ROW_NUMBER, RANK, DENSE_RANK:

salary  ROW_NUMBER  RANK  DENSE_RANK
300000  1           1     1
250000  2           2     2
250000  3           2     2     ← тот же ранг
220000  4           4     3     ← RANK пропускает, DENSE_RANK нет

Когда что использовать:

ROW_NUMBER — "топ-1 в каждой группе" (любым tiebreaker)
DENSE_RANK — медали (1, 2, 3, без пропусков)
RANK — официальные рейтинги (с пропусками после ties)
NTILE(4) — разбить на квартили

3. Соседние строки (offset)

LAG, LEAD — взять значение из предыдущей или следующей строки.

-- День-над-днём изменение выручки
select day, revenue,
       lag(revenue) over (order by day) as prev_day,
       revenue - lag(revenue) over (order by day) as day_over_day_diff
from daily_revenue;

LAG(col, n, default) — n шагов назад, default если выходит за пределы.

4. Первая/последняя в окне

FIRST_VALUE, LAST_VALUE, NTH_VALUE.

-- Сравнить зарплату каждого с самой высокой в отделе
select employee, dept, salary,
       first_value(salary) over (
         partition by dept order by salary desc
       ) as top_salary_in_dept
from salaries;

Frame clause: главная ловушка

По умолчанию рамка окна — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (при наличии ORDER BY).

Это значит: для running totals — работает интуитивно.

-- Накопительная выручка по дням
select day, revenue,
       sum(revenue) over (order by day) as running_total
from daily_revenue;
-- day=1: revenue
-- day=2: revenue(1) + revenue(2)
-- day=3: revenue(1) + revenue(2) + revenue(3)

Но для FIRST_VALUE / LAST_VALUE это создаёт сюрприз:

last_value(salary) over (partition by dept order by salary desc)
-- Вернёт... salary самой текущей строки. WTF?

Потому что default frame = "от начала до текущей строки". А LAST_VALUE берёт последнюю в этой подмножестве — то есть текущую.

Решение: явно расширить frame:

last_value(salary) over (
  partition by dept order by salary desc
  rows between unbounded preceding and unbounded following
)

Правило большого пальца: для running totals — оставляй дефолт. Для LAST_VALUE и подобных — указывай BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

3 классические задачи

Задача 1: Топ-N в каждой группе

«Найди топ-3 самых дорогих заказа в каждом городе»

with ranked as (
  select *,
         row_number() over (partition by city order by total desc) as rn
  from orders
)
select * from ranked where rn <= 3;

Задача 2: Сравнение с предыдущим месяцем

«Покажи выручку за каждый месяц + рост по сравнению с прошлым»

select month, revenue,
       lag(revenue) over (order by month) as prev_month,
       (revenue - lag(revenue) over (order by month)) /
         nullif(lag(revenue) over (order by month), 0) * 100 as growth_pct
from monthly_revenue;

Задача 3: Running average последних 7 дней

«Скользящее среднее DAU за 7 дней»

select day, dau,
       avg(dau) over (
         order by day
         rows between 6 preceding and current row
       ) as dau_7day_avg
from daily_metrics;

Производительность

Оконные функции обычно медленнее чем GROUP BY, потому что движок не может просто схлопнуть строки — он должен сохранить их и дополнительно посчитать оконку.

Оптимизация:

Один и тот же OVER (...) несколько раз? Используй WINDOW alias:

select ...
from t
window w as (partition by dept order by salary)
-- потом: sum(x) over w, avg(y) over w

Индекс на колонках из PARTITION BY + ORDER BY ускоряет.
Не оборачивай оконку в подзапрос если можно избежать (PostgreSQL умеет хорошо оптимизировать, MySQL хуже).

TL;DR

Окна = добавляют столбец, не схлопывают строки
ROW_NUMBER для топ-1, RANK/DENSE_RANK для медалей
LAG/LEAD для сравнения с соседями
SUM() OVER (ORDER BY ...) для running totals
Frame clause критичен для LAST_VALUE (без него вернёт текущую строку)
Для повторяющегося окна — WINDOW alias

Window-функции в SQL: от ROW_NUMBER до running totals