Информатика · объяснение и практика
RANK и DENSE_RANK: откуда берутся пропуски номеров
Чем RANK отличается от DENSE_RANK: равные баллы, группы сверстников и пропуски мест на собственном примере.

В теме Таблицы, базы данных и SQL · Учебная карта
В маршруте Запросы, ограничения и устойчивое чтение · 10 тем
Главная мысль
RANK присваивает равным значениям место первой строки их группы и оставляет пропуски после совпадений. DENSE_RANK нумерует сами группы без пропусков. Выбор зависит от того, нужны места участников или номера различных результатов.
Сначала определяем, кого считаем равными
Рассмотрим результаты задачи, отсортированные по убыванию балла. Участники с одинаковым значением образуют группу равных по правилу окна. Именно полный список выражений оконного ORDER BY определяет такую группу. Если добавить туда уникальный id, одинаковые баллы перестанут быть равными для ранжирования.
Поэтому правило для спортивных мест и правило для стабильного вывода лучше разделить. Внутри OVER оставляют сравнение баллов, а снаружи можно упорядочить равных участников по id. Так места отражают результат работы, а экран остаётся воспроизводимым и удобным для чтения.
Считаем позиции и группы
Пусть баллы пяти работ равны 14, 11, 11, 11, 8. Первая работа занимает первую позицию, три следующие начинаются со второй позиции. RANK выдаёт им место два. Последняя работа расположена уже на пятой позиции и получает место пять.
Для DENSE_RANK здесь всего три различных результата: четырнадцать, одиннадцать и восемь. Их номера — один, два и три. Число людей внутри средней группы ничего не меняет в номере последней группы. Обе функции сохраняют равенство одинаковых баллов, но отвечают на разные вопросы.
Пример
Баллы: 14,11,11,11,8. RANK: 1,2,2,2,5. DENSE_RANK: 1,2,2,2,3.
После тройного второго места RANK пропускает третье и четвёртое: эти позиции уже заняты строками средней группы. DENSE_RANK считает только три группы.
Проверяем смысл фильтра по месту
Условие «показать первые три места» неоднозначно. По RANK в примере будут выбраны четыре работы с местами один и два; пятое место не проходит. По DENSE_RANK попадут все пять работ, потому что различных групп результатов только три.
Если требуется ровно три строки, ни одна из этих формулировок не гарантирует нужное количество при совпадениях. Тогда необходим технический выбор с ROW_NUMBER и явным разрешением равенств либо отдельное правило отбора. Название функции не заменяет описание задачи: сначала договоритесь о поведении на одинаковых результатах, затем записывайте запрос.
От чтения к действию
А теперь — вы
Частые вопросы
Почему у RANK бывают пропуски?
Место совпавшей группы равно позиции её первой строки. После нескольких равных участников следующая группа начинается дальше.
Можно ли сортировать равных участников по id?
Да, во внешнем ORDER BY. Добавление id внутрь окна изменило бы состав групп равных.
Источники и работа с материалом
Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.
Продолжить разбираться
Серии последовательных номеров: значение минус ROW_NUMBER
Серии последовательных целых чисел в SQLite: вычитаем ROW_NUMBER, группируем одинаковую разность и проверяем уникальность входа.
Открыть темуПлан запроса: что показывает индексный поиск без обещания секунд
Как читать EXPLAIN QUERY PLAN в SQLite: SCAN, SEARCH, покрывающий индекс и проверка гипотезы на настоящем запросе.
Открыть тему