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, но без схлопывания строк.

Анекдоты по теме

Разработчик: — У меня запрос на 8 таблиц через JOIN, подзапросы, оконные функции внутри WHERE и GROUP BY на хэш с миллиардом строк. Он идёт 3 дня. Как оптимизировать? DBA: — TRUNCATE TABLE карьера_разработчика. И иди в менеджеры.

Аналитик хочет топ-3 продукта в каждой категории. Пишет GROUP BY, ORDER BY, LIMIT 3. Получает топ-3 вообще, не по категориям. DBA: тебе нужна оконная функция. ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) Потом WHERE rn <= 3. Аналитик: почему это не просто LIMIT 3 внутри GROUP BY?! DBA: потому что SQL так работает.

LEAD() смотрит вперёд. LAG() смотрит назад. Они никогда не встречаются в одной строке результата. Но в одном запросе — пожалуйста: SELECT LAG(price) OVER w, LEAD(price) OVER w FROM prices WINDOW w AS (ORDER BY date);

Все анекдоты →

Читать статью →Потренироваться →Вопросы на собесе →← Все термины