Эмуляция функции ROW_NUMBER() в MySQL: решение на примерах

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

SELECT 
  *, ROW_NUMBER() OVER (ORDER BY id) AS `row_num`
FROM 
  your_table;

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

Эмуляция ROW_NUMBER() в ранних версиях MySQL

В версиях MySQL до 8.0 можно было воспользоваться пользовательскими переменными, чтобы обойтись без ROW_NUMBER(). Вот пример такой эмуляции:

SET @row_number = 0;
SELECT 
  (@row_number:=@row_number + 1) AS row_num, t.*
FROM 
  (SELECT * FROM your_table ORDER BY id) AS t;

Здесь каждой строке присваивается порядковый номер путём увеличения переменной на единицу, что позволяет создать эффективный аналог функции ROW_NUMBER.

Разделение данных на группы

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

SET @row_number = 0;
SELECT *, 
  (@row_number:=CASE WHEN @prev_value = partition_column THEN @row_number + 1 ELSE 1 END) AS `row_num`,
  @prev_value := partition_column
FROM your_table,
  (SELECT @prev_value:=NULL) AS init
ORDER BY 
  partition_column, order_criteria;

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

Вспомните гонку:

🏁🏃💨----🏃💨----🏃💨---- |🏅 Финишная черта

SELECT name, 
       ROW_NUMBER() OVER (ORDER BY finish_time ASC) as position
FROM runners;

Результаты после финиша:

| Бегун   | Позиция после забега |
|----------|----------------------|
| Джон     | 🥇 1                 |
| Сара     | 🥈 2                 |
| Майкл    | 🥉 3                 |

Как и в случае с гонкой, каждой строке (бегуну) присваивается уникальный номер позиции в соответствии со временем финиша.

Решения для ранних версий MySQL

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

Максимальные значения через самосоединение

Чтобы выделить строку с наибольшим значением в группе, используйте следующее самосоединение:

SELECT a.*
FROM your_table a
LEFT JOIN your_table b 
ON a.group_column = b.group_column AND a.rank_criteria < b.rank_criteria
WHERE b.rank_criteria IS NULL;

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

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

Групповой максимум без аналитических функций

Можно найти групповой максимум даже без аналитических функций:

SELECT a.*
FROM your_table a
WHERE 
  a.rank_criteria = (
    SELECT MAX(b.rank_criteria)
    FROM your_table b
    WHERE a.group_column = b.group_column
  );

Сортировка с использованием пользовательских переменных

Создание собственной системы сортировки возможно благодаря переменным:

SELECT
  name,
  IF(@prev != category, @rn:=1, @rn:=@rn+1) as rank,
  @prev:=category as category
FROM
  items,
  (SELECT @rn:=0, @prev:=NULL) as vars
ORDER BY
  category,
  value DESC;

Здесь переменная @rn сбрасывается на 1, если меняется категория, при этом предыдущая категория отслеживается с помощью переменной @prev.

Мудрости и потенциальные подводные камни

• Важен порядок вывода результатов для корректного ранжирования.
• Будьте аккуратны с пользовательскими переменными в MySQL, они могут привести к сбоям.
• Тестирование кода — залог его надёжности. Уделите внимание граничным значениям, таким как NULL или дубликаты.

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

1. MySQL :: Руководство по MySQL 8.0 :: 12.20.1 Описание функций окон — официальная документация MySQL по ROW_NUMBER().
2. sql – ROW_NUMBER() в MySQL – Stack Overflow — обсуждение эмуляции ROW_NUMBER() в ранних версиях MySQL на Stack Overflow.
3. sql – Функция ранжирования в MySQL – Stack Overflow — обсуждение альтернативных методов ранжирования на Stack Overflow.
4. Видеоурок: ROW_NUMBER() в MySQL 8.0 и использование CTE — видеоурок об использовании ROW_NUMBER() в сочетании с CTE в MySQL 8.0.
5. ROW_NUMBER – База знаний MariaDB — информация об использовании ROW_NUMBER() в MariaDB.
6. Наглядное объяснение пагинации в MySQL 8 с использованием ROW_NUMBER() — учебный видеоматериал по пагинации в MySQL 8 с использованием функции ROW_NUMBER().