Sql rank: RANK() DENSE_RANK() | SQL | SQL-tutorial.ru

RANK ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию RANK с синтаксисом и примерами.

Описание

Oracle/PLSQL функция RANK возвращает ранг значения в группе значений. Это очень похоже на функцию DENSE_RANK. Однако функция RANK может возвращать не последовательное ранжирование, если тестируемые значения одинаковы. Тогда как, функция DENSE_RANK всегда будет приводить к последовательному ранжированию.

Функцию RANK можно использовать двумя способами — в качестве агрегатной функции или в качестве аналитической функции.

Синтаксис № 1 функция RANK — используется в качестве агрегатной функции

Как агрегатная функция, функция RANK возвращает ранг строки в группе строк.

Синтаксис для функции RANK при использовании в качестве агрегатной функции:

RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] )

Параметры или аргументы

expr1 — первое выражение, которое идентифицирует уникальную строку в группе.

expr2, … expr_n — необязательный. Дополнительные выражения, которые идентифицируют уникальную строку в группе.

Примечание

• Функция RANK возвращает numeric значение.
• В первом списке выражений должно быть столько же выражений, сколько в операторе ORDER BY.
• Списки выражений совпадают по позициям, поэтому типы данных должны быть совместимы между выражениями в первом списке, так и в списке оператора ORDER BY.

Применение

Функцию RANK можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Пример (как агрегирующая функция)

Рассмотрим пример функции RANK и изучим, как использовать функцию RANK в Oracle/PLSQL.

select RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;

Запрос SQL вернет ранг сотрудника с окладом в 1000$ и бонус в размере 500$ из таблицы employees.

Синтаксис № 2 функция RANK — используется в качестве аналитической функции

Как аналитическая функция, функция RANK возвращает ранг каждой строки запроса соответствующим для других строк.

Синтаксис функции RANK при использовании в качестве аналитической функции:

rank() OVER ( [ query_partition_clause] ORDER BY clause )

Функция RANK возвращает numeric значение.

Применение

функцию RANK можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Пример (как аналитическая функция)

select employee_name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = ‘Marketing’;

Запрос SQL вернет всех сотрудников, работающих в отделе маркетинга, а затем рассчитает ранг для каждой уникальной зарплаты в отделе маркетинга. Если у двух сотрудников была одинаковая зарплата, функция RANK вернет тот же ранг, для обоих сотрудников. Тем не менее, это вызовет разрыв в рядах (т.е.: непоследовательных рангов). Это в корне отличается от функции DENSE_RANK который генерирует последовательные ранги.

Функции ранжирования и нумерации в Transact-SQL — ROW_NUMBER, RANK, DENSE_RANK, NTILE | Info-Comp.ru

Изучение Transact-SQL продолжается и на очереди у нас функции ранжирования ROW_NUMBER, RANK, DENSE_RANK и NTILE, сейчас мы узнаем, что делают эти функции и зачем вообще они нужны, все как обычно будем рассматривать на примерах.

В языке Transact-SQL очень много различных функций, конструкций, например, PIVOT или INTERSECT, которые в принципе редко используются, их мы даже в нашем мини справочнике Transact-SQL не указывали, но знать, где и как их можно использовать нужно, так же, как и функции ранжирования или их еще называют функции нумерации. Поэтому сегодня давайте поговорим именно об этих функциях, и если говорить конкретно, то это функции: ROW_NUMBER, RANK, DENSE_RANK, NTILE.

И начнем мы, конечно же, с определения, что же вообще это за ранжирующие функции.

Ранжирующие функции в T-SQL

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

И для того чтобы лучше усвоить работу и применение этих функций, давайте рассмотрим все их по очереди, и параллельно будем сравнивать их друг с другом, т.е. таким образом, мы еще и узнаем в чем их отличие. Но для того чтобы начать рассматривать примеры, необходимо определится с исходными данными.

Примечание! Для детального изучения языка T-SQL, рекомендую почитать книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю основы программирования на языке T-SQL.

Исходные данные для примеров

Использовать мы будем MS SQL Server Express 2014, а запросы будем писать в Management Studio Express. В качестве тестовых данных будем использовать таблицу selling, которая будет содержать различные товары (телефоны, планшеты, ноутбуки, программы) с выдуманными ценами.

Наша тестовая таблица

   
   CREATE TABLE [dbo].[selling](
          [id] [int] IDENTITY(1,1) NOT NULL,
          [NameProduct] [varchar](50) NOT NULL,
          [price] [money] NOT NULL,
          [category] [varchar](50) NOT NULL
   ) ON [PRIMARY]
   GO

Заполним ее тестовыми данными, в итоге получим следующее (для выборки пишем простой запрос select)

Заметка! Функции TRIM, LTRIM и RTRIM в T-SQL – описание, отличия и примеры.

ROW_NUMBER

ROW_NUMBER – функция нумерации в Transact-SQL, которая возвращает просто номер строки.

Синтаксис

ROW_NUMBER () OVER ([PARTITION BY столбы группировки] ORDER BY столбец сортировки)

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

Пример без группировки с сортировкой по цене

Текст запроса

   
   SELECT NameProduct, price, category,
          ROW_NUMBER() over (order by price desc) as [ROW_NUMBER]
   FROM selling

Пример с группировкой по категории и с сортировкой по цене

Текст запроса

   
   SELECT NameProduct, price, category,
          ROW_NUMBER() over (partition by category order by price desc) as [ROW_NUMBER_PART]
   FROM selling

Как видите, здесь уже нумерация идет в каждой категории.

RANK

RANK – ранжирующая функция, которая возвращает ранг каждой строки. В данном случае, в отличие от row_number(), идет уже анализ значений и в случае нахождения одинаковых, функция возвращает одинаковый ранг с пропуском следующего. Как было уже сказано выше, здесь также можно использовать partition by для группировки и обязательно нужно указывать столбец сортировки в order by.

Пример без группировки с сортировкой по цене и отличие от row_number()

Текст запроса

   
   SELECT NameProduct, price, category,
          rank() over (order by price desc) [RANK],
          ROW_NUMBER() over (order by price desc) as [ROW_NUMBER]
   FROM selling

Пример с группировкой по категории и с сортировкой по цене и отличие от row_number()

Текст запроса

   
   SELECT NameProduct, price, category,
          rank() over (partition by category order by price desc) [RANK],
          ROW_NUMBER() over (partition by category order by price desc) as [ROW_NUMBER_PART]
   FROM selling

DENSE_RANK

DENSE_RANK — ранжирующая функция, которая возвращает ранг каждой строки, но в отличие от rank, в случае нахождения одинаковых значений, возвращает ранг без пропуска следующего.

Пример без группировки с сортировкой по цене и отличие от rank() и row_number()

Текст запроса

   
   SELECT NameProduct, price, category,
          rank() over (order by price desc) [RANK],
          DENSE_RANK () over (order by price desc) [DENSE_RANK],
          ROW_NUMBER() over (order by price desc) as [ROW_NUMBER]
   FROM selling

NTILE

NTILE – функция Transact-SQL, которая делит результирующий набор на группы по определенному столбцу. Количество групп указывается в качестве параметра. В случае если в группах получается не одинаковое количество строк, то в первой группе будет наибольшее количество, например, в нашем случае строк 10 и если мы поделим на три группы, то в первой будет 4 строки, а во второй и третей по 3.

Пример

Текст запроса

   
   SELECT NameProduct, price, category,
          NTILE(3)over (order by price desc) [NTILE]
   FROM selling

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

Текст запроса

   
   SELECT NameProduct, price, category,
          ROW_NUMBER() over (order by price desc) as [ROW_NUMBER],
          rank() over (order by price desc) [RANK],
          DENSE_RANK () over (order by price desc) [DENSE_RANK],
          NTILE(3)over (order by price desc) [NTILE]
   FROM selling

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

На этом я думаю по ранжирующим функциям достаточно, в следующих статьях мы продолжим изучение Transact-SQL, а на этом пока все. Удачи!

НравитсяНе нравится

MSSQL Функция RANK

Функция RANK MSSQL используется для ранжирования повторяющиеся значения таким образом, что аналогичные значения ранжируются то же самое. Другими словами, функция ранга возвращает ранг каждой строки в пределах раздела результирующего набора. Ранг подряд один плюс число рядов, которые приходят до строки в вопросе.

MSSQL ранговая функция СИНТАКСИС


RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )


Partition_by_clause разбивает строки, генерируемые в различных наборах. Если partition_by_clause не указано, то все строки должны рассматриваться как единая группа.

Приоритет рейтинга определяется order_by_clause. Order_by_clause в отличие от partition_by_clause имеет важное значение в функции Rank.

MSSQL RANK ФУНКЦИЯ ПРИМЕР

Пример таблицы базы данных «Сотрудник»

Пример использования MSSQL RANK Функция без использования partition_by_clause из таблицы «Сотрудник»:


SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (order by Gender DESC)AS Rank
FROM Employee;


MSSQL ранговая функция Результаты

Это даст следующие результаты ранжирования.

MSSQL RANK ФУНКЦИЯ ПРИМЕР 2

MSSQL RANK Функция с partition_by_clause от «Сотрудник» таблицы выше.


SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (partition by Location, order by Gender DESC)AS Rank
FROM Employee;


RANK Функции Результаты Пример 2

MSSQL RANK Функция помощи partition_by_clause обеспечит следующие результаты.

Добавить эту страницу в закладки

DENSE_RANK ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию DENSE_RANK с синтаксисом и примерами.

Описание

Oracle/PLSQL функция DENSE_RANK возвращает ранг строки в упорядоченной группе строк. Она очень похожа на функцию RANK. Однако функция RANK может вызвать непоследовательное ранжирование если тестируемые значения одинаковы. Поэтому, функция DENSE_RANK всегда будет приводить к последовательному ранжированию строк.

Синтаксис

Синтаксис Oracle/PLSQL функции DENSE_RANK:

DENSE_RANK( expression1, … expression_n ) WITHIN GROUP ( ORDER BY expression1, … expression_n )

Параметры или аргументы

expression1 .. expression_n может быть одно или несколько выражений, которые идентифицируют уникальную строку в группе..

Примечание

• Функция DENSE_RANK возвращает numeric значение.
• Количество expression в первом списке выражений, должно быть таким же, как и в операторе ORDER BY.
• Список выражений позиции и типы данных в первом списке должны быть совместимы c выражениями, как и в операторе ORDER BY.

Применение

Функцию DENSE_RANK можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Рассмотрим несколько примеров функции DENSE_RANK и изучим, как использовать функцию DENSE_RANK в Oracle/PLSQL.

Пример (как агрегирующая функция)

select DENSE_RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;

Запрос SQL вернет ранг строки сотрудника с окладом в 1000$ и бонус в размере 500$ из таблицы employees.

DENSE_RANK КАК АНАЛИТИЧЕСКАЯ ФУНКЦИЯ

Как аналитическая функция, функция DENSE_RANK возвращает ранг каждой строки запроса с соответствием для других строк.

Синтаксис функции DENSE_RANK при использовании в качестве аналитической функции:

DENSE_RANK() OVER ( [ query_partition_clause] ORDER BY clause )

функцию DENSE_RANK можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример (как аналитическая функция)

select employee_name, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = ‘Marketing’;

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

MSSQL DENSE RANK Функция

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

Примечание: Это же, как и функции RANK (). Только разница возвращается ранг без пробелов.

MSSQL DENSE_RANK ФУНКЦИЯ СИНТАКСИС


DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )


Partition_by_clause разбивает строки, генерируемые в различных наборах. Если partition_by_clause не указано, то все строки должны рассматриваться как единая группа. Приоритет рейтинга определяется order_by_clause. Order_by_clause, в отличие от partition_by_clause, имеет важное значение в функции DENSE_RANK ().

MSSQL DENSE_RANK ФУНКЦИЯ

Таблица базы данных «Сотрудник»

DENSE_RANK Функция Пример

Функция DENSE_RANK без использования partition_by_clause из таблицы «Сотрудник»:


SELECT EmployeeID, EmployeeName, Gender, Location,
DENSE_RANK() OVER (order by Gender DESC)AS DenseRank
FROM Employee;

DENSE_RANK Функциональные результаты

DENSE_RANK Функция без использования вывода partition_by_clause

DENSE_RANK Функция Пример

С partition_by_clause из таблицы «Сотрудник»


SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (partition by Location, order by Gender DESC)AS Rank
FROM Employee;

DENSE_RANK Функциональные результаты

С partition_by_clause производит следующий вывод.

Добавить эту страницу в закладки

Обзор функций SQL RANK

Мы выполняем вычисления с данными, используя различные агрегированные функции, такие как Max, Min и AVG. Используя эти функции, мы получаем одну выходную строку. SQL Sever предоставляет функции SQL RANK для определения ранга для отдельных полей в соответствии с категоризацией. Он возвращает агрегированное значение для каждой участвующей строки. Функции SQL RANK также известны как оконные функции.

• Примечание. Термин Windows в данном контексте не относится к операционной системе Microsoft Windows.Это функции SQL RANK.

У нас есть следующие ранговые функции.

• ROW_NUMBER ()
• РАНГ ()
• DENSE_RANK ()
• NTILE ()

В функциях SQL RANK мы используем предложение OVER () для определения набора строк в наборе результатов. Мы также можем использовать предложение SQL PARTITION BY для определения подмножества данных в разделе. Вы также можете использовать предложение Order by для сортировки результатов в порядке убывания или возрастания.

Прежде чем мы исследуем эти функции SQL RANK, давайте подготовим образцы данных. В этом примере данных представлены результаты экзаменов трех студентов по математике, естествознанию и английскому языку.

У нас есть следующие образцы данных в таблице ExamResult.

Давайте использовать каждую функцию ранжирования SQL в следующих примерах.

ROW_Number () Функция SQL RANK

Мы используем функцию ROW_Number () SQL RANK, чтобы получить уникальный порядковый номер для каждой строки в указанных данных. Он присваивает ранг один для первой строки, а затем увеличивает значение на единицу для каждой строки. Мы также получаем разные ранги для строки с похожими значениями.

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

По умолчанию он сортирует данные в порядке возрастания и начинает назначать ранги для каждой строки. На приведенном выше снимке экрана мы получаем ROW номер 1 для отметок 50.

Мы можем указать порядок убывания с помощью предложения Order By, и он соответственно изменит RANK.

RANK () Функция SQL RANK

Мы используем функцию RANK () SQL Rank, чтобы указать ранг для каждой строки в наборе результатов. У нас есть результаты студентов по трем предметам.Мы хотим ранжировать результаты студентов по их оценкам по предметам. Например, на следующем снимке экрана ученица Изабелла получила самые высокие оценки по английскому предмету и самые низкие по математике. По оценкам Изабелла занимает первое место по английскому языку и третье место по математике.

Выполните следующий запрос, чтобы получить этот набор результатов. В этом запросе вы можете отметить следующее:

• Мы используем предложение PARTITION BY Studentname для выполнения вычислений для каждой группы студентов.
• Каждое подмножество должно получить рейтинг в соответствии с их оценками в порядке убывания.
• В наборе результатов используется предложение Order By для сортировки результатов по имени студента и его рангу.

Давайте выполним следующий запрос функции SQL Rank и посмотрим на набор результатов.В этом запросе мы не указали предложение SQL PARTITION By для разделения данных на меньшее подмножество. Мы используем функцию SQL Rank с предложением over в предложении Marks (в порядке убывания), чтобы получить ранги для соответствующих строк.

На выходе мы видим, что каждый ученик получает рейтинг по своим оценкам, независимо от конкретного предмета.Например, самая высокая и самая низкая оценки в полном наборе результатов равны 90 и 50 соответственно. В наборе результатов самая высокая оценка получает RANK 1, а самая низкая оценка — RANK 9.

Если два ученика получают одинаковые оценки (в нашем примере это ряды 4 и 5), их ранги также совпадают.

DENSE_RANK () Функция SQL RANK

Мы используем функцию DENSE_RANK (), чтобы указать уникальный номер ранга в разделе в соответствии с указанным значением столбца.Она похожа на функцию Rank с небольшой разницей.

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

Давайте выполним следующий запрос с функцией DENSE_RANK ().

В выходных данных вы можете видеть, что у нас одинаковый рейтинг для Лили и Изабеллы, набравших 70 баллов.

Давайте использовать функцию DENSE_RANK в сочетании с предложением SQL PARTITION BY.

У нас нет двух учеников с одинаковыми оценками; поэтому в этом случае набор результатов аналогичен функции RANK.

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

Мы видим, что в студенческой группе Изабелла получила схожие оценки по математике и естественным наукам. Ранг в этом случае также одинаков для обоих предметов.

Давайте посмотрим на разницу между функцией RANK () и DENSE_RANK () SQL Rank с помощью следующего запроса.

• Запрос 1
  

  ВЫБРАТЬ Имя студента, Предмет, оценок, РАНГ () ВЫШЕ (РАЗДЕЛЕНИЕ ПО ИМЕНАМ УЧАЩИХСЯ ПОРЯДОК ПО оценкам) Рейтинг ОТ ExamResult ПОРЯДОК ПО ИМЕНИ УЧАСТНИКА, Ранг;

• Запрос 2
  

  ВЫБРАТЬ Имя студента, Предмет, баллов, DENSE_RANK () ВЫШЕ (РАЗДЕЛЕНИЕ ПО ИМЕНИ студента ПОРЯДОК ПО оценкам) Рейтинг ОТ ExamResult ЗАКАЗАТЬ ПО имени студента, Ранг;

В выходных данных вы можете увидеть пробел в выходных данных функции ранжирования внутри раздела.У нас нет пробелов в функции DENSE_RANK.

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

В функции Dense_Rank он поддерживает ранг и не дает никаких пробелов для значений.

Функция NTILE (N) SQL RANK

Мы используем функцию NTILE (N), чтобы распределить количество строк в указанном (N) количестве групп.Каждая группа строк получает свой ранг в соответствии с указанным условием. Нам нужно указать значение для желаемого количества групп.

В моем примере у нас есть девять записей в таблице ExamResult. NTILE (2) показывает, что нам требуется группа из двух записей в результате.

На выходе мы видим две группы.Группа 1 содержит пять строк, а группа 2 — четыре строки.

Аналогично, NTILE (3) делит количество строк на три группы, по три записи в каждой группе.

Мы можем использовать предложение SQL PARTITION BY, чтобы иметь более одного раздела.В следующем запросе каждый раздел по предметам разделен на две группы.

Практическое использование функций SQL RANK

Мы можем использовать функцию SQL RANK для извлечения определенных строк из данных.Предположим, мы хотим получить данные учащихся с рангов от 1 до 3. В следующем запросе мы используем общие табличные выражения (CTE) для получения данных с помощью функции ROW_NUMBER (), а затем отфильтровали результат CTE, чтобы удовлетворить нашему условию.

Мы можем использовать команду OFFSET FETCH, начиная с SQL Server 2012, чтобы получить определенное количество записей.

Краткое описание функций SQL RANK

Заключение

В этой статье мы изучили функции SQL RANK и разницу между ними. Разработчикам sql полезно знать эти функции, чтобы хорошо исследовать свои данные и управлять ими. Если у вас есть какие-либо комментарии или вопросы, не стесняйтесь оставлять их в комментариях ниже.

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

Работая старшим консультантом администратора баз данных для крупных заказчиков и получив сертификат MCSA SQL 2012, он любит делиться знаниями в различных блогах.
С ним можно связаться по адресу [email protected]

Посмотреть все сообщения Rajendra Gupta

Последние сообщения Rajendra Gupta (посмотреть все)

.

ROW_NUMBER (), RANK (), DENSE_RANK () и NTILE ()

SQL Server предоставляет нам ряд оконных функций, которые помогают нам выполнять вычисления по набору строк без необходимости повторять обращения к базе данных. В отличие от стандартных агрегатных функций, оконные функции не группируют строки в единую выходную строку, они возвращают единое агрегированное значение для каждой строки, сохраняя отдельные идентификаторы для этих строк. Термин Window здесь не относится к операционной системе Microsoft Windows, он описывает набор строк, которые функция будет обрабатывать.

Одним из наиболее полезных типов оконных функций являются оконные функции ранжирования, которые используются для ранжирования определенных значений полей и их категоризации в соответствии с рангом каждой строки, в результате чего получается единое агрегированное значение для каждой участвующей строки. SQL Server поддерживает четыре функции окна ранжирования; ROW_NUMBER (), RANK () , DENSE_RANK () и NTILE () . Все эти функции используются для вычисления ROWID для окна предоставленных строк по-своему.

Четыре функции окна ранжирования используют предложение OVER () , которое определяет указанный пользователем набор строк в наборе результатов запроса. Определив предложение OVER (), вы также можете включить предложение PARTITION BY , которое определяет набор строк, которые оконная функция будет обрабатывать, путем предоставления столбцов или разделенных запятыми столбцов для определения раздела. Кроме того, можно включить предложение ORDER BY , которое определяет критерии сортировки в разделах, по которым функция будет проходить строки во время обработки.

В этой статье мы обсудим, как на практике использовать четыре функции окна ранжирования: ROW_NUMBER (), RANK (), DENSE_RANK () и NTILE (), а также разницу между ними.

В качестве демонстрации мы создадим новую простую таблицу и вставим в нее несколько записей, используя сценарий T-SQL ниже:

 СОЗДАТЬ ТАБЛИЦУ StudentScore
(
  Student_ID INT ПЕРВИЧНЫЙ КЛЮЧ,
  Student_Name NVARCHAR (50),
  Student_Score INT
)

ИДТИ

ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (1, 'Ali', 978)
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (2, 'Zaid', 770)
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (3, 'Mohd', 1140)
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (4, 'Джек', 770)
ВСТАВИТЬ ЗНАЧЕНИЯ StudentScore (5, 'John', 1240)
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (6, «Майк», 1140)
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (7, 'Goerge', 885)
 

Вы можете проверить, успешно ли вставлены данные, используя следующий оператор SELECT:

 ВЫБРАТЬ * ИЗ StudentScore ЗАКАЗАТЬ ПО Student_Score 

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

ROW_NUMBER ()

Функция окна ранжирования ROW_NUMBER () возвращает уникальный порядковый номер для каждой строки в пределах раздела указанного окна, начиная с 1 для первой строки в каждом разделе, без повторения или пропуска чисел в результате ранжирования каждого раздела.Если в наборе строк есть повторяющиеся значения, номера идентификаторов ранжирования будут присвоены произвольно. Если указано предложение PARTITION BY, номер строки ранжирования будет сброшен для каждого раздела. В ранее созданной таблице запрос ниже показывает, как использовать оконную функцию ранжирования ROW_NUMBER для ранжирования строк таблицы StudentScore в соответствии с оценкой каждого студента:

 SELECT *, ROW_NUMBER () OVER (ORDER BY Student_Score) как RowNumberRank
ОТ StudentScore
 

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

Если мы изменим предыдущий запрос, включив предложение PARTITION BY, чтобы иметь более одного раздела, как показано в запросе T-SQL ниже:

 ВЫБРАТЬ *, ROW_NUMBER () ВЫШЕ (РАЗДЕЛЕНИЕ ПО Student_Score ORDER BY Student_Score) КАК RowNumberRank
ОТ StudentScore
 

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

Например, учащиеся с результатом 770 будут ранжироваться в пределах этого результата путем присвоения ему порядкового номера. Однако, когда он переходит к ученику со счетом 885, начальный номер ранга будет сброшен и снова начнется с 1, как показано ниже:

РАНГ ()

Функция окна ранжирования RANK () возвращает уникальный номер ранга для каждой отдельной строки в разделе в соответствии с заданным значением столбца, начиная с 1 для первой строки в каждом разделе, с одинаковым рангом для повторяющихся значений и оставляя промежутки между чины; этот пробел появляется в последовательности после повторяющихся значений.Другими словами, функция окна ранжирования RANK () ведет себя так же, как функция ROW_NUMBER (), за исключением строк с равными значениями, где она будет ранжироваться с тем же идентификатором ранга и генерировать пробел после него. Если мы изменим предыдущий запрос ранжирования, чтобы использовать функцию ранжирования RANK ():

 SELECT *, RANK () OVER (ORDER BY Student_Score) как RankRank
ОТ StudentScore
 

Из результата вы увидите, что оконная функция RANK будет ранжировать строки таблицы в соответствии со значениями столбца Student_Score для каждой строки со значением ранжирования, отражающим его Student_Score, начиная с числа 1, и ранжирует строки с одинаковым Student_Score с такое же ранговое значение.Вы также можете видеть, что две строки со значением Student_Score, равным 770, ранжируются с одним и тем же значением, оставляя зазор, который представляет собой пропущенное число 2, после строки со вторым ранжированием. То же самое происходит со строками, в которых Student_Score равно 1140, которые ранжируются с тем же значением, оставляя пробел, который представляет собой отсутствующее число 6, после второй строки, как показано ниже:

Изменение предыдущего запроса путем включения предложения PARTITION BY, чтобы иметь более одного раздела, как показано в запросе T-SQL ниже:

 SELECT *, RANK () OVER (РАЗДЕЛЕНИЕ ПО Student_Score ORDER BY Student_Score) КАК RowNumberRank
ОТ StudentScore
 

Результат ранжирования не будет иметь значения, поскольку ранжирование будет производиться в соответствии со значениями Student_Score для каждого раздела, а данные будут разделены в соответствии со значениями Student_Score.И из-за того, что в каждом разделе будут строки с одинаковыми значениями Student_Score, строки с одинаковыми значениями Student_Score в одном разделе будут ранжироваться со значением, равным 1. Таким образом, при переходе ко второму разделу ранг будет быть сброшенным, начиная снова с числа 1, имея все значения ранжирования, равные 1, как показано ниже:

DENSE_RANK ()

Функция окна ранжирования DENSE_RANK () аналогична функции RANK (), генерируя уникальный номер ранга для каждой отдельной строки внутри раздела в соответствии с заданным значением столбца, начиная с 1 для первой строки в каждом разделе, ранжируя строки с равными значениями с одинаковым номером ранга, за исключением того, что он не пропускает ни одного ранга, не оставляя промежутков между рядами.

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

Снова измените предыдущий запрос, включив предложение PARTITION BY, чтобы иметь более одного раздела, как показано в запросе T-SQL ниже:

 SELECT *, DENSE_RANK () OVER (РАЗДЕЛЕНИЕ ПО Student_Score ORDER BY Student_Score) КАК RowNumberRank
ОТ StudentScore
 

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

NTILE (N)

Функция окна ранжирования NTILE (N) используется для распределения строк в наборе строк в указанное количество групп, предоставляя каждой строке в наборе строк уникальный номер группы, начиная с номера 1, который показывает группу в этой строке. принадлежит, где N — положительное число, которое определяет количество групп, в которые необходимо распределить набор строк.

Другими словами, если вам нужно разделить определенные строки данных таблицы на 3 группы на основе определенных значений столбцов, функция окна ранжирования NTILE (3) поможет вам легко добиться этого.

Количество строк в каждой группе можно рассчитать, разделив количество строк на необходимое количество групп. Если мы изменим предыдущий запрос ранжирования, чтобы использовать оконную функцию ранжирования NTILE (4) для ранжирования семи строк таблицы в четыре группы, как приведенный ниже запрос T-SQL:

 SELECT *, NTILE (4) OVER (ORDER BY Student_Score) КАК NTILERank
ОТ StudentScore
 

Количество строк должно быть (7/4 = 1.75) рядов в каждую группу. Используя функцию NTILE (), SQL Server Engine назначит 2 строки первым трем группам и одну строку последней группе, чтобы все строки были включены в группы, как показано в результирующем наборе ниже:

Изменение предыдущего запроса путем включения предложения PARTITION BY, чтобы иметь более одного раздела, как показано в запросе T-SQL ниже:

 SELECT *, NTILE (4) OVER (РАЗДЕЛЕНИЕ ПО Student_Score ORDER BY Student_Score) КАК RowNumberRank
ОТ StudentScore
 

Строки будут распределены по четырем группам в каждом разделе.Например, первые две строки со значением Student_Score, равным 770, будут находиться в одном разделе и будут распределены по группам, каждая из которых ранжируется уникальным номером, как показано в приведенном ниже наборе результатов:

Собираем все вместе

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

 ОБРЕЗАННАЯ ТАБЛИЦА StudentScore
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ StudentScore ДОБАВИТЬ СИМВОЛ КЛАССА (1)
ИДТИ
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (1, 'Ali', 978, 'A')
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (2, 'Zaid', 770, 'B')
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (3, 'Mohd', 1140, 'A')
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (4, 'Jack', 879, 'B')
ВСТАВИТЬ ЗНАЧЕНИЯ StudentScore (5, 'John', 1240, 'C')
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (6, «Майк», 1100, «B»)
ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (7, 'Goerge', 885, 'C')
 

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

 ВЫБРАТЬ *, ROW_NUMBER () ВЫШЕ (РАЗДЕЛЕНИЕ ПО КЛАССАМ ПОСРЕДСТВОМ Student_Score) как RowNumberRank,
          РАНГ () ВЫШЕ (РАЗДЕЛЕНИЕ ПО КЛАССАМ ПО УКАЗАНИЮ ПО Student_Score) КАК Рейтинг
DENSE_RANK () OVER (РАЗДЕЛЕНИЕ ПО КЛАССАМ ПОСРЕДСТВОМ Student_Score) как DenseRankRank
NTILE (7) OVER (РАЗДЕЛЕНИЕ ПО КЛАССАМ ПО УЧЕТУ Student_Score) AS NTILERank

ОТ StudentScore
ИДТИ
 

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

Если в класс A включен другой учащийся с оценкой, которая уже есть у другого учащегося в том же классе, используя оператор INSERT ниже:

 ВСТАВИТЬ В ЗНАЧЕНИЯ StudentScore (8, 'Faisal', 978, 'A') 

Ничего не изменится для оконных функций ранжирования ROW_NUMBER () и NTILE ().Функции RANK и DENSE_RANK () присваивают одинаковый ранг студентам с одинаковым баллом, с разрывом в рейтингах после повторяющихся рангов при использовании функции RANK и без разрыва в рядах после повторяющихся рангов при использовании DENSE_RANK ( ), как показано в результате ниже:

Практический сценарий

Функции окна ранжирования широко используются разработчиками SQL Server. Один из распространенных сценариев использования функций ранжирования, когда вы хотите получить определенные строки и пропустить другие, с помощью оконной функции ранжирования ROW_NUMBER (,) в CTE, как в приведенном ниже сценарии T-SQL, который возвращает студентов с рангами между 2 и 5 и пропустить остальные:

 С ClassRanks AS
(
  ВЫБРАТЬ *, ROW_NUMBER () ВЫШЕ (ЗАКАЗАТЬ ПО Student_Score) КАК RowNumberRank
ОТ StudentScore
)

ВЫБЕРИТЕ Student_Name, Student_Score
ОТ ClassRanks
ГДЕ RowNumberRank> = 2 и RowNumberRank <= 5
ЗАКАЗАТЬ ПО RowNumberRank
 

Результат покажет, что будут возвращены только студенты с разрядами от 2 до 5:

Начиная с SQL Server 2012, была представлена ​​новая полезная команда OFFSET FETCH , которую можно использовать для выполнения той же предыдущей задачи, выбирая определенные записи и пропуская другие, используя сценарий T-SQL ниже:

 с классом рангов AS
(
  ВЫБРАТЬ *, ROW_NUMBER () ВЫШЕ (ЗАКАЗАТЬ ПО Student_Score) КАК RowNumberRank
ОТ StudentScore
)

ВЫБЕРИТЕ Student_Name, Student_Score
ОТ ClassRanks
СОРТИРОВАТЬ ПО
      RowNumberRank OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY NEXT 4 ROWS;
 

Получение того же предыдущего результата, как показано ниже:

Заключение

SQL Server предоставляет нам четыре функции окна ранжирования, которые помогают нам ранжировать предоставленный набор строк в соответствии с определенными значениями столбца.Это функции: ROW_NUMBER (), RANK (), DENSE_RANK () и NTILE (). Все эти функции ранжирования выполняют задачу ранжирования по-своему, возвращая тот же результат, если в строках нет повторяющихся значений. Если в наборе строк есть повторяющееся значение, функция RANK назначит один и тот же идентификатор ранжирования для всех строк с одинаковым значением, оставляя промежутки между рангами после дубликатов. Функция DENSE_RANK также назначит один и тот же идентификатор ранжирования для всех строк с одинаковым значением, но не оставит промежутков между рангами после дубликатов.В этой статье мы рассмотрим различные сценарии, чтобы охватить все возможные случаи, которые помогут вам на практике понять функции окна ранжирования.

Артикул:

Ахмад Ясин - руководитель администрирования баз данных SQL Server в компании Aramex International Company со степенью бакалавра в области компьютерной инженерии, а также опытом разработки .NET.
Он является сертифицированным специалистом Microsoft с большим опытом разработки, администрирования, настройки производительности, мониторинга и обеспечения высокой доступности и аварийного восстановления серверов SQL.

Последние сообщения Ахмада Ясина (посмотреть все).Сервер

sql: рейтинг по сумме баллов и порядок по рейтингу

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

Загрузка…

2. Авторизоваться
   зарегистрироваться

3. текущее сообщество
   

.Функции

RANK, DENSE_RANK и ROW_NUMBER в SQL Server

Введение

В этой статье вы узнаете о функциях RANK, DENSE_RANK и ROW_NUMBER в SQL Server.

Функции RANK, DENSE_RANK и ROW_NUMBER в SQL Server

• Функции RANK, DENSE_RANK и ROW_NUMBER используются для получения возрастающего целочисленного значения в зависимости от порядка строк путем наложения предложения ORDER BY в инструкции SELECT.
• Когда мы используем функции RANK, DENSE_RANK или ROW_NUMBER, предложение ORDER BY является обязательным, а предложение PARTITION BY является необязательным.
• Когда мы используем предложение PARTITION BY, выбранные данные будут разделены, а целочисленное значение сбрасывается до 1 при изменении раздела.

Давайте посмотрим на пример.

В этом примере я создал таблицу под названием «Сотрудники», в которую я добавил три столбца EMPID, Name и Salary соответственно. Для создания таблицы и столбца используется сценарий, приведенный ниже.

Создание таблицы

1. CREATE TABLE [dbo]. [Employee] (
2. [EMPID] [nvarchar] (30) NOT NULL,
3. [Name] [nvarchar] (150) NULL,
4. [Salary] [money] NULL
5. ) )

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

1. INSERT [dbo].[Сотрудник] ([EMPID], [Имя], [Зарплата]) ЗНАЧЕНИЯ ('EMP101', 'Vishal', 15000)
2. ВСТАВИТЬ [dbo]. [Сотрудник] ([EMPID], [Имя], [Заработная плата] ) VALUES ('EMP102', 'Sam', 20000)
3. INSERT [dbo]. [Employee] ([EMPID], [Name], [Salary]) VALUES ('EMP105', 'Ravi', 10000)
4. INSERT [dbo]. [Employee] ([EMPID], [Name], [Salary]) VALUES ('EMP106', 'Mahesh', 18000)

Таблица создана и данные вставлены. Теперь посмотрим, как ведут себя функции RANK, DENSE_RANK и ROW_NUMBER.

Для этого мы можем написать оператор Select, как показано ниже.

1. выберите EMPID, имя, зарплату,
2. RANK () сверху (упорядочить по зарплате по убыванию) как _Rank,
3. DENSE_RANK () выше (упорядочить по зарплате по убыванию) как DenseRank,
4. ROW_NUMBER () сверху (упорядочить по зарплате desc) как RowNumber от сотрудника

. Ниже вы можете увидеть, что функции RANK, DENSE_RANK и ROW_NUMBER дают одинаковый результат и возвращают целочисленные значения на основе максимальной заработной платы.

Я предполагаю, что у вас есть то, что делают функции RANK, DENSE_RANK и ROW_NUMBER.

RANK vs DENSE_RANK vs ROW_NUMBER функции

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

1. INSERT [dbo]. [Employee] ([EMPID], [Name], [Salary]) VALUES ('EMP108', 'Rahul', 20000)
2. INSERT [dbo].[Сотрудник] ([EMPID], [Имя], [Зарплата]) ЗНАЧЕНИЯ ('EMP109', 'menaka', 15000)
3. ВСТАВИТЬ [dbo]. [Сотрудник] ([EMPID], [Имя], [Заработная плата] ) VALUES ('EMP111', 'akshay', 20000)

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

Теперь мы можем увидеть разницу между функциями RANK, DENSE_RANK и ROW_NUMBER, выполнив тот же оператор SELECT, который мы выполняли ранее.

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

Функция RANK, DENSE_RANK ищет повторяющиеся значения. Целочисленное значение увеличивается на единицу, но если в таблице присутствует одно и то же значение (Salary), то одно и то же целочисленное значение присваивается всем строкам с одинаковым значением (Salary), отмеченным небесно-голубым цветом.

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

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

Некоторые из использования этих функций в реальном времени

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

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

1. с empCTE как
2. (
3. select *, ROW_NUMBER () over (разделение по EMPID по EMPID) как rowno от сотрудника
4. )
5. удалить из empCTE, где rowno> 1

В этом коде я написали заявление Select, чтобы получить звание, и я выступил в качестве CTE.После этого я удаляю записи из CTE, где номер строки больше единицы, поэтому остается одна запись.

Как видно на рисунке выше, повторяющиеся строки удаляются.

• Используя функцию RANK или DENSE_RANK, мы можем очень легко найти N-ую самую высокую зарплату, однако, какую функцию использовать, зависит от того, что нам нужно.

Для этого давайте обновим зарплату Рави так же, как и Сэма. Сценарий выглядит следующим образом:

1. update Employee set Salary = 20000, где EMPID = 'EMP105'

Теперь у нас есть две первые записи о зарплате по высоте.Давайте напишем оператор Select, чтобы найти вторую по величине зарплату, используя функцию RANK или DENSE_RANK, как показано ниже.

Как вы можете видеть выше, функция DENSE_RANK дает точный результат, а функция RANK не возвращает ни одной строки. Итак, мы можем использовать функцию DENSE_RANK, даже если есть такая же зарплата.

.