SQL-рецепт: ранжирование строк

Это заметка из серии «SQL-рецепты», в которой я описываю решения распространенных задач анализа данных в SQL.

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

Решение — использовать функцию rank() поверх SQL-окна, упорядоченного по целевым столбцам.

Пример

Отранжируем сотрудников из таблицы employees по зарплате:

select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;

Функция rank() присваивает каждому сотруднику ранг в соответствии с его зарплатой (order by salary desc). Обратите внимание, что сотрудники с одинаковой зарплатой получили одинаковые ранги (Леонид и Марина, Вероника и Григорий).

Альтернативы

Можно использовать dense_rank() вместо rank(), чтобы избежать «дыр» в рангах:

select
  dense_rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;

Обратите внимание, что Анна заняла 3-е место, а Ксения 5-е, тогда как раньше они занимали 4-е и 7-е места соответственно.

Совместимость

Все основные СУБД поддерживают оконные функции rank() и dense_rank(). Некоторые из них, такие как MS SQL и Oracle, не поддерживают инструкцию window. В этих случаях можно перенести определение окна в основную часть запроса:

select
  rank() over (
    order by salary desc
  ) as "rank",
  name, department, salary
from employees
order by "rank", id;

Можно переписать запрос вообще без оконных функций:

select
  (
    select count(*)
    from employees as e2
    where e2.salary > e1.salary
  ) + 1 as "rank",
  e1.name, e1.department, e1.salary
from employees as e1
order by "rank", e1.id;

Хотите узнать больше об оконных функциях? Записывайтесь на курс Оконные функции SQL.