Ранжирование клиентов в MySQL: функция Rank по полу и возрасту

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

Функция RANK() в MySQL применяется для присваивания упорядоченных рангов записям в таблице. Эта функция обозначает одинаковый ранг для строк с идентичными значениями. При этом ранги следующих строк пропускаются в соответствии с числом занятых позиций. Посмотрим на пример применения:

SELECT
  data_field,
  RANK() OVER (ORDER BY data_field DESC) AS rank_col
FROM
  table_name;

В данном случае, ранги назначаются значениям столбца data_field. Дублирующиеся значения рассматриваются как эквивалентные, и сортировка производится по убыванию.

Ручное ранжирование с помощью переменных в MySQL

До выпуска MySQL 8.0 обходное решение, которое позволяло реализовывать ранжирование, основывалось на использовании пользовательских переменных.

Симуляция ранга с помощью переменных

Если требуется сегментировать данные по категориям, например, пол и возраст клиентов, можно искусственно смоделировать ранжирование с помощью переменных:

SET @prev_value = NULL;
SET @rank_count = 0;

SELECT
  first_name,
  age,
  gender,
  CASE
    WHEN @prev_value = gender THEN @rank_count
    WHEN @prev_value := gender THEN @rank_count := @rank_count + 1
  END AS rank
FROM
  (SELECT first_name, age, gender
   FROM customers
   ORDER BY gender, age DESC) AS sorted_data;

Подробнее об этом расскажет наш спикер на видео

Вычисление ранга с использованием самоприсоединения

Такой подход, похожий на пожатие собственной руки – выглядит необычно, но работает:

SELECT
  a.first_name,
  a.age,
  a.gender,
  (SELECT COUNT(*) FROM customers b 
   WHERE b.gender = a.gender AND b.age >= a.age) AS rank
FROM
  customers a;

Упрощение с помощью подзапросов

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

SELECT
  first_name,
  age,
  gender,
  (SELECT COUNT(DISTINCT age) FROM customers c 
   WHERE c.gender = customers.gender AND c.age > customers.age) + 1 AS rank
FROM
  customers;

Одноранговые строки и функция IF()

Одноранговые строки могут затруднить понимание, однако для подобной проблемы в MySQL существует функция IF(). Она позволяет сравнить последовательные значения и назначить им один ранг, если они совпадают:

SELECT
  first_name,
  age,
  gender,
  @curRank := IF(@prevVal = age, @curRank, @curRank + 1) AS rank,
  @prevVal := age
FROM
  customers,
  (SELECT @curRank := 0, @prevVal := NULL) r
ORDER BY
  age DESC;

Углубляемся: методы продвинутого ранжирования

Правильное назначение переменных и работа с отличными от нуля значениями

Правильное управление переменными важно, особенно когда значения исследуемых переменных не равны нулю:

SELECT
  IF(@prevAge IS NOT NULL AND @prevAge = age, @curRank, @curRank := @curRank + 1) AS rank,
  @prevAge := age AS dummy
FROM
  customers,
  (SELECT @curRank := 0) r
ORDER BY
  age DESC;

От ANSI к диалекту MySQL — изменения в SQL

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

Ранжирование в MySQL: особенности

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

Оптимизация: ускоряем ранжирование

Для оптимизации запросов ранжирования важно эффективно группировать и сортировать данные:

SELECT
  first_name,
  age,
  gender,
  @curRank := IF(@prevGender = gender, IF(@prevAge = age, @curRank, @curRank + 1), 1) AS rank,
  @prevAge := age,
  @prevGender := gender
FROM
  customers,
  (SELECT @curRank := 0, @prevAge := NULL, @prevGender := NULL) r
ORDER BY
  gender, age DESC;

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

Применим наш подход к примеру с олимпийским подиумом, где необходимо назначить ранг атлетам в соответствии с их временем финиша. Здесь RANK() выступает судьей:

SELECT athlete_name, performance_time,
       RANK() OVER (ORDER BY performance_time) AS rank
FROM results;

Визуализация этого процесса выглядит следующим образом:

🥇 Самый быстрый спортсмен становится первым, получая ранг 1.
🥈 Следующий спортсмен, с таким же временем, делят с ним первое место, в итоге получая ранг 3.
🥉 И так далее до конца сводной таблицы, как и на церемонии награждения.

Теперь каждый спортсмен знает своё место в рейтинге! 🏆

Разные методы: выбираем способ ранжирования в MySQL

Осознание различий между методами ранжирования в MySQL и стандарте ANSI позволит вам создавать более качественные запросы и развивать универсальные SQL навыки.

Практика на dbfiddle

Платформа dbfiddle.uk предлагает возможность проводить безопасные эксперименты с запросами SQL в условиях реального времени.

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

1. MySQL :: Руководство по MySQL 8.0 :: 12.20 Оконные функции — официальная документация MySQL, описывающая оконные функции.
2. Учебник: использование функции RANK() в MySQL – MySQLTutorial.org — обучающий материал с примерами применения функции RANK().
3. Обучающее видео: Функции RANK и DENSE_RANK в MySQL – YouTube — видеоруководство по функциям RANK и DENSE_RANK.
4. Сравнение функций RANK(), DENSE_RANK() и ROW_NUMBER() – GeeksforGeeks — аналитический обзор различий функций ранжирования.