Отличия функций RANK() и DENSE_RANK() в Oracle, учёт NULL

Быстрый ответ

Основное различие между функциями RANK() и DENSE_RANK() заключается в способе присвоения рангов записям в упорядоченном наборе данных. RANK() творит "пробелы" в ранжированном списке после группы совпадающих значений (например, 1, 1, 3), тогда как DENSE_RANK() продолжает ранжирование без пропусков (например, 1, 1, 2).

SELECT score,
       RANK() OVER (ORDER BY score DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS DenseRank
FROM Scores;

Если в ранговом распределении «скачки» вас не смущают, выбирайте функцию RANK(). Если же важна непрерывность рангов, прибегните к функции DENSE_RANK().

Работа с NULL в функциях RANK() и DENSE_RANK()

Функции RANK() и DENSE_RANK() относятся к NULL как к совпадающим значениям при сортировке. Однако при использовании функции RANK() могут образовываться пробелы в ранговой последовательности. В отличие от этого, DENSE_RANK() обеспечивает непрерывный ряд рангов, как и функция ROW_NUMBER().

Если необходимо найти "n-ую зарплату" в данных, содержащих NULL и дубликаты, лучшим выбором будет DENSE_RANK() – тогда избежать "чёрных дыр" в присвоении рангов удастся гораздо проще, нежели при использовании RANK(), ведущего к возможности пропусков.

Визуализация

Рассмотрим на примере гонки космических кораблей:

🚀 Финишная черта: |---1---|---2---|---3---|---X---| 🌑

RANK():
🥇 | 🥈2️⃣ | 🥈2️⃣ | 🥉4️⃣ | (Корабли с одинаковым временем получают разные ранги)

DENSE_RANK():
🥇 | 🥈2️⃣ | 🥈2️⃣ | 🥈3️⃣ | (Корабли с одинаковым временем получают одинаковые ранги)

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

• RANK(): Для неодинаковых результатов сохраняются уникальные ранги.
• DENSE_RANK(): Одинаковые результаты ведут к получению одинаковых рангов.

Непрерывное ранжирование и ранжирование с пропусками

Ранжирование без пропусков

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

"Прыжки" в рейтинге

RANK() подойдет, когда требуется выделить уровни производительности, сохраняя отдельные ранги даже при совпадающих результатах.

Повышенное ранжирование

Ранжирование по отделам

Для оценки производительности по отделам используйте конструкцию OVER (PARTITION BY deptno ORDER BY sal).

Влияние NULL на ранжирование

NULL-значения могут серьезно исказить распределение рангов при использовании RANK(), что может привести к непредсказуемым последствиям.

Выбор способа ранжирования: RANK() или DENSE_RANK()?

• Учитываются ли дубликаты?
• Как NULL-значения могут повлиять на распределение рангов?
• Требуется ли непрерывная последовательность рангов?
• Насколько важно сохранять разные ранги при одинаковых результатах?

Дополнительные возможности

RANK() и DENSE_RANK() значительно увеличивают аналитические возможности SQL, особенно при работе с большими объемами данных.

Понимание принципов ранжирования

Овладение техникой ранжирования поможет вам поднять анализ данных на новый уровень.

Использование ROW_NUMBER()

Когда вам необходим уникальный номер каждой строки, не учитывающий дубликаты, стоит выбрать ROW_NUMBER().

Полезные материалы

1. RANK — Официальная документация Oracle для функции RANK().
2. DENSE_RANK — Официальная документация Oracle для функции DENSE_RANK().
3. SQL Window Functions | Advanced SQL – Mode — Исчерпывающая информация о функциях окна в SQL, включая функции ранжирования.
4. sql server – SQL RANK() versus ROW_NUMBER() – Stack Overflow — Дискуссия о различиях между RANK() и DENSE_RANK().
5. Phantom Planet – California (Official Music Video) — Видеообзор с иллюстрацией функций ранжирования.