Эмуляция функции row_number() в MySQL

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

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

Нумерация строк

Чтобы пронумеровать строки, мы должны объявить переменную запроса. Продемонстрируем этот подход на примере простой таблицы, содержащей список работников предприятия (employees). Следующий запрос выбирает 5 работников из таблицы, присваивая им номера по порядку, начиная с 1:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees
LIMIT 5;

В выше приведённом запросе мы:

• Определили переменную row_number и инициализировали её нулевым значением;
• Увеличивали её значение на 1 при каждой итерации запроса.

Другая техника, позволяющая достичь того же результата, заключается в создании вместо глобальной переменной производной таблицы и перекрёстном объединении этих двух таблиц. Пример такого запроса:

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

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

Возобновление нумерации в группах

Как нам задать отдельную нумерацию для каждой группы строк, объединённых выражением ORDER BY или GROUP BY? Например, как имитировать следующий запрос:

SELECT
    customerNumber, paymentDate, amount
FROM
    payments
ORDER BY customerNumber;

Нам нужно сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы получить требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора клиента из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber as CustomerNumber,
    paymentDate,
    amount
FROM
    payments
ORDER BY customerNumber;

Мы использовали оператор CASE для вычисления условия: если номер клиента остаётся прежним, мы увеличиваем номер строки на 1, в противном случае мы устанавливаем номер строки равным 1. Результат будет тем же, что и на выше приведённом скриншоте.

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

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber as CustomerNumber,
    paymentDate,
    amount
FROM
    payments,(SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY customerNumber;

Итак, мы научились эмулировать нумерацию строк запроса в MySQL.

Сергей Бензенкоавтор-переводчик статьи «MySQL row_number Emulation»