Новое в стандарте и реализациях языка SQL

Функции ранжирования

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

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

Используя функцию 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

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

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

Консоль

Выполнить

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

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

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