ROW_NUMBER, RANK, DENSE_RANK: нумерация строк в группах

🧮 Зачем нужна нумерация строк?

Представь, что у тебя есть таблица с продажами, и нужно:

Пронумеровать заказы внутри каждого клиента по дате
Определить ТОП-3 самых дорогих покупки в каждом регионе
Найти второй по величине чек в ресторане за месяц

Оконные функции ROW_NUMBER, RANK и DENSE_RANK решают эти задачи элегантно! Они создают "виртуальные" номера строк в указанных группах данных.

-- Базовая структура всех трёх функций
Функция() OVER (
    PARTITION BY группа_строк
    ORDER BY критерий_сортировки
)

1️⃣ ROW_NUMBER: строгий порядковый номер

ROW_NUMBER() присваивает уникальные последовательные номера (1, 2, 3...) внутри каждой группы. Даже если значения в ORDER BY одинаковые — номера будут разными.

-- Нумеруем товары в каждом заказе по убыванию цены
SELECT 
    order_id,
    product_name,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY order_id 
        ORDER BY price DESC
    ) AS price_rank
FROM order_items;

Результат:

order_id	product_name	price	price_rank
1001	Ноутбук	1200	1
1001	Мышь	50	2 ← Мышь дешевле, получает следующий номер
1002	Монитор	300	1
1002	Клавиатура	300	2 ← Такая же цена, но номер всё равно уникальный!

2️⃣ RANK: ранжирование с пропусками

RANK() ведёт себя иначе: одинаковые значения получают одинаковый ранг, а следующий номер — с пропуском.

-- Ранжируем сотрудников по зарплате в каждом отделе
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Результат:

department	employee_name	salary	salary_rank
IT	Анна	5000	1
IT	Иван	4500	2
IT	Алексей	4500	2 ← Та же зарплата = тот же ранг
IT	Мария	4000	4 ← Пропущен номер 3!

3️⃣ DENSE_RANK: плотное ранжирование

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

-- Определяем рейтинг студентов по баллам (без пропусков)
SELECT 
    student_name,
    exam_score,
    DENSE_RANK() OVER (
        ORDER BY exam_score DESC
    ) AS score_rank
FROM students;

Результат:

student_name	exam_score	score_rank
Ольга	95	1
Дмитрий	90	2
Екатерина	90	2 ← Тот же ранг
Артём	85	3 ← А вот здесь уже нет пропуска!

🔍 Сравнение на реальном примере

Допустим, у нас есть результаты марафона:

SELECT 
    runner_name,
    finish_time,
    ROW_NUMBER() OVER (ORDER BY finish_time) AS row_num,
    RANK() OVER (ORDER BY finish_time) AS rank,
    DENSE_RANK() OVER (ORDER BY finish_time) AS dense_rank
FROM marathon_results;

Вывод:

runner_name	finish_time	row_num	rank	dense_rank
Андрей	02:30:00	1	1	1
Татьяна	02:35:00	2	2	2
Сергей	02:35:00	3	2	2 ← Два серебряных призёра
Анна	02:40:00	4	4	3 ← RANK пропустил 3, DENSE_RANK — нет

🛠️ Практические кейсы

Кейс 1: Выборка N записей из каждой группы

-- Выбираем 3 последних заказа каждого клиента
WITH numbered_orders AS (
    SELECT 
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS order_seq
    FROM orders
)
SELECT * FROM numbered_orders 
WHERE order_seq <= 3;

Кейс 2: Анализ продаж с ранжированием

-- ТОП-2 товара по выручке в каждой категории
WITH product_ranks AS (
    SELECT 
        category,
        product_name,
        revenue,
        DENSE_RANK() OVER (
            PARTITION BY category 
            ORDER BY revenue DESC
        ) AS revenue_rank
    FROM sales
)
SELECT * FROM product_ranks 
WHERE revenue_rank <= 2;

💡 Главные отличия в одной таблице

Функция	Уникальность	Одинаковые значения	Пропуски номеров
`ROW_NUMBER`	Всегда уникальны	Разные номера	Нет
`RANK`	Могут повторяться	Одинаковый ранг	Да
`DENSE_RANK`	Могут повторяться	Одинаковый ранг	Нет

🚀 Продвинутый пример: комбинирование функций

-- Анализ зарплат: позиция в отделе vs общий рейтинг
SELECT 
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS company_rank,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS diff_from_prev
FROM employees;

Разбор:

dept_rank — позиция в отделе с пропусками
company_rank — общий рейтинг по компании без пропусков
diff_from_prev — разница с предыдущей зарплатой в отделе

📝 Важные нюансы

Производительность: PARTITION BY с большими группами может замедлить запрос
Сортировка NULL: ORDER BY price DESC помещает NULL в конец. Используй NULLS LAST/FIRST при необходимости
Оконные фреймы: Эти функции не используют ROWS/RANGE, но другие оконные функции — да

-- Пример с явным указанием обработки NULL
RANK() OVER (ORDER BY last_login_date NULLS FIRST)

🏆 Когда что использовать?

ROW_NUMBER — когда нужна строгая уникальная нумерация (например, пагинация)
RANK — для спортивных рейтингов, где важен "пропуск мест"
DENSE_RANK — для аналитических отчётов, где важна плотность ранжирования

ROW_NUMBER, RANK, DENSE_RANK: нумерация строк в группах

🧮 Зачем нужна нумерация строк?

1️⃣ ROW_NUMBER: строгий порядковый номер

2️⃣ RANK: ранжирование с пропусками

3️⃣ DENSE_RANK: плотное ранжирование

🔍 Сравнение на реальном примере

🛠️ Практические кейсы

Кейс 1: Выборка N записей из каждой группы

Кейс 2: Анализ продаж с ранжированием

💡 Главные отличия в одной таблице

🚀 Продвинутый пример: комбинирование функций

📝 Важные нюансы

🏆 Когда что использовать?

🌱 Индвидидулаьные занятия