Оконные функцииСредний
ROW_NUMBER / RANK / DENSE_RANK
Оконные функции нумерации строк. Различаются поведением при одинаковых значениях.
Синтаксис
ROW_NUMBER() OVER ([PARTITION BY col] ORDER BY col) RANK() OVER (...) DENSE_RANK() OVER (...)
Объяснение
ROW_NUMBER(): уникальный номер для каждой строки, без пропусков.
RANK(): одинаковые значения получают одинаковый ранг, следующий ранг пропускается (1,1,3).
DENSE_RANK(): одинаковые значения получают одинаковый ранг, пропуска нет (1,1,2).
Часто используется для топ-N в группе: ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC), потом WHERE rn <= 3.
Пример
-- Топ-3 сотрудника по зарплате в каждом отделе
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;Связанные термины
CTE (Common Table Expression)Именованный подзапрос, объявленный через WITH. Делает сложные запросы читаемыми.OVER (оконные функции)Ключевое слово, превращающее агрегатную функцию в оконную — вычисляет значение без схлопывания строк.PARTITION BYДелит строки на разделы для оконной функции. Аналог GROUP BY, но без схлопывания строк.
Анекдоты по теме
— Что такое оконная функция? — Это агрегат, который не схлопывает строки. — Как? — Представь, что GROUP BY считает итог для группы. Оконная функция считает итог, но оставляет каждую строку.
RANK() и DENSE_RANK() поспорили о справедливости. RANK(): — Если двое на первом месте, следующий — третий. DENSE_RANK(): — Если двое на первом месте, следующий — второй. ROW_NUMBER(): — Мне всё равно. Каждый получает уникальный номер.
LEAD() смотрит вперёд. LAG() смотрит назад. Они никогда не встречаются в одной строке результата. Но в одном запросе — пожалуйста: SELECT LAG(price) OVER w, LEAD(price) OVER w FROM prices WINDOW w AS (ORDER BY date);