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

Изучение 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)

Скриншот 1

ROW_NUMBER

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

Синтаксис

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

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

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

Скриншот 2

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

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

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

Скриншот 3

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

   
   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()

Скриншот 4

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

   
   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()

Скриншот 5

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

   
   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()

Скриншот 6

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

   
   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.

Пример

Скриншот 7

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

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

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

Скриншот 8

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

   
   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

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментариев: 5
  1. Аватар
    Володя

    Велике дякую класні пояснення з прикладами!

  2. Аватар
    Анна

    Самая понятная и полная статья, которые мне попадались. Row_number() я освоила, он уже в голове, а вот остальные редко трогаю, поэтому каждый раз перечитываю эту статью, она у меня в закладках и отдельно даже сохранена на компе.
    Спасибо большое.
    Уважение от меня — Вам.

    1. Админ
      Админ (автор)

      Спасибо за отзыв!

  3. Аватар
    Артемий

    Классная статья. Всё понятно, наглядные примеры.
    Спасибо за труд.

    1. Админ
      Админ (автор)

      И Вам спасибо за отзыв!

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.