Перевели для вас интересную статью об использовании ROW_NUMBER OVER в SQL.
Ссылка на оригинал: https://learnsql.com/blog/row-number-over-in-sql/
Иногда вам нужно знать положение строк в результирующем наборе. Узнайте, как с помощью ROW_NUMBER и OVER в SQL это можно сделать!
Вам когда-нибудь нужно было добавлять порядковый номер к записям, возвращаемым SQL-запросом? Или, возможно, вам нужно создать ‘top n’ отчет на основе определенного ранжирования. В любом из этих случаев вам необходимо вычислить позицию строки в списке. Для этого вам понадобится функция ROW_NUMBER() . Функция присваивает последовательное целое число любой строке в результирующей выборке.
В этой статье мы рассмотрим, как использовать функцию ROW_NUMBER().
Что такое функция ROW_NUMBER()?
ROW_NUMBER это оконная функция в SQL. Она используется для добавления последовательных номеров к строкам результирующей выборки. Как и любая другая оконная функция она используется с оператором OVER() . Вот синтаксис:
Оператор OVER () может быть дополнена двумя другими операторами: PARTITION BY и ORDER BY. Мы покажем примеры с использованием нескольких вариантов OVER.
Использование ROW_NUMBER() с помощью OVER(): вводный пример
Давайте рассмотрим простой SQL-запрос с использованием оконной функции ROW_NUMBER. Для иллюстрации ранжирования нет ничего лучше спорта, поэтому давайте предположим, что мы работаем в компании, которая организует спортивные соревнования в разных странах.
У нас есть таблица athlete состоящая из столбцов firstname, lastname, sport и country. Запрос для генерации отчета, включающего порядковый номер для каждого спортсмена, выглядит следующим образом:
Выражением ROW_NUMBER() OVER () присваивает последовательное целое значение, начинающееся с 1, каждой строке в результирующем наборе запроса. Порядок чисел, присвоенных строкам в результате, не является детерминированным, если вы используете простой оператор OVER (). (Обратите внимание, что здесь нет дополнительных операторов типа ORDER BY или PARTITION BY в OVER()). Первой записью может быть любая запись таблицы; для этой записи ROW_NUMBER вернет 1. Затем то же самое для второй записи, которая будет иметь номер 2, и так далее. Ниже приведен частичный результат запроса:
Создание рейтингов с помощью ROW_NUMBER() и ORDER BY
Теперь предположим, что компании необходимо создать ярлык с номером участника для всех спортсменов, участвующих в марафоне. Спортсмены должны быть упорядочены по фамилии, и компания хочет присвоить каждому спортсмену порядковый номер; эти номера будут использованы в качестве наклеек на футболки спортсменов во время марафона. Наклейки должны начинаться со 1001. Запрос:
Этот запрос аналогичен предыдущему примеру. Одним из отличий является оператор WHERE, которое возвращает только спортсменов, участвующих в марафоне. Другим отличием (которое является основным) является оператор OVER(ORDER BY lastname). Он указывает ROW_NUMBER(), что порядковый номер должен быть присвоен в порядке следования фамилии — например, 1 - первой фамилии, 2 - второй и так далее.
В предыдущем наборе результатов участники были упорядочены по lastname. Однако, если у двух участников одинаковая фамилия (например, Смит), то порядок этих двух строк не является детерминированным; строки могут располагаться в любом порядке. Если мы хотим упорядочить данные как по lastname , так и по firstname, мы должны использовать выражение:
Использование ORDER BY дважды в одном запросе
В приведенном выше запросе мы используем оператор ORDER BY в функции ROW_NUMBER(). Однако результат запроса не соответствует никакому порядку — то есть строки упорядочены случайным образом. При желании мы могли бы добавить второй оператор ORDER BY в конце запроса, чтобы определить порядок отображения записей результатов.
Давайте изменим предыдущий запрос, добавив одно изменение: мы добавим ORDER BY country:
Строки в приведенном ниже результате — это те же строки, что и в предыдущем запросе, но они показаны в другом порядке. Теперь они упорядочены относительно страны спортсмена. Однако, если два или более спортсмена из одной страны, они отображаются в любом порядке. Мы можем видеть это ниже на примере двух спортсменов из Канады:
В этом запросе мы дважды использовали оператор ORDER BY. Первый раз было использовано в функции ROW_NUMBER для присвоения порядкового номера для фамилии. Второй раз использовался для определения порядка отображения результирующих строк, который сформирован по названию страны.
Использование ROW_NUMBER() с PARTITION BY и ORDER BY
В следующем примере запроса мы будем использовать оператор ROW_NUMBER() в сочетании с PARTITION BY и ORDER BY. Мы покажем запрос для присвоения спортсменам номеров комнат. Предположим, компания хочет разместить спортсменов из одной страны в смежных гостиничных номерах. Идея состоит в том, чтобы создать табличку со страной и порядковым номером для каждого спортсмена и повесить эту табличку на дверь каждого гостиничного номера. Например, если страной является Канада и в ней 3 спортсмена, нам нужны таблички для комнат ‘Canada_1’, ‘Canada_2’ и ‘Canada_3’.
Запрос для генерации табличек комнат с именем спортсмена, присвоенным этой комнате, выглядит следующим образом:
Новый элемент, введенный в запрос, является OVER(PARTITION BY country). Он группирует строки из того же country и генерирует разные последовательные ряды чисел (начиная с 1) для каждой страны.
В следующем результате запроса вы можете видеть, что строки, сгруппированные с помощью PARTITION BY , имеют одинаковый цвет. Одна группа строк предназначена для Канады (светло-голубая), другая - для Франции (фиолетовая) и так далее.
Внутри каждой группы строк оператор ORDER BY lastname используется для присвоения спортсменам порядковых номеров по фамилии. Для ‘Ireland’ у нас есть три строки; первая предназначена для ‘Barry’, вторая - для ‘Fox’ и так далее.
Другие оконные функции ранжирования: RANK и DENSE_RANK
Помимо ROW_NUMBER, SQL предоставляет две другие оконные функции для вычисления ранжирования: RANK и DENSE_RANK. Функция RANK работает иначе, чем ROW_NUMBER, когда между строками есть связи. Когда есть связь, RANK присваивает одинаковое значение обеим строкам и пропускает следующее значение (например, 1, 2, 2, 2, 5 – ранги 3 и 4 опущены). Функция DENSE_RANK не пропускает следующее значение (я).
Давайте рассмотрим простой пример, чтобы увидеть различия между этими тремя функциями:
В результате:
Использование ROW_NUMBER() с оператором WHERE
В SQL вы не можете использовать оконные функции с оператором WHERE . Однако в некоторых сценариях это может потребоваться. Например, в отчете Top 10 было бы очень полезно иметь возможность использовать условие типа WHERE ROW_NUMBER OVER() <= 10.
Хотя вы не можете использовать ROW_NUMBER() непосредственно в WHERE, вы можете сделать это косвенно через общее табличное выражение или CTE. Например, предположим, что мы хотим получить первые 3 позиции в марафоне и забеге на 100 метров. Сначала мы пишем CTE, который начинается с WITH:
В предыдущем запросе мы создали CTE с именем positions. В нем есть столбец с именем position, который заполняется результатом выполнения функции ROW_NUMBER() .
В основном запросе (т.е. во втором операторе SELECT ) мы можем использовать столбец position с оператором WHERE для фильтрации спортсменов, которые заканчивают соревнование на первых трех позициях.
Примечание: Если у нас есть связи между двумя конкурентами, функция RANK() может быть более подходящей для использования, чем функция ROW_NUMBER() в этом отчете.
Результаты запроса приведены ниже:
Псевдоколонка Oracle ROWNUM
Oracle SQL позволяет нам помещать псевдоколоночку с именем ROWNUM в любой запрос. Псевдоколонка ведет себя как столбец таблицы, но фактически не хранится в таблице. Вы можете выбирать из псевдоколонки, как если бы это был столбец в таблице.
Псевдоколонка ROWNUM возвращает позицию строки в результирующем наборе. Она начинается с 1 для первой строки, и каждая из следующих записей увеличивается на 1.
Однако Oracle ROWNUM не обладает возможностями оконной функции ROW_NUMBER . Например, вы не можете использовать оператор PARTITION BY для создания нескольких разных последовательностей, как мы делали в запросе гостиничного номера. Другое ограничение заключается в том, что вы не можете использовать оператор ORDER BY для указания порядка последовательности, отличного от порядка результирующего набора. Причина этих ограничений проста: ROWNUM это не оконная функция; это всего лишь простая псевдоколонка.
Готовы попрактиковаться в ROW_NUMBER() и OVER() в SQL?
Мы рассмотрели несколько способов добавления числовой последовательности к результату запроса с помощью функции ROW_NUMBER . И мы показали различные способы использования оператора OVER() . Мы также представили еще две функции ранжирования SQL: RANK и DENSE_RANK.
