Функция 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

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

Вы можете поэкспериментировать с этими запросами в консоли, выбрав из списка соответствующую СУБД.