Функции ранжирования в MS SQL

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

Итак, собственно, функции ранжирования. Представим, что у нас есть вот такая табличка:

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

SELECT ROW_NUMBER() OVER (ORDER BY PositionType DESC) as RowNumber
      ,[maker]
      ,[positionName]
      ,[positionType]
      ,[price]
  FROM [test].[dbo].[rowNumbering]

В результате выполнения этого запроса мы получим вот такой результат:

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

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT TOP 1 1 FROM [test].[dbo].[rowNumbering])) as RowNumber
      ,[maker]
      ,[positionName]
      ,[positionType]
      ,[price]
  FROM [test].[dbo].[rowNumbering]

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

Следующая функция - RANK() более интересна. Она позволяет ранжировать результаты выдачи на основе какого-то столбца. Посмотрите на результат выполнения вот такого запроса:

SELECT RANK() OVER (ORDER BY PositionType DESC) as rnk
      ,[maker]
      ,[positionName]
      ,[positionType]
      ,[price]
  FROM [test].[dbo].[rowNumbering]

Как видите, все сервера у нас получили ранг равный 1, а модем - ранг равный 4. Почему не 2? Потому что в функции RANK() ранг каждого ряда вычисляется как номер ряда+1. Ряды с одинаковым значением столбца получают одинаковый ранг, а следующий отличающийся - свой порядковый.

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

SELECT DENSE_RANK() OVER (ORDER BY positionType DESC) as rnk
      ,[maker]
      ,[positionName]
      ,[positionType]
      ,[price]
FROM [test].[dbo].[rowNumbering]

А результат выполнения выглядит уже значительно красивее:

Ну а самое интересное во всех функциях ранжирования - это возможность разделять результирующий набор данных на основании значения какой либо колонки. Для этого используется выражение PARTITION BY. Давайте попробуем вывести пронумерованный список продуктов каждой компании. Используем для этого функцию ROW_NUMBER() с выражением PARTITION BY maker.

SELECT ROW_NUMBER() OVER (PARTITION BY maker ORDER BY PositionType DESC) as RowNumber
      ,[maker]
      ,[positionName]
      ,[positionType]
      ,[price]
  FROM [test].[dbo].[rowNumbering]

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

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

                         SELECT maker, positionName FROM (

SELECT DENSE_RANK() OVER (PARTITION BY maker ORDER BY price) as rnk
 ,[maker]
 ,[positionName]
 ,[positionType]
 ,[price]
 FROM
(SELECT * FROM [test].[dbo].[rowNumbering] where positionType='server') as t1

                                                        ) as t2
                                                        where rnk=1

А вот результат его выполнения: