SQL Server представил четыре новые функции ранжирования, которые упрощают процесс ранжирования записей в наборе результатов или внутри групп записей. Эти функции не только помогают упростить запрос, но и улучшают его производительность. В этой статье мы рассмотрим две из этих функций ранжирования: DENSE_RANK и NTILE.
DENSE_RANK()
Функция DENSE_RANK используется, когда вам нужны последовательные номера ранжирования без пропусков между ними. Она присваивает последовательный ранг каждой строке набора результатов или внутри разделов (групп записей). В отличие от функции RANK, функция DENSE_RANK дает последовательное ранжирование. Синтаксис функции DENSE_RANK выглядит следующим образом:
DENSE_RANK() OVER ( [PARTITION BY partition_value_expression, ... [n]] ORDER BY order_value_expression, ... [n] )
Вот пример использования функции DENSE_RANK:
SELECT DENSE_RANK() OVER (ORDER BY Title) AS [RecordRank],
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM HumanResources.Employee
Этот скрипт присваивает последовательный ранг каждой строке набора результатов, упорядоченной по столбцу Title. Если две или более записей имеют одно и то же значение для столбца Title, они получат одинаковый ранг.
Вы также можете использовать функцию DENSE_RANK для присвоения последовательного ранга внутри разделов. Вот пример:
SELECT DENSE_RANK() OVER ( PARTITION BY Gender ORDER BY Title ) AS [RecordRank], LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender FROM HumanResources.Employee
Этот скрипт присваивает последовательный ранг каждой строке раздела (группы записей), который разделен по значению столбца Gender и упорядочен по столбцу Title. Если две или более записей имеют одно и то же значение для столбца Title внутри раздела, они получат одинаковый ранг.
NTILE()
Функция NTILE используется для разделения набора результатов или разделов (групп записей) на указанное количество подразделов (подгрупп записей). Для каждой строки в подразделе она возвращает номер подраздела, в который попадает строка. Если функция NTILE не может равномерно разделить строки на указанное количество подгрупп, то более крупные подгруппы будут идти перед более маленькими группами.
Синтаксис функции NTILE выглядит следующим образом:
NTILE() OVER ( [PARTITION BY partition_value_expression, ... [n]] ORDER BY order_value_expression, ... [n] )
Вот пример использования функции NTILE:
SELECT NTILE(50) OVER (ORDER BY Title) AS [SubPartition#],
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM HumanResources.Employee
Этот скрипт разделяет набор результатов на 50 подразделов. Первые 40 подразделов будут содержать по 6 записей каждый, в то время как последние 10 подразделов будут содержать по 5 записей каждый.
Вы также можете использовать функцию NTILE для разделения каждого раздела на подразделы. Вот пример:
SELECT NTILE(50) OVER ( PARTITION BY Gender ORDER BY Title ) AS [SubPartition#], LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender FROM HumanResources.Employee
Этот скрипт разделяет каждый раздел, который был разделен по значению столбца Gender, на 50 подразделов. Первый раздел для “F” содержит 84 записи, поэтому первые 34 подраздела будут содержать по 2 записи каждый, в то время как последние 16 подразделов будут содержать по 1 записи каждый.
Заключение
В этой статье мы рассмотрели две функции ранжирования в SQL Server: DENSE_RANK и NTILE. Функция DENSE_RANK используется для присвоения последовательных номеров ранжирования без пропусков, а функция NTILE используется для разделения набора результатов или разделов на подразделы. Понимание этих функций ранжирования может значительно упростить ваши запросы и улучшить производительность.