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, которое не имеет отношения к порядку вывода строк запроса. Если вы посмотрите на результат, то заметите, что порядок строк в результирующем наборе и порядок нумерации не совпадают.

num trip_no id_comp 3 1181 1 4 1182 1 5 1187 1 6 1188 1 7 1195 1 8 1196 1 1 1145 2 2 1146 2

Условие отбора 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;

num trip_no id_comp 1 1181 1 2 1182 1 3 1187 1 4 1188 1 5 1195 1 6 1196 1 7 1145 2 8 1146 2

А если требуется пронумеровать рейсы для каждой компании отдельно? Для этого нам потребуется еще одна конструкция в предложении 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 означает, что рейсы каждой компании образуют группу, для которой и выполняется независимая нумерация. В результате получим:

num trip_no id_comp 1 1181 1 2 1182 1 3 1187 1 4 1188 1 5 1195 1 6 1196 1 1 1145 2 2 1146 2

Отсутствие конструкции 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;

num id_comp trip_no 1 1 1181 2 1 1182 3 1 1187 4 1 1188 5 1 1195 6 1 1196 7 2 1145 8 2 1146 В третьей строке запроса выполняется инициализация переменной и присваивается ей начальное значение. В итоге каждая строка таблицы 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;

num id_comp trip_no 1 1 1181 1 2 1145 2 1 1182 2 2 1146 3 1 1187 4 1 1188 5 1 1195 6 1 1196

Или, коль скоро вы отошли от стандарта, можно использовать функцию 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;

На этой странице