Оконные функции: OVER, PARTITION BY и ранжирование

Считаем аналитику по строке над множеством связанных строк - не схлопывая их.

Главная идея: Оконная функция выполняет вычисление над окном - набором строк, связанных с текущей строкой - и возвращает одно значение на каждую строку, сохраняя все исходные строки видимыми. Это ключевое отличие от GROUP BY, который сворачивает множество строк в одну. Оконные функции - фундамент аналитического SQL: ранжирования, топ-N по группам, нарастающие итоги и скользящие средние начинаются отсюда.

К концу вы сможете:

объяснить, чем оконная модель отличается от GROUP BY;
читать устройство конструкции OVER(...);
разбивать данные на группы через PARTITION BY;
верно выбирать между ROW_NUMBER, RANK и DENSE_RANK;
раскладывать строки по корзинам через NTILE(n);
чисто решать задачу топ-N в каждой группе.

0. Пример схемы

Каждый запрос ниже выполняется над одной небольшой таблицей продаж. Каждая строка - одна сделка, закрытая продавцом в регионе.

CREATE TABLE sales (
    id           INT PRIMARY KEY,
    region       VARCHAR(20),
    salesperson  VARCHAR(30),
    amount       DECIMAL(10,2)
);

INSERT INTO sales (id, region, salesperson, amount) VALUES
    (1, 'North', 'Alice', 5000),
    (2, 'North', 'Bob',   5000),
    (3, 'North', 'Carol', 3000),
    (4, 'South', 'Dan',   7000),
    (5, 'South', 'Eve',   4000),
    (6, 'South', 'Frank', 4000),
    (7, 'West',  'Grace', 6000),
    (8, 'West',  'Heidi', 2000);

Обратите внимание на намеренные совпадения: Alice и Bob оба закрыли 5000; Eve и Frank оба закрыли 4000. Именно на совпадениях три функции ранжирования ведут себя по-разному.

1. Оконная модель против GROUP BY

Вы уже знаете GROUP BY: он схлопывает каждую группу в одну итоговую строку. Если запросить сумму по региону, вернутся три строки - по одной на регион - а отдельные продавцы исчезнут.

SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;

region	region_total
North	13000
South	15000
West	8000

Оконная функция считает ту же SUM, но сохраняет каждую строку. Волшебное слово - OVER: оно говорит "сделай этот агрегат над окном, а не над всей группой".

SELECT region, salesperson, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

region	salesperson	amount	region_total
North	Alice	5000	13000
North	Bob	5000	13000
North	Carol	3000	13000
South	Dan	7000	15000
South	Eve	4000	15000
South	Frank	4000	15000
West	Grace	6000	8000
West	Heidi	2000	8000

Те же восемь строк, теперь каждая несёт рядом свой региональный итог. Именно эта способность - "агрегируй, но сохрани детализацию" - заставляет аналитиков постоянно тянуться к оконным функциям. Вычислите amount / region_total - и получите долю каждого в своём регионе, что за один проход обычным GROUP BY невозможно.

Оконные функции: OVER, PARTITION BY и ранжирование