Что такое ROW_NUMBER, RANK и DENSE_RANK? В чём между ними разница?

ROW_NUMBER, RANK и DENSE_RANK: Window Functions для Ранжирования

ROW_NUMBER, RANK и DENSE_RANK — это три различных способа ранжирования строк в SQL. Они работают как Window Functions и позволяют нумеровать, ранжировать или находить топ элементов. Различия между ними критичны для правильного анализа данных.

ROW_NUMBER — Последовательная Нумерация

Применяет уникальный номер каждой строке в порядке сортировки. Если есть дубликаты, они всё равно получают разные номера.

SELECT
    product_name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as rank
FROM products;

Результат:

product_name | revenue | rank
---
Laptop       | 5000    | 1
Phone        | 5000    | 2      <- Один номер = одна строка
Tablet       | 3000    | 3
Watch        | 2000    | 4

RANK — Ранжирование с Пробелами

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

SELECT
    product_name,
    revenue,
    RANK() OVER (ORDER BY revenue DESC) as rank
FROM products;

Результат:

product_name | revenue | rank
---
Laptop       | 5000    | 1
Phone        | 5000    | 1      <- Одинаковый ранг
Tablet       | 3000    | 3      <- Пропуск номера 2
Watch        | 2000    | 4

DENSE_RANK — Ранжирование Без Пробелов

Применяет одинаковый ранг дубликатам, но НЕ пропускает следующие номера.

SELECT
    product_name,
    revenue,
    DENSE_RANK() OVER (ORDER BY revenue DESC) as rank
FROM products;

Результат:

product_name | revenue | rank
---
Laptop       | 5000    | 1
Phone        | 5000    | 1      <- Одинаковый ранг
Tablet       | 3000    | 2      <- Без пропусков
Watch        | 2000    | 3

Сравнение Таблица

Практические Примеры

1. Топ 3 продукта по доходу (RANK)

Что если есть ничья на 3 месте? RANK сохранит всех участников ничьи в топе.

WITH product_ranking AS (
  SELECT
    product_id,
    product_name,
    SUM(revenue) as total_revenue,
    RANK() OVER (ORDER BY SUM(revenue) DESC) as rank
  FROM sales
  GROUP BY product_id, product_name
)
SELECT *
FROM product_ranking
WHERE rank <= 3;

Результат может иметь 4+ строк, если несколько продуктов имеют одинаковый доход на позиции 3.

2. Первый заказ каждого пользователя (ROW_NUMBER)

WITH first_purchase AS (
  SELECT
    user_id,
    order_id,
    created_at,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) as purchase_num
  FROM orders
)
SELECT *
FROM first_purchase
WHERE purchase_num = 1;  -- Только первый заказ

ROW_NUMBER гарантирует ровно одну строку на пользователя (первую по времени).

3. Уровни квалификации сотрудников (DENSE_RANK)

Разбей зарплаты на 5 уровней без пропусков:

SELECT
    employee_id,
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) as salary_level
FROM employees;

Результат: 5 уровней (1,2,3,4,5) без пробелов.

4. Пагинация с OFFSET/FETCH

WITH ranked_products AS (
  SELECT
    product_id,
    product_name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as row_num
  FROM products
)
SELECT *
FROM ranked_products
WHERE row_num BETWEEN 11 AND 20;  -- Страница 2, по 10 товаров

5. PARTITION BY — Ранжирование внутри групп

Лучший продукт в каждой категории:

WITH category_ranking AS (
  SELECT
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rank_in_category
  FROM products
)
SELECT *
FROM category_ranking
WHERE rank_in_category = 1;  -- Топ 1 в каждой категории

6. Сложный пример: Месячный рост зарплаты сотрудников

WITH salary_history AS (
  SELECT
    employee_id,
    salary,
    DATE_TRUNC('month', date)::DATE as month,
    DENSE_RANK() OVER (PARTITION BY employee_id ORDER BY salary) as salary_level,
    LAG(salary) OVER (PARTITION BY employee_id ORDER BY DATE_TRUNC('month', date)) as prev_salary
  FROM employee_salary_log
),
salary_growth AS (
  SELECT
    employee_id,
    month,
    salary,
    salary_level,
    prev_salary,
    CASE
      WHEN prev_salary IS NULL THEN 'Starting'
      WHEN salary > prev_salary THEN 'Raised'
      WHEN salary < prev_salary THEN 'Decreased'
      ELSE 'Unchanged'
    END as salary_change
  FROM salary_history
)
SELECT *
FROM salary_growth
WHERE salary_change = 'Raised'
ORDER BY employee_id, month;

Комбинация с LAG / LEAD

Делай сравнение с соседними строками:

WITH ranked_sales AS (
  SELECT
    DATE(created_at) as date,
    SUM(amount) as daily_revenue,
    ROW_NUMBER() OVER (ORDER BY DATE(created_at)) as day_num,
    LAG(SUM(amount)) OVER (ORDER BY DATE(created_at)) as prev_day_revenue,
    LEAD(SUM(amount)) OVER (ORDER BY DATE(created_at)) as next_day_revenue
  FROM sales
  GROUP BY DATE(created_at)
)
SELECT
  date,
  daily_revenue,
  prev_day_revenue,
  daily_revenue - prev_day_revenue as growth_from_yesterday
FROM ranked_sales;

Производительность

Все три функции имеют похожую производительность O(n log n). Выбирай исходя из логики, не производительности.

Совет: Используй PARTITION BY для разделения больших таблиц на груп пы — это может ускорить вычисления.

-- Этот запрос может быть медленнее
SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY created_at) as num
FROM huge_table;  -- 100M строк

-- Этот быстрее если у тебя есть natural partitioning
SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) as num
FROM huge_table;

Ключевые Выводы

• ROW_NUMBER: Просто нумеруй. Каждая строка = уникальный номер
• RANK: Ранжируй с пробелами (как олимпийские медали: 1,1,3)
• DENSE_RANK: Ранжируй без пробелов (уровни: 1,2,3 всегда подряд)
• PARTITION BY: Ранжируй отдельно внутри групп
• Комбинируй с LAG/LEAD: Сравнивай соседние строки

Выбор правильной функции критичен для корректного анализа. Подумай о том, как ты хочешь обрабатывать дубликаты, прежде чем писать запрос.