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

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

— Доктор, у меня шизофрения. — Это лечится. Расскажите подробно. — WITH моей_личности AS (SELECT боль, радость, тревогу, ROW_NUMBER() OVER (PARTITION BY день_недели ORDER BY кофеин DESC) FROM психика)... Ой, кажется, я только что создал три новых партиции самосознания.

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

Собеседование: — Напишите запрос для нахождения второй по величине зарплаты. Кандидат пишет подзапрос. — А можно без подзапроса? Пишет DENSE_RANK(). — А одной строкой? Пишет ORDER BY salary DESC LIMIT 1 OFFSET 1. Интервьюер: — Молодец. Это был вопрос на знание разных подходов, а не на правильный ответ.

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

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