Глава 5. Функции ранжирования.

Очень часто возникает вопрос: «Как получить последнюю добавленную в таблицу строку?». Ответом на вопрос будет "никак", если в таблице не предусмотрен столбец, содержащий дату вставки строки, или не используется последовательная нумерация строк, реализуемая во многих СУБД с помощью столбца с автоинрементируемым значением. Тогда можно выбрать строку с максимальным значением даты или счетчика.

Реляционная модель исходит из того факта, что строки в таблице не имеют порядка, являющегося прямым следствием теоретико-множественного подхода. Вопрос о последней строке имеет смысл только в аспекте выдачи результата выполнения запроса, при этом предполагается некоторая сортировка, которая задается с помощью предложения ORDER BY в операторе SELECT. Если никакая сортировка не задана, то полагаться на то, что порядок вывода строк, полученных при выполнении запроса сегодня, останется таким же и завтра, нельзя, т.к. этот порядок зависит от плана, который выбирает оптимизатор запросов для их выполнения. А план может меняться, и зависит это от многих причин, которые мы здесь опустим.

Теоретически каждая строка запроса обрабатывается независимо от других строк. Однако на практике часто требуется при обработке строки соотносить ее с предыдущими или последующими строками (например, для получения нарастающих итогов), выделять группы строк, обрабатываемые независимо от других и т.д. В ответ на потребности практики в ряде СУБД в языке SQL появились соответствующие конструкции, в частности, функции ранжирования и оконные (аналитические) функции, которые де-юре были зафиксированы в стандарте SQL:2003. В SQL Server ранжирующие функции появились в версии 2005.

5.1. Функция row_number.

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

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

задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора;

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

использовать одновременно несколько способов нумерации, поскольку, фактически, нумерация не зависит от сортировки строк запроса.

Покажем возможности функции ROW_NUMBER на простых примерах.

Пример 42.

Пронумеровать все блюда из таблицы Блюда в алфавитном порядке. Выполнить сортировку по {Основа, Блюдо}.

SELECT row_number() over(ORDER BY Блюдо) as Номер, Блюдо, Основа

FROM Блюда

WHERE Вид < 2

ORDER BY Основа, Блюдо;

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

Результат
Номер	Блюдо	Основа
7	Сметана	Молоко
8	Творог	Молоко
1	Мясо с гарниром	Мясо
5	Салат мясной	Мясо
3	Салат витаминный	Овощи
4	Салат летний	Овощи
2	Паштет из рыбы	Рыба
6	Салат рыбный	Рыба

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

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

Пример 43.

Пронумеровать блюда в рамках каждой основы отдельно.

SELECT row_number() over(partition BY Основа ORDER BY Блюдо) as Номер, Блюдо, Основа

FROM Блюда

WHERE Вид < 2

ORDER BY Основа, Блюдо;

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

Результат
Номер	Блюдо	Основа
1	Сметана	Молоко
2	Творог	Молоко
1	Мясо с гарниром	Мясо
2	Салат мясной	Мясо
1	Салат витаминный	Овощи
2	Салат летний	Овощи
1	Паштет из рыбы	Рыба
2	Салат рыбный	Рыба

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