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

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

ROW_NUMBER, RANK, DENSE_RANK, NTILE

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

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

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

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

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

Заметка! Для комплексного изучения языка SQL рекомендую почитать мою книгу «SQL код». Данный книга рассчитана на изучение языка SQL как стандарта, т.е. на изучение тех возможностей 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

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

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

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

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

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

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

  2. Анна

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

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

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

  3. Артемий

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

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

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

  4. Z_K

    Автор, ты классный. Твоя статья — первая, в которой я столкнулся с таким понятным обьяснением и столь простыми сравнительными примерами функций. Аж в голове уложилось. Спасибо тебе огромное! :oops:

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

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

Добавить комментарий для Анна Отменить ответ

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