Оконные функции: основы

Считать по группе, но не терять строки

Помните GROUP BY? Он считает итог по группе, но схлопывает строки: вместо десяти самолётов остаётся одна строка со средней скоростью. А что если нужно показать каждый самолёт и рядом — среднюю по всем? Тут на помощь приходят оконные функции.

Оконная функция вычисляется «по окну» строк, но каждую строку оставляет в результате, добавляя рядом вычисленный столбец. Ключевое слово — OVER.

Синтаксис: функция OVER (...)

SELECT airplane_code, speed,
       AVG(speed) OVER () AS avg_speed
FROM airplanes;

Пустые скобки OVER () означают «окно = все строки результата». В каждой строке появится одинаковая средняя скорость по всем самолётам — при этом сами строки никуда не делись. Так же работают SUM(...) OVER (), COUNT(*) OVER (), MAX(...) OVER () и т.д.

PARTITION BY — окно по группам

Чтобы считать не по всем строкам, а внутри групп (например, «сколько мест в этом же самолёте»), в окне указывают PARTITION BY:

SELECT airplane_code, seat_no,
       COUNT(*) OVER (PARTITION BY airplane_code) AS seats_in_plane
FROM seats;

Это похоже на GROUP BY, но строки остаются: рядом с каждым местом видно, сколько всего мест в его самолёте.

Нумерация и ранги: ROW_NUMBER, RANK, DENSE_RANK

Если внутри окна задать порядок через ORDER BY, можно нумеровать строки:

SELECT airplane_code, speed,
       ROW_NUMBER() OVER (ORDER BY speed DESC) AS rn
FROM airplanes;

• ROW_NUMBER() — сквозная нумерация 1,2,3,… (всегда уникальная);
• RANK() — ранг с «пропусками» при совпадениях: 1,1,3,…;
• DENSE_RANK() — ранг без пропусков: 1,1,2,….

Разбор: ROW_NUMBER, RANK и DENSE_RANK на одном примере

Пусть скорости трёх самолётов — 900, 900 и 850, а окно задано как ORDER BY speed DESC:

speed	ROW_NUMBER	RANK	DENSE_RANK
900	1	1	1
900	2	1	1
850	3	3	2

ROW_NUMBER нумерует подряд, даже при одинаковых значениях. RANK даёт одинаковым значениям одно место и пропускает следующие — как в спорте. DENSE_RANK тоже даёт одно место, но без пропусков.

Типичные ошибки

• Фильтр по оконной функции в WHERE. WHERE ROW_NUMBER() OVER (…) = 1 — ошибка: окна вычисляются после WHERE. Заверните запрос в CTE и фильтруйте снаружи.
• ROW_NUMBER без уникальной сортировки. Если скорости совпадают, какой самолёт получит номер 1, а какой — 2, решает случай. Добавьте в окно уникальный столбец: ORDER BY speed DESC, airplane_code.

💡 Для ROW_NUMBER() добавляйте в OVER (ORDER BY ...) уникальный «тай-брейкер» (например, airplane_code), иначе номера равных строк могут «прыгать». Но для RANK() и DENSE_RANK() не добавляйте уникальный столбец, если одинаковые значения должны получить одинаковый ранг. Стабильный порядок вывода задавайте отдельно, во внешнем ORDER BY.