SQL: Функция ROW_NUMBER

Функция ROW_NUMBER, как следует из ее названия, нумерует строки, возвращаемые запросом. С ее помощью можно выполнить более сложное упорядочивание строк в отчете, чем то, которое дает предложение ORDER BY в рамках Стандарта SQL-92.

До появления этой функции для нумерации строк, возвращаемых запросом, приходилось использовать довольно сложный интуитивно непонятный алгоритм, изложенный в параграфе. Единственным достоинством данного алгоритма является то, что он будет работать практически на всех СУБД, поддерживающих стандарт SQL-92.

Естественно, можно выполнить нумерацию средствами процедурных языков, используя при этом курсоры и/или временные таблицы. Но мы здесь говорим о чистом SQL.

Используя функцию ROW_NUMBER можно:

• задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора;
• создать несквозную нумерацию, т.е. выделить - группы из общего множества строк и пронумеровать их отдельно для каждой группы;
• использовать одновременно несколько способов нумерации, поскольку, фактически, нумерация не зависит от сортировки строк запроса.

Проще всего возможности функции ROW_NUMBER показать на простых примерах, к чему мы и переходим.

Пример 1

Пронумеровать все рейсы из таблицы Trip в порядке возрастания их номеров. Выполнить сортировку по {id_comp, trip_no}.

SELECT row_number() over(ORDER BY trip_no) num, 
 trip_no, id_comp
FROM trip
WHERE ID_comp < 3
ORDER BY id_comp, trip_no;

Предложение OVER, с которым используется функция ROW_NUMBER задает порядок нумерации строк. При этом используется дополнительное предложение ORDER BY, которое не имеет отношения к порядку вывода строк запроса. Если вы посмотрите на результат, то заметите, что порядок строк в результирующем наборе и порядок нумерации не совпадают.

Условие отбора id_comp<3 использовано лишь с целью уменьшения размера выборки.

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

ORDER BY trip_no

Или, наоборот, пронумеровать строки в порядке заданной сортировки:

SELECT row_number() over(ORDER BY id_comp, trip_no) num, 
 trip_no, id_comp 
FROM trip
WHERE ID_comp<3
ORDER BY id_comp, trip_no;

А если требуется пронумеровать рейсы для каждой компании отдельно? Для этого нам потребуется еще одна конструкция в предложении OVER - PARTITION BY.

Конструкция PARTITION BY задает группы строк, для которых выполняется независимая нумерация. Группа определяется равенством значений в списке столбцов, перечисленных в этой конструкции, у строк, составляющих группу.

Пример 2

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

SELECT row_number() over(partition BY id_comp ORDER BY id_comp,trip_no) num,
 trip_no, id_comp 
FROM trip
WHERE ID_comp < 3
ORDER BY id_comp, trip_no;

PARTITION BY id_comp означает, что рейсы каждой компании образуют группу, для которой и выполняется независимая нумерация. В результате получим:

Отсутствие конструкции PARTITION BY, как это было в первом примере, означает, что все строки результирующего набора образуют одну единственную группу.

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

SELECT @i:=@i+1 num, trip_no, id_comp
FROM Trip, (SELECT @i:=0) X
WHERE ID_comp < 3
ORDER BY id_comp, trip_no;

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

В первой строке запроса значение переменной инкрементируется на 1, что происходит при вычислении каждой следующей строки в порядке, заданном предложением ORDER BY. В итоге мы получаем нумерацию строк в порядке сортировки.

Если вы опустите инициализацию переменной, то можете получить правильный результат. Но это не гарантировано, в чем можно убедиться, повторно выполнив этот же запрос в текущей сессии соединения с базой данных. Вы должны получить продолжение нумерации с максимального значения переменной @i, достигнутого на предыдущем запуске скрипта.

Мы также можем перенумеровать строки для каждой компании отдельно, т.е. сымитировав поведение PARTITION BY в запросе:

SELECT row_number() over(PARTITION BY id_comp ORDER BY id_comp,trip_no) num,
     trip_no, id_comp 
    FROM Trip
    WHERE ID_comp < 3
    ORDER BY ID_comp, trip_no;

Идея решения состоит в следующем. Введем еще одну переменную для хранения номера компании. При инициализации присвоим ей несуществующий номер (например, 0). Затем для каждой строки будем проверять, совпадает ли номер с номером компании текущей строки. Если значения совпадают, будем инкрементировать, если нет, сбрасывать в 1. Наконец, будем присваивать переменной номер компании из текущей строки. Дело в том, что проверка выполняется до присвоения, тем самым мы сравниваем текущее значение номера компании с номером компании из предыдущей строки (в заданном порядке сортировки). Теперь сам запрос.

SELECT 
CASE WHEN @comp=id_comp THEN @i:=@i+1 ELSE @i:=1 END num,
@comp:=id_comp id_comp, trip_no
    FROM Trip, (SELECT @i:=0, @comp:=0) X
    WHERE ID_comp < 3
    ORDER BY ID_comp, trip_no;

Или, коль скоро вы отошли от стандарта, можно использовать функцию IF, чтобы сократить запись:

SELECT 
IF(@comp=id_comp, @i:=@i+1, @i:=1) num,
@comp:=id_comp id_comp, trip_no
    FROM Trip, (SELECT @i:=0, @comp:=0) X
    WHERE ID_comp < 3
    ORDER BY id_comp, trip_no;