Ответ

Оконные функции позволяют выполнять вычисления по "окну" строк, не группируя их в отдельные строки, что удобно для отчетов и аналитики.

ROW_NUMBER() — присваивает уникальный последовательный номер строкам внутри каждой партиции (разбивки) окна, сортируя их по заданному критерию. Может возвращать пропуски нумерации при одинаковых значениях в ORDER BY.
RANK() — присваивает одинаковый ранг строкам с одинаковым значением ORDER BY, но номера следующих элементов пропускает (будет разрыв).
DENSE_RANK() — тоже присваивает одинаковый ранг строкам с одинаковым значением, но номера идут подряд, без пропусков.

Пример:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Таблица:

name	salary
Вася	10000
Петя	10000
Маша	9000

Результат:

name	salary	num	rank	dense_rank
Вася	10000	1	1	1
Петя	10000	2	1	1
Маша	9000	3	3	2

Подводные камни:

Некорректно выбранный ORDER BY может привести к неверной сортировке.
(row_number) Если не выбрать уникальное поле в ORDER BY — отсутствует гарантия стабильного порядка.
Использование без PARTITION BY при необходимости разбивки приводит к неправильной нумерации по всему множеству строк.

Вопрос с подвохом

Если не указать PARTITION BY в окне функции, как будет производиться нумерация строк в ROW_NUMBER()?

Ответ: Вся выборка данных будет считаться одной партицией. То есть нумерация будет сквозная по всем строкам, без учета каких-либо группировок.

Пример:

SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
-- Все сотрудники получат уникальный номер, начиная с 1 без разбивки по отделам

История №1

В BI-отчете забыли указать PARTITION BY по отделу. Все сотрудники компании получили подряд сквозную нумерацию, а задача ставилась — идентифицировать лучших внутри каждого отдела. В результате получился некорректный TOP-N сотрудников по отделам.

История №2

Разработчик выбрал RANK() вместо ROW_NUMBER(), чтобы определить "лучшего" из группы — но при одинаковых показателях были присвоены одинаковые номера, из-за чего возникли неявные дубликаты лидеров в аналитике.

История №3

При использовании DENSE_RANK() не учли, что он запрещает пропуски в рангах, что исказило отчет о количестве "уникальных" значимых позиций при анализе продаж. Контроль на бизнес-логику выявил ошибку распределения мест.

Как работают и чем отличаются оконные функции ROW_NUMBER(), RANK(), DENSE_RANK() при программировании отчетов в SQL? Какие подводные камни есть при их использовании?

Ответ

Вопрос с подвохом