Функция ROW_NUMBER

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

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

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

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

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

Пример 1.

Решение

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

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

order by trip_no

(или, что то же самое, order by num ).

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

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

Рекомендуемые упражнения: 65, 97, 116, 125, 130, 137

В 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;

Проверить эти запросы вы можете из консоли, выбрав из списка MySQL.