Практика: топ-N по группам и доли

Собираем оконные функции в реальные задачи: топ-N по группам и анализ долей.

Самый частый production-приём с окнами — «топ-N в каждой группе»: пронумеровать строки внутри группы через ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) и снаружи оставить только первые N.

Задача: топ-2 зарплаты в каждом отделе

Это нельзя сделать обычным GROUP BY — он схлопнет строки и потеряет имена. Решение в два этажа: внутренний запрос нумерует сотрудников внутри отдела по убыванию зарплаты, внешний фильтрует по номеру. Помните из первого урока: окно нельзя звать в WHERE той же выборки, поэтому нумерацию прячем в подзапрос.

CREATE TABLE emp(name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES
 ('Аня','Backend',120),('Борис','Backend',150),('Вика','Backend',90),
 ('Глеб','Frontend',100),('Дина','Frontend',110),('Егор','Frontend',95);

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM emp
)
WHERE rn <= 2
ORDER BY dept, salary DESC;

Вывод:

name   dept      salary
-----  --------  ------
Борис  Backend   150
Аня    Backend   120
Дина   Frontend  110
Глеб   Frontend  100

Из каждого отдела остались ровно двое лучших. Если нужны не «по одному на место», а «все с топ-2 значениями зарплаты» (с учётом ничьих), поменяйте ROW_NUMBER на DENSE_RANK — и при равных зарплатах попадут все.

Задача: доля каждого товара в категории

Комбинируем SUM ... OVER (PARTITION BY) для знаменателя и обычную колонку для числителя.

CREATE TABLE sale(product TEXT, category TEXT, amount INTEGER);
INSERT INTO sale VALUES
 ('Мышь','Периферия',20),('Клава','Периферия',60),
 ('SSD','Накопители',30),('HDD','Накопители',10);

SELECT product, category, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY category), 1) AS pct_in_cat
FROM sale
ORDER BY category, amount DESC;

Вывод:

product  category    amount  pct_in_cat
-------  ----------  ------  ----------
SSD      Накопители  30      75.0
HDD      Накопители  10      25.0
Клава    Периферия   60      75.0
Мышь     Периферия   20      25.0

Шаблон «топ-N по группам» на будущее

SELECT *
FROM (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY группа ORDER BY метрика DESC) AS rn
  FROM таблица t
) sub
WHERE rn <= N;

Запомните этот каркас — он закрывает огромный класс задач: «3 самых дорогих заказа каждого клиента», «последнее событие на пользователя», «топ-5 страниц в каждом разделе».

Итог

Топ-N по группам: пронумеровать ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) в подзапросе, снаружи WHERE rn <= N.
DENSE_RANK вместо ROW_NUMBER — если нужно учитывать ничьи.
Доля строки в группе = значение / SUM(...) OVER (PARTITION BY группа).