Как пронумеровать строки в запросе oracle

Обновлено: 07.07.2024

ROWNUM - волшебный столбец базы данных Oracle, доставляющий много хлопот. Но когда вы узнаете, что это такое и как работает, ROWNUM может оказаться полезным. Я использую его для достижения двух целей:

1. Выполните обработку TOP-N. Это похоже на функцию оператора Limit в других базах данных.

После того, как я рассмотрю, как работает ROWNUM, я рассмотрю эти способы использования.

2. КАК РАБОТАЕТ ROWNUM

ROWNUM - это виртуальный столбец (не настоящий столбец), Может использоваться в запросах. ROWNUM будет преобразован в числа 1, 2, 3, 4, . N, где N - количество строк в наборе, используемом с ROWNUM. Значение ROWNUM не всегда будет присвоено строке (записи). (Это распространенное заблуждение). Строке (записи) в таблице нет соответствующего номера, вы не можете попросить взять пятую строку из таблицы - такого нет.

Также многих сбивает с толку, когда фактически присваивается значение ROWNUM.Значение ROWNUM присваивается строке после прохождения фазы предиката запроса, но до того, как запрос выполнит какую-либо сортировку или агрегирование.. Кроме того, значение ROWNUM увеличивается только после выделения, поэтому следующий запрос никогда не возвращает строки:

Поскольку первая строка в таблице не удовлетворяет требованиям ROWNUM> 1, ROWNUM не вырастет до 2. Следовательно, никогда не будет ситуации, когда ROWNUM больше 1.
Рассмотрим запрос со следующей структурой:

Вышеуказанные операторы будут выполняться в следующем порядке:

Оператор FROM / WHERE выполняется первым
ROWNUM выделяется и увеличивается на каждую выходную строку предложения FROM / WHERE.
SELECT применяется
GROUP BY применяется
HAVING применяется
ORDER BY применяется

Вот почему запрос следующей формы почти наверняка является ошибкой:

Вышеупомянутый запрос изначально был предназначен для поиска 5 самых высокооплачиваемых людей - запрос TOP N. Тем не менее, запрос вернет пять случайных записей (первые пять, с которыми он столкнулся),
Сортировать по зарплате. Псевдокод процесса запроса выглядит следующим образом:

Он получает первые пять записей и затем сортирует их. Запрос с WHERE ROWNUM = 5 или WHERE ROWNUM> 5 не имеет смысла. Это связано с тем, что значение ROWNUM присваивается строке во время оценки предиката и увеличивается только после того, как строка передает предложение WHERE.
Правильная версия этого запроса:

Эта версия отсортирует EMP в порядке убывания зарплаты, а затем вернет первые пять обнаруженных записей (первые пять записей). Как вы увидите в предстоящих обсуждениях Top N, Oracle Database на самом деле не сортирует весь набор результатов - он умнее этого - но концептуально именно это и произошло.

3. Обработка запросов Top-N с ROWNUM (Обработка запросов Top-N с ROWNUM)

В запросе TOP-N вы обычно хотите выполнить сложный запрос, отсортировать, а затем получить N верхних строк (верхние N строк).
ROWNUM имеет N лучших оптимизаций, которые могут облегчить этот тип запроса. Вы можете использовать ROWNUM, чтобы избежать сортировки больших коллекций. Я расскажу, как это делается концептуально, а затем рассмотрю пример.
Предположим, у вас есть запрос в такой форме:

Предположим, этот запрос возвращает большой объем данных: тысячи, сотни тысяч или более строк. Однако вас интересуют только Top-N (например, топ-10 или топ-100). Есть два способа решить эту проблему:

Пусть клиентское приложение выполнит запрос и получит только первые N строк.
Используйте запрос как встроенное представление и используйте ROWNUM для ограничения результатов, например SELECT * FROM（your_query_here）WHERE ROWNUM <= N.

По двум причинам второй метод намного превосходит первый. Меньшая из этих двух причин состоит в том, что клиенту требуется меньше работы, поскольку база данных отвечает за ограничение набора результатов. Более важная причина заключается в том, что специальная обработка, которую может выполнить база данных, может предоставить вам только первые N строк. Использование запросов top-N означает, что вы предоставили базе данных дополнительную информацию. Вы говорите ему: «Меня интересует только N строк; остальные я никогда не буду рассматривать». Теперь, прежде чем вы подумаете о сортировке - как работает сортировка и что нужно делать серверу, это не звучит слишком шокирующе. Давайте обсудим эти два метода с примерами запросов:

Теперь предположим, что T - это большая таблица с более чем одним миллионом записей, и каждая запись «толстая», то есть 100 или более байтов. Также предполагается, что UNINDEXED_COLUMN, как следует из названия, является неиндексированным столбцом. Предположим, вы хотите получить только первые 10 строк. База данных Oracle будет выполнять следующие операции:

Запустить полное сканирование таблицы на T
Сортировать по UNINDEXED_COLUMN T. Это полная сортировка.
У вас должна закончиться память области сортировки, и вам потребуется переместить временный экстент на диск.
Объедините временную область расширения, чтобы получить первые 10 записей во время запроса.
Очистите (освободите) область временного расширения после завершения.

Теперь это создает много операций ввода-вывода. База данных Oracle, вероятно, скопирует всю таблицу в TEMP и запишет ее, просто чтобы получить первые 10 строк .

Затем давайте посмотрим, что может делать база данных Oracle концептуально с помощью запросов top-N:

В этом случае база данных Oracle предпримет следующие шаги:

Выполните полное сканирование таблицы на T, как и раньше (вы не можете избежать этого шага).
В массиве из N элементов (возможно, на этот раз в памяти) отсортируйте только: N строк.

Первые N строк заполнят этот массив строк в отсортированном порядке. При получении N + 1 строк она будет сравниваться с последней строкой в массиве. Если он входит в слот N + 1 в массиве, он будет брошен. В противном случае он будет добавлен в этот массив и отсортирован, а одна из существующих строк будет отброшена. Ваша область сортировки содержит не более N строк, поэтому вы можете отсортировать N строк вместо сортировки миллиона строк.

Кажущиеся незначительными детали использования концепции массива и сортировки только N строк могут принести огромный выигрыш в производительности и использовании ресурсов. ОЗУ, необходимое для сортировки 10 строк, меньше ОЗУ, необходимого для сортировки 1 миллиона строк (не говоря уже об использовании пространства TEMP).

Используя приведенную ниже таблицу T, вы можете увидеть, что, хотя эти два метода достигают одинаковых результатов, они используют совершенно разное количество ресурсов:

Затем используйте ROWNUM для выполнения вашего верхнего N запроса:

Наконец, запустите запрос «сделай сам», чтобы получить только первые 10 записей:

call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.04 0 949 0 10

total 4 0.04 0.04 0 949 0 10

Rows Row Source Operation

10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10 VIEW (cr=949 pr=0 pw=0 time=46979 us)
10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)

Запрос читает всю таблицу (потому что он должен), но с помощью шага SORT ORDER BY STOPKEY он может временно
Использование пространства ограничено 10 строками. Обратите внимание на последнюю строку операции источника строки -
Он показывает, что запрос выполнил в общей сложности 949 логических операций ввода-вывода (cr = 949),
не выполнял физическое чтение или запись (pr = 0 и pw = 0),
, и это заняло 400066 миллионных долей секунды (0,04 секунды).
Сравните его с самостоятельным методом, показанным в листинге 2.

3.2 Листинг 2: запрос "Сделай сам" без ROWNUM

call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 10 0.35 0.40 155 949 6 10

total 13 0.36 0.40 155 949 6 10

Rows Row Source Operation

10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)

Истекшее время включает ожидание следующих событий:
Event waited on Times

direct path write temp 33
direct path read temp 5
Как видите, этот результат сильно отличается. Стоит отметить, что затраченное время / процессорное время значительно выше, а последняя строка исходной операции строки дает представление о ее причине. Вы должны отсортировать диск, вы можете использовать pw = 891 (физическая запись) для просмотра. Ваш запрос выполнил несколько операций чтения и записи по прямому пути - 100 000 записей (а не только 10, которые нам в конечном итоге были интересны) произошли на диске, что значительно увеличило время выполнения / использование ресурсов запроса.

4. Используйте подкачку ROWNUM

Мое любимое использование ROWNUM - пейджинг. В этом случае я использую ROWNUM для получения строк от N до M. Общий вид выглядит следующим образом:

FIRST_ROWS (N) сообщает оптимизатору: «Эй, я хочу получить первую строку. Я получу N как можно быстрее».

: MAX_ROW_TO_FETCH устанавливается в последнюю строку набора результатов для получения - если вы хотите от 50 до 60 строк набора результатов, установите его на 60.

: MIN_ROW_TO_FETCH устанавливается на первую строку получаемого набора результатов, поэтому для получения строк с 50 по 60 вы можете установить его на 50.

Концепция этого сценария заключается в том, что конечный пользователь с веб-браузером завершил поиск и ожидает результатов. Первая страница результатов (и вторая страница и т. Д.) Должна быть возвращена как можно скорее. Если вы внимательно посмотрите на запрос, вы заметите, что он содержит верхние N запросов (получите первый из запроса: MAX_ROW_TO_FETCH rows), так что вы можете извлечь выгоду из оптимизации первых N запросов, которую я только что описал. Кроме того, он возвращает только определенные строки, представляющие интерес для клиента по сети, - он удаляет все ведущие строки, которые не представляют интереса, из набора результатов.
При использовании этого запроса на разбиение на страницы важно, чтобы оператор ORDER BY был отсортирован в уникальном порядке. Если сортируемые вами вещи не уникальны, вам следует добавить что-нибудь в конце ORDER BY, чтобы добиться этого. Например, если вы отсортируете 100 записей по SALARY, и все они будут иметь одинаковое значение SALARY, то указание строк с 20 по 25 фактически не имеет смысла. Чтобы проиллюстрировать этот момент, здесь мы используем небольшую таблицу с большим количеством повторяющихся значений идентификаторов:

Затем запросите строки с 148 по 150 и 151 после сортировки по столбцу идентификатора:

0 38 148
0 64 149
0 53 150

0 59 148
0 38 149
0 64 150
0 53 151
Обратите внимание, что в этом случае для одной из строк 148 результат возвращает DATA = 38, а в следующий раз результат возвращает DATA = 59. В соответствии с вашими требованиями оба запроса возвращают правильный ответ: отсортируйте данные по идентификатору, выбросите первые 147 строк, а затем верните следующие 3 или 4 строки.Оба они делают это, но поскольку идентификатор имеет много повторяющихся значений, запрос не может быть выполнен детерминированно - один и тот же порядок сортировки не может быть гарантирован от выполнения к запуску запроса. Чтобы решить эту проблему, вам нужно добавить контент, уникальный для ORDER BY. В этом случае просто используйте ROWID:

0 45 148
0 99 149
0 41 150

0 45 148
0 99 149
0 41 150
0 45 151
Теперь запрос очень детерминированный. ROWID уникален в таблице, поэтому, если вы используете ORDER BY ID
Затем используйте ORDER BY ROWID в идентификаторе, строки будут иметь четкий детерминированный порядок, и запрос будет разбит на страницы
вернет строки, как ожидалось, с уверенностью.

Пять: сводка ROWNUM

В этой статье я расскажу вам, как пронумеровать строки результата запроса, возвращаемого MySQL .

Функция row_number() – это функция ранжирования, возвращающая порядковый номер строки, начиная с 1 для первой строки. Номер строки часто бывает нужен при генерации отчётов. Эта функция реализована в MS SQL и в Oracle . В MySQL подобная функция отсутствует, но её несложно реализовать за счёт глобальных переменных.

Нумерация строк

Чтобы пронумеровать строки, мы должны объявить переменную запроса. Продемонстрируем этот подход на примере простой таблицы, содержащей список работников предприятия ( employees ). Следующий запрос выбирает 5 работников из таблицы, присваивая им номера по порядку, начиная с 1:

В выше приведённом запросе мы:

Определили переменную row_number и инициализировали её нулевым значением;
Увеличивали её значение на 1 при каждой итерации запроса.

Другая техника, позволяющая достичь того же результата, заключается в создании вместо глобальной переменной производной таблицы и перекрёстном объединении этих двух таблиц. Пример такого запроса:

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

Возобновление нумерации в группах

Как нам задать отдельную нумерацию для каждой группы строк, объединённых выражением ORDER BY или GROUP BY ? Например, как имитировать следующий запрос:

Нам нужно сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы получить требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора клиента из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:

Мы использовали оператор CASE для вычисления условия: если номер клиента остаётся прежним, мы увеличиваем номер строки на 1, в противном случае мы устанавливаем номер строки равным 1. Результат будет тем же, что и на выше приведённом скриншоте.

Теперь добьёмся того же результата, используя технику производной таблицы и перекрёстного запроса:

В данном посте описываются способы добавления функционала автоматической нумерации к таблице в базах данных Oracle.

Автоматическая нумерация или автоинкремент (в некоторых системах управления базами данных, например, в MySQL, для автоматической нумерации используется понятие автоинкремент) – это автоматическое увеличение значения колонки в таблице, чаще всего колонки, содержащей значения первичных ключей – Primary Key (обязательны к заполнению и должны быть уникальны). Можно прописывать эти значения вручную, но в промышленной среде, когда в таблицы записывается много данных, запоминать предыдущее значение и сохранять уникальность значений трудно, что скажется на скорости заполнения таблиц. Для промышленной среды такая ситуация недопустима, и в целом – очень неудобна.

Ручная нумерация

Рассматривается пример с ручным вводом данных для значений колонки с primary key.

Шаг 1. Создается таблица test с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

Шаг 2. Добавляются данные в таблицу test.

Данные (2 строки) успешно добавлены. Далее будет выполнена попытка добавления третьей строки со значением NULL и без указания данных для колонки id.

Добавление третьей строки с указанием NULL:

и добавление строки без указания данных для колонки id:

и в обоих случаях система выдаст следующую ошибку:

Данная ошибка (ORA-01400) возникает из-за того, что новая добавляемая строка не содержит значение для колонки id. Колонка id таблицы test является первичным ключом (primary key) и требуется обязательное указание уникального значения.

Если добавить следующую строку,

то система выдаст ошибку:

Данная ошибка (ORA-00001) возникает из-за того, что нарушено условие уникальности значений в колонке id. В таблице уже есть строка со значением 2 в колонке id. Данный тип ошибки возникает при попытке выполнить команды INSERT или UPDATE, которые пытаются вставить дублирующее значение в колонку с ограничением по уникальности значений.

Шаг 3. Проверка данных в таблице.

В таблице test после всех вышеперечисленных операций будут следующие 2 строки данных:

Автоматическая нумерация.

Общий принцип механизма автоматической нумерации можно описать следующим образом: внутри базы к каждой таблице создается и привязывается отдельный счетчик, который увеличивается на единицу при вставке новой строки, а получившееся значение записывается в ту колонку, которое помечено как автонумерация (автоинкремент).

У различных СУБД этот механизм реализован по-своему. В данном посте рассматривается применение данного механизма применительно к СУБД Oracle.

В Oracle автонумерацию можно реализовать несколькими способами. Например, с помощью:

Последовательности (Sequence),
Комбинации последовательности и триггера (Trigger),
Использования колонки с IDENTITY.

Ниже рассматривается каждый способ с примерами.

1. Автоматическая нумерация с помощью последовательности (Sequence).

Для баз данных, использующих Oracle Database версии до 12c одним из способов реализации является sequence (последовательность). Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда нужно создать уникальный номер в качестве значения первичного ключа.

Шаг 1. Создается таблица test_sequence с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

Шаг 2. Ниже создается последовательность с названием t_sequence. Значение данной последовательности начинается с единицы и каждое новое сгенерированное число будет увеличиваться с шагом 1.

Последовательность создана. Синтаксис создания последовательности позволяет, кроме start with и increment by, задать также и другие параметры. Например, maxvalue, minvalue, cycle, cache и т.д. Но в данном посте для упрощения задачи они опускаются.

Шаг 3. Добавляются данные в таблицу test_sequence. Вместо значения id прописывается параметр nextval последовательности t_sequence (t_sequence.nextval).

Все данные успешно добавлены.

Шаг 4. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3, то есть значения уникальны и параметр nextval последовательности вставил их вместо ручного ввода пользователем.

2. Автоматическая нумерация с помощью комбинации последовательности и тригерра.

Шаг 1. Создается таблица test_sequence_trigger с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

Шаг 2. Ниже создается последовательность с названием tt_sequence. Значение последовательности начинается с единицы и каждое новое сгенерированное число будет увеличиваться с шагом 1.

Шаг 3. Создается триггер tt_trigger для таблицы test_sequence_trigger. Каждый раз, перед тем как данные будут добавляться во время выполнения оператора INSERT в таблицу test_sequence_trigger, триггер с именем tt_trigger будет запрашивать число из созданной последовательности tt_sequence и подставлять его в колонку id.

Шаг 4. Выполняется вставка данных в таблицу test_sequence_trigger.

Шаг 5. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3.

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

3. Автоматическая нумерация с помощью использования колонки с IDENTITY.

Начиная с версии Oracle Database 12c есть возможность создать колонку с IDENTITY для автоматической нумерации.

Колонку с типом IDENTITY можно объявить при создании таблицы, либо изменить существующую таблицу, добавив в неё колонку с типом IDENTITY используя DDL команду ALTER TABLE.

Общий синтаксис создания таблицы с колонкой IDENTITY приведен ниже:

GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY [sequence_options,…]

Колонку IDENTITY можно создать с одной из трех опций генерации значений:

GENERATED BY DEFAULT AS IDENTITY – Генератор последовательностей добавляет значение в колонку IDENTITY каждый раз, когда значение не предоставлено вручную.
GENERATED BY DEFAULT ON NULL AS IDENTITY – Генератор последовательностей добавляет следующее значение колонки IDENTITY, если будет вручную указано значение NULL для колонки.
GENERATED ALWAYS AS IDENTITY – Генератор последовательностей всегда добавляет значение в колонку IDENTITY. Не предоставляется возможности вручную указать значение.

START WITH initial_value – эта опция контролирует первое (начальное) значение (число) для колонки IDENTITY. По умолчанию, это значение равно 1.
INCREMENT BY internval_value – эта опция определяет с каким шагом будут генерироваться числа последовательности. По умолчанию, это значение равно 1.

Ниже в примерах описывается применение IDENTITY с различными опциями.

3.1. Опция GENERATED BY DEFAULT AS IDENTITY.

Шаг 1. Создание таблицы test_identity с опцией GENERATED BY DEFAULT AS IDENTITY для колонки id.

Шаг 2. Добавление данных в таблицу test_identity.

Данные успешно добавлены.

Если попытаться вставить в колонку с типом IDENTITY значение NULL:

то система выдаст следующую ошибку:

Опция GENERATED BY DEFAULT AS IDENTITY не позволяет вставлять значения типа NULL в колонку с типом IDENTITY. Если значение для колонки не указано, то генератор последовательностей (Sequence Generator) будет использовать свое значение.

Шаг 3. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3.

Шаг 4. Удаляется таблица test_identity для выполнения теста со следующей опцией IDENTITY (п. 3.2).

3.2. Опция GENERATED BY DEFAULT ON NULL AS IDENTITY.

Шаг 1. Создание таблицы test_identity с опцией GENERATED BY DEFAULT ON NULL AS IDENTITY для колонки id.

Шаг 2. Добавление данных в таблицу test_identity.

Данные успешно добавлены.

Выше были вставлены значения NULL в колонку с типом IDENTITY и не было ошибки (в отличие от опции пункта 3.1). Опция GENERATED BY DEFAULT ON NULL AS IDENTITY позволяет вставлять значения типа NULL в колонку IDENTITY.

Шаг 3. Проверяется содержимое таблицы.

Шаг 4. Удаляется таблица test_identity для выполнения теста со следующей опцией IDENTITY (п. 3.3).

3.3. Опция GENERATED ALWAYS AS IDENTITY.

Шаг 1. Создание таблицы с опцией GENERATED ALWAYS AS IDENTITY для колонки id.

Шаг 2. Добавление данных в таблицу test_identity.

Шаг 3. Проверяется содержимое таблицы. Как видно из выборки, в колонке id автоматически вставились значения для двух успешно добавленных строк.

Далее выполняется ввод данных из двух сессий без выполнения команды commit для проверки принципа работы генератора последовательности с опцией GENERATED ALWAYS AS IDENTITY.

3.3.1 Первая сессия:

Выполняется вставка новой строки:

Строка добавлена. Выполняется выборка данных из таблицы:

Как видно, пользователь первой сессии после вставки новой строки видит 3 строки данных.

3.3.2. Ввод и выборка данных второй сессии:

Выполняется вставка новой строки второй сессии:

Строка успешно добавлена. Выполняется выборка данных второй сессии. Пользователь данной сессии для новой строки получает значение 4 для колонки id с учетом того, что генератор последовательности (Sequence Generator) уже выдал значение 3 для пользователя первой сессии.

После выполнения commit в каждой сессии можно получить такой результат из сессии любого пользователя:

Это означает, что данные, добавленные в первой сессии и данные, добавленные во второй сессии, после совершения commit объединились в одной таблице, сохранив лишь уникальные значения для колонки id.

3.3.3. Применение атрибутов Sequence Generator.

Как было описано ранее в данном посте, при использовании IDENTITY можно задать значение параметров генератора последовательностей (Sequence Generator). Приводится пример создания таблицы, где определяются значения двух параметров Sequence Generator – start with и increment by. В приведенном ниже примере генератор последовательности будет генерировать числа для колонки id начиная с числа 5 и каждое следующее число будет увеличиваться с шагом 5:

Далее выполняется вставка данных для проверки генерируемых значений.

Выполняется выборка данных из таблицы:

Результат запроса показывает, что генератор последовательности для колонки id с IDENTITY сгенерировал числа начиная с пяти и каждое последующее число увечил на пять.

На этом завершается описание трех способов реализации автоматической нумерации (автоинкремент) в базах данных Oracle.

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

Функции ROW_NUMBER и RANK похожи. ROW_NUMBER нумерует все строки по порядку (например, 1, 2, 3, 4, 5). RANK назначает одинаковое числовое значение строкам, претендующим на один ранг (например, 1, 2, 2, 4, 5).

ROW_NUMBER — это временное значение, вычисляемое во время выполнения запроса. Сведения о хранении номеров в таблице см. в разделах Свойство IDENTITY и SEQUENCE.

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

PARTITION BY value_expression
Делит результирующий набор, полученный от предложения FROM, на секции, к которым применяется функция ROW_NUMBER. value_expression определяет столбец, по которому секционируется результирующий набор. Если параметр PARTITION BY не указан, функция обрабатывает все строки результирующего набора запроса как одну группу. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

order_by_clause
Предложение ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции. Оно должно указываться обязательно. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

Типы возвращаемых данных

bigint

Общие замечания

Нет гарантии того, что строки, возвращенные запросом, использующим ROW_NUMBER() , будут расставлены в одинаковом порядке после каждого выполнения, если не соблюдены указанные ниже условия.

Все значения в секционированном столбце являются уникальными.

Все значения в столбцах ORDER BY являются уникальными.

Сочетания значений из столбца секционирования и столбцов ORDER BY являются уникальными.

Функция ROW_NUMBER() не детерминирована. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.

Примеры

A. Простые примеры

Приведенный ниже запрос возвращает четыре системные таблицы в алфавитном порядке.

name	recovery_model_desc
master	ПРОСТОЙ
model	FULL
msdb	ПРОСТОЙ
tempdb	ПРОСТОЙ

Номер строки	name	recovery_model_desc
1	master	ПРОСТОЙ
2	model	FULL
3	msdb	ПРОСТОЙ
4	tempdb	ПРОСТОЙ

Добавление предложения PARTITION BY для столбца recovery_model_desc приведет к тому, что нумерация начнется заново при изменении значения recovery_model_desc .

Номер строки	name	recovery_model_desc
1	model	FULL
1	master	ПРОСТОЙ
2	msdb	ПРОСТОЙ
3	tempdb	ПРОСТОЙ

Б. Возврат номера строки для salespeople

В следующем примере показан расчет номера строки для salespeople в Компания Adventure Works Cycles, выполняемый на основе ранжирования продаж за текущий год.

В. Возврат подмножества строк

В следующем примере показан расчет номеров всех строк в таблице SalesOrderHeader в порядке OrderDate с последующим возвращением строк с номерами от 50 до 60 включительно.

Г. Использование ROW_NUMBER() с PARTITION

В следующем примере аргумент PARTITION BY используется для секционирования результирующего набора запроса по столбцу TerritoryName . Предложение ORDER BY , указанное в предложении OVER , упорядочивает строки каждой секции по столбцу SalesYTD . Предложение ORDER BY в инструкции SELECT упорядочивает полный результирующий набор запроса по TerritoryName .

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Д. Возврат номера строки для salespeople

В приведенном ниже примере возвращается ROW_NUMBER для торговых представителей в зависимости от установленной для них квоты продаж.

Здесь приводится частичный результирующий набор.

Е. Использование ROW_NUMBER() с PARTITION

Следующий пример демонстрирует использование функции ROW_NUMBER с аргументом PARTITION BY . В результате функция ROW_NUMBER нумерует строки в каждой секции.

Аналитические функции - очень мощный инструмент в SQL. Со слов Тома Кайта, можно написать отдельную книгу по аналитическим функциям, настолько они полезны.

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

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

Для примера возьмем данные, которые мы использовали при разборе агрегирующих функций:

Посмотрим, какие данные теперь хранятся в таблице:

Теперь напишем запрос, который бы возвращал максимальный стаж среди всех сотрудников отдельной колонкой. Для этого можно использовать подзапрос:

Усложним задачу: напишем запрос, который будет возвращать отдельной колонкой максимальный стаж на должности каждого сотрудника. Для этого также можно использовать подзапрос, только уже коррелированный:

Теперь решим эти же задачи при помощи аналитический функций:

Аналитические функции позволяют использовать агрегирующие функции без подзапросов, что уменьшает размер запроса( примеры, когда использование подзапроса усложняет чтение запроса, будут немного дальше).

Помимо этого, вот еще два примера запросов с аналитическими функциями.

Две колонки, max_exp_asc и max_exp_desc , считают максимальный стаж среди сотрудников в порядке возрастания и убывания их имен соответственно.

С простыми примерами аналитических функций мы познакомились, теперь разберемся,как они работают.

Когда агрегирующая функция становится аналитической

В первом примере агрегирующая функция max превратилась в аналитическую после добавления к ней части over() . В итоге, было найдено максимальное значение колонки exp среди всего набора данных, и это значение было добавлено к каждой строке выборки, без группировки.

Подсчет результатов по группам. Partition by

Для того, чтобы результаты считались по определенным группам, нужно использовать конструкцию partition by , в которой нужно указать колонки, по которым будет производиться вычисление.

В данном примере, который уже приводился раньше, максимальный стаж вычисляется в отдельности для каждой из профессий, и затем добавляется к каждой строке.

Посчитаем количество сотрудников по должностям и выведем отдельной колонкой:

Результаты можно считать по нескольким группам. Выведем напротив каждого сотрудника общее число сотрудников, родившихся в том же месяце(колонка mnth_cnt ) и количество сотрудников, родившихся в том же месяце и занимающих такую же должность:

Всего есть три сотрудника, которые родились в одном и том же месяце - июле. Поэтому в колонке mnth_cnt отображается число 3. В то же время, есть лишь два сотрудника, которые родились в одном и том же месяце, и при этом занимают одну и ту же должность - это сотрудники с id равными 2 и 3.

Порядок вычисления. Order by

В аналитических функциях можно указывать порядок, в котором они будут работать с итоговым набором данных. Для этого используется конструкция order by .

Пронумеруем строки в нашей таблице в порядке возрастания и убывания дней рождения сотрудников.

Функция row_number возможно является одной из самых часто используемых аналитических функций. Она возвращает номер строки в итоговой выборке. До ее появления в Oracle подобного функционала можно было достичь лишь при использовании подзапросов и псевдостолбца ROWNUM .

Аналитические функции могут работать не только по группам или в определенном порядке, но и в определенном порядке в пределах заданной группы:

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

Диапазон работы аналитических функций

Аналитические функции всегда, явно или неявно, применяются к определенному набору строк, называемому окном аналитической функции.

Не во всех аналитических функциях можно указывать окно. Среди самых часто используемых функций, для которых можно указывать окно, находятся MIN , MAX , SUM , AVG , COUNT , LAST_VALUE , FIRST_VALUE и другие.

Чтобы примеры были немного более практичными, создадим еще одну таблицу, в которой будем хранить данные о начисленных зарплатах сотрудникам по месяцам:

Общие данные выглядят следующим образом:

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

Сейчас может показаться, что результат, который получился в запросе такой же, как если бы мы и не задавали размер окна. Действительно, для текущих данных запрос без добавления лишних ключевых слов выдает такой же результат:

Чуть позже станет понятно, что это 2 совершенно разных запроса, а пока разберем подробнее различные варианты указания окна в аналитических функциях.

Строки и значения

Строки, которые определяют окно работы аналитической функции, можно указывать физически, т.е. сказать БД: "Для текущей строки в выборке аналитическая функция должна обработать две строки перед ней и три строки после нее"; или: "Для текущей строки в выборке аналитическая функция должна обработать все строки начиная с текущей и заканчивая всеми последующими строками".

Вторым возможным способом определения окна является определение не по физическому расположению строки в выборке, а по значениям, которые строки в себе содержат. Мысленно это можно произнести: "Для текущей строки в выборке аналитическая функция должна обработать те строки, в которых значение колонки А будет больше, чем значение в колонке А текущей строки"; или: "Для текущей строки в выборке аналитическая функция должна обработать те строки, в которых значение колонки А будет в пределах от 10 до 20 включительно".

В первом случае, при указании физических строк, используется ключевое слово ROWS , во втором случае, при указании строк по их значениям, используется ключевое слово RANGE .

Смещения при определении окна

Итак, при указании окна мы должны задать его верхнюю и нижнюю границу.

В общем виде указание границы выглядит следующим образом:

Теперь рассмотрим варианты для этих границ:

UNBOUNDED PRECEDING - указывает, что окно начинается с первой строки в разделе. Может быть указано только для верхней границы, в качестве нижней границы использовать нельзя.
UNBOUNDED FOLLOWING - указывает, что окно заканчивается на последней строке в разделе. Может быть указано только для нижней границы.
CURRENT ROW - обозначает текущую строку или значение. Может быть использовано как для нижней границы, так и для верхней.
PRECEDING - значение в строке или физическая строка, которая предшествует текущей строке на
FOLLOWING - значение в строке или физическая строка, которая находится впереди текущей строки на

Следует помнить, что если окно задается с использованием ROWS , т.е. указываются строки, то и границы окна будут задаваться в строках, и наоборот, если используется , то границы окна будут учитываться по значениям в строках.

Если окно не указывается, то по-умолчанию оно имеет вид RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .

Теперь посмотрим на один из предыдущих запросов:

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

PARTITION BY не указан, значит результаты будут "сплошные" и не будут разбиваться по группам. Обрабатываться строки будут в порядке возрастания даты в колонке sal_date , а диапазон строк, для которых будет вычисляться функция, задается первой строкой во всем наборе данных и заканчивается текущей строкой.

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

Еще один важный момент: значения в колонке sal_date не являются уникальными. Это означает, что результат будет недетерминированным, т.е. может отличаться от запуска к запуску, т.к. порядок следования строк в выборке может измениться.

Чтобы избавиться от такого эффекта, можно добавить еще одну колонку в конструкцию order by, чтобы сделать порядок следования строк уникальным и не меняющимся. В данном случае мы можем дополнительно сортировать данные по id сотрудника:

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

Функции CUME_DIST , DENSE_RANK , NTILE , PERCENT_RANK и RANK возвращают одинаковый результат для всех строк
Функция ROW_NUMBER присвоит каждой строке уникальное значение. Порядок присваивания будет зависеть от порядка обработки строк БД, который мы не можем предугадать
Все остальные функции будут работать по-разному в зависимости от спецификации окна. Если окно задавалось при помощи RANGE , то функция вернет одинаковое значение для всех строк. Если использовалось ключевое слово ROWS , то результат нельзя будет предугадать - он опять же будет зависеть от порядка обработки строк базой данных, который может отличаться для одного и того же набора данных от запуска к запуску.

Размеры окна можно задавать в виде смещений:

Здесь в колонке avg_sal считается средняя заработная плата по трем строкам - двум предшествующим и текущей. Порядок следования, как мы помним, задается при помощи ORDER BY, поэтому две предшествующие строки - это строки, у которых значение в колонках sal_date будет меньше либо равным значению в текущей строке.

Значение функции округляется до двух знаков после запятой при помощи функции round . Аналитическая функция берется в скобки полностью, начиная от имени функции и заканчивая определением окна. К значениям, полученным при помощи аналитических функций можно применять другие функции или операторы - например, можно было бы добавить 100 к среднему значению:

Или даже получить разность между значениями двух аналитических функций:

В следующем примере смещение задается не в строках, а в диапазоне значений, которые содержит колонка sal_value:

Т.к. использовался RANGE , то сумма рассчитывается для всех строк, значение которых находится в диапазоне от 1000 до значения в текущей строке.

Еще раз, следует обратить внимание, что строки, которые находятся после текущей, также обрабатываются функцией, если значение колонки sal_value входит в заданный диапазон. Это можно видеть на изображении выше, в строках, где значение sal_value равно 1000 - для первой строки в сумму посчиталось и значение следующей.

Следующий пример считает сумму по четырем строкам - в окно входят 2 предшествующие строки, текущая строка и одна строка, следующая за текущей:

Т.к. окно задавалось с использованием ROWS , сумма считается именно по строкам, а не по их значениям. Для первой строки в сумму были взяты данные из нее самой и следующей, т.к. предыдущих строк у нее нет. Для второй строки была лишь одна предыдущая строка, а у последней не было следующей.

Ограничения на ORDER BY

ORDER BY в аналитических функциях может использоваться только с одной колонкой, за исплючением случаев, когда используется RANGE и окно задается одним из следующих способов:

Читайте также: