Различие между ROW_NUMBER(), RANK() и DENSE_RANK()

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

ROW_NUMBER()

• Назначение: Присваивает уникальный номер каждой строке в пределах окна.

• Принцип работы: Нумерация строк начинается с 1 и увеличивается на 1 для каждой строки, независимо от значений в других строках.

• Случай с равенством значений: Не учитывает равенства значений — каждая строка получает уникальный номер, даже если значения в строках равны.

Пример использования ROW_NUMBER()

SELECT employee_id, name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

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

RANK()

• Назначение: Присваивает ранг строкам в пределах окна, учитывая равенство значений.

• Принцип работы: Строки с одинаковыми значениями получают одинаковый ранг. Если несколько строк имеют одинаковое значение, они получают одинаковый ранг, а следующий ранг пропускается на количество строк с одинаковыми значениями.

• Случай с равенством значений: Если несколько строк имеют одинаковые значения, они получают один и тот же ранг, но последующий ранг пропускается.

Пример использования RANK()

SELECT employee_id, name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Если два сотрудника имеют одинаковую зарплату, они получат один и тот же ранг, а следующий ранг будет пропущен.

DENSE_RANK()

• Назначение: Присваивает ранг строкам в пределах окна, учитывая равенство значений.

• Принцип работы: Строки с одинаковыми значениями получают одинаковый ранг, но ранги не пропускаются. Следующий ранг идет последовательно после рангов для строк с одинаковыми значениями.

• Случай с равенством значений: Строки с одинаковыми значениями получают один и тот же ранг, и ранги идут последовательно без пропусков.

Пример использования DENSE_RANK()

SELECT employee_id, name, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

Если два сотрудника имеют одинаковую зарплату, они получат один и тот же ранг, а следующий ранг будет присвоен непосредственно следующему различному значению.

Сравнение функций

• ROW_NUMBER():

  • Каждый ряд получает уникальный номер.

  • Пример: если 3 строки имеют одинаковое значение, они получат 1, 2, и 3.

• RANK():

  • Строки с одинаковыми значениями получают одинаковый ранг.

  • Пример: если 3 строки имеют одинаковое значение, они получат ранг 1, и следующий ранг будет 4 (пропуская 2 и 3).

• DENSE_RANK():

  • Строки с одинаковыми значениями получают одинаковый ранг.

  • Пример: если 3 строки имеют одинаковое значение, они получат ранг 1, а следующий ранг будет 2 (без пропусков).

Пример демонстрации различий

Предположим, у нас есть таблица employees с колонками employee_id, department, и salary, и мы хотим узнать, как ранжируются сотрудники по зарплате в рамках их отдела:

-- Пример данных
employee_id | department | salary
--------------------------------
1          | Sales      | 60000
2          | Sales      | 60000
3          | Sales      | 50000
4          | Sales      | 40000
5          | Marketing  | 70000
6          | Marketing  | 50000
7          | Marketing  | 50000

Результат ROW_NUMBER()

SELECT employee_id, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

employee_id | department | salary | row_num
------------------------------------------
1          | Sales      | 60000  | 1
2          | Sales      | 60000  | 2
3          | Sales      | 50000  | 3
4          | Sales      | 40000  | 4
5          | Marketing  | 70000  | 1
6          | Marketing  | 50000  | 2
7          | Marketing  | 50000  | 3

Результат RANK()

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

employee_id | department | salary | rank
------------------------------------------
1          | Sales      | 60000  | 1
2          | Sales      | 60000  | 1
3          | Sales      | 50000  | 3
4          | Sales      | 40000  | 4
5          | Marketing  | 70000  | 1
6          | Marketing  | 50000  | 2
7          | Marketing  | 50000  | 2

Результат DENSE_RANK()

SELECT employee_id, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

employee_id | department | salary | dense_rank
--------------------------------------------
1          | Sales      | 60000  | 1
2          | Sales      | 60000  | 1
3          | Sales      | 50000  | 2
4          | Sales      | 40000  | 3
5          | Marketing  | 70000  | 1
6          | Marketing  | 50000  | 2
7          | Marketing  | 50000  | 2

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