DENSE_RANK (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric
Эта функция возвращает ранг каждой строки в секции результирующего набора без промежутков в значениях ранжирования. Ранг определенной строки равен количеству различных значений рангов, предшествующих строке, увеличенному на единицу.
Соглашения о синтаксисе Transact-SQL
Синтаксис
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Аргументы
<partition_by_clause>
Делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым затем применяется функция DENSE_RANK. Синтаксис PARTITION BY см. в статье Предложение OVER (Transact-SQL).
<order_by_clause>
Определяет порядок, в котором функция DENSE_RANK применяется к строкам в секции.
Типы возвращаемых данных
bigint
Замечания
Если две строки или несколько в одной секции имеют одинаковые значения ранга, каждой такой строке присваивается один и тот же ранг. Например, если двум лучшим менеджерам по продажам соответствует одинаковое значение SalesYTD, им обоим присваивается значение ранга 1. Менеджеру по продажам со следующим по величине значением SalesYTD присваивается значение ранга 2. Это значение превышает количество отдельных строк, предшествующих данной строке, на единицу. Таким образом, между номерами, возвращаемыми функцией DENSE_RANK, нет промежутков, и они всегда имеют последовательные значения ранга.
Порядок сортировки, используемый для всего запроса, определяет порядок строк в результирующем наборе. Из этого следует, что строка с рангом 1 не всегда является первой строкой в секции.
Функция DENSE_RANK не детерминирована. Дополнительные сведения см. в статье Детерминированные и недетерминированные функции.
Примеры
А. Ранжирование строк внутри секции
В приведенном ниже примере продукты ранжируются по количеству в указанных местоположениях в описи. Функция DENSE_RANK секционирует результирующий набор по LocationID и логически сортирует его по Quantity. Обратите внимание, что количество продуктов 494 и 495 совпадает. Так как они имеют одинаковое значение количества, им обоим присваивается значение ранга 1.
USE AdventureWorks2022;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,DENSE_RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO
Вот результирующий набор.
ProductID Name LocationID Quantity Rank
----------- ---------------------------------- ---------- -------- -----
494 Paint - Silver 3 49 1
495 Paint - Blue 3 49 1
493 Paint - Red 3 41 2
496 Paint - Yellow 3 30 3
492 Paint - Black 3 17 4
495 Paint - Blue 4 35 1
496 Paint - Yellow 4 25 2
493 Paint - Red 4 24 3
492 Paint - Black 4 14 4
494 Paint - Silver 4 12 5
(10 row(s) affected)
B. Ранжирование всех строк в результирующем наборе
В приведенном ниже примере возвращается список первых десяти сотрудников, ранжированных по окладу. Так как в инструкции SELECT предложение PARTITION BY не указывалось, функция DENSE_RANK применялась ко всем строкам результирующего набора.
USE AdventureWorks2022;
GO
SELECT TOP(10) BusinessEntityID, Rate,
DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory;
Вот результирующий набор.
BusinessEntityID Rate RankBySalary
---------------- --------------------- --------------------
1 125.50 1
25 84.1346 2
273 72.1154 3
2 63.4615 4
234 60.0962 5
263 50.4808 6
7 50.4808 6
234 48.5577 7
285 48.101 8
274 48.101 8
C. Использование четырех ранжирующих функций в одном запросе
В этом примере демонстрируются четыре функции ранжирования:
Они используются в одном запросе. См. конкретные примеры по каждой ранжирующей функции.
USE AdventureWorks2022;
GO
SELECT p.FirstName, p.LastName
,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"
,RANK() OVER (ORDER BY a.PostalCode) AS Rank
,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"
,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile
,s.SalesYTD
,a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
Вот результирующий набор.
| FirstName | LastName | Номер строки | Rank | Dense Rank | Quartile | SalesYTD | PostalCode |
|---|---|---|---|---|---|---|---|
| Николай | Blythe | 1 | 1 | 1 | 1 | 4557045,0459 | 98027 |
| Лиза | Mitchell | 2 | 1 | 1 | 1 | 5200475,2313 | 98027 |
| Jillian | Carson | 3 | 1 | 1 | 1 | 3857163,6332 | 98027 |
| Garrett | Vargas | 4 | 1 | 1 | 1 | 1764938,9859 | 98027 |
| Tsvi | Reiter | 5 | 1 | 1 | 2 | 2811012,7151 | 98027 |
| Shu | Ito | 6 | 6 | 2 | 2 | 3018725,4858 | 98055 |
| Josй | Saraiva | 7 | 6 | 2 | 2 | 3189356,2465 | 98055 |
| Дэвид | Campbell | 8 | 6 | 2 | 3 | 3587378,4257 | 98055 |
| Tete | Mensa-Annan | 9 | 6 | 2 | 3 | 1931620,1835 | 98055 |
| Lynn | Tsoflias | 10 | 6 | 2 | 3 | 1758385,926 | 98055 |
| Rachel | Valdez | 11 | 6 | 2 | 4 | 2241204,0424 | 98055 |
| Jae | Pak | 12 | 6 | 2 | 4 | 5015682,3752 | 98055 |
| Ranjit | Varkey Chudukatil | 13 | 6 | 2 | 4 | 3827950,238 | 98055 |
Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
Г. Ранжирование строк внутри секции
В приведенном ниже примере торговые представители на каждой территории продаж ранжируются в соответствии с общим объемом продаж. Функция DENSE_RANK секционирует набор строк по SalesTerritoryGroup и логически сортирует результирующий набор по SalesAmountQuota.
-- Uses AdventureWorks
SELECT LastName, SUM(SalesAmountQuota) AS TotalSales, SalesTerritoryGroup,
DENSE_RANK() OVER (PARTITION BY SalesTerritoryGroup ORDER BY SUM(SalesAmountQuota) DESC ) AS RankResult
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey
INNER JOIN dbo.DimSalesTerritory AS st ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE SalesPersonFlag = 1 AND SalesTerritoryGroup != N'NA'
GROUP BY LastName, SalesTerritoryGroup;
Вот результирующий набор.
LastName TotalSales SalesTerritoryGroup RankResult
---------------- ------------- ------------------- --------
Pak 10514000.0000 Europe 1
Varkey Chudukatil 5557000.0000 Europe 2
Valdez 2287000.0000 Europe 3
Carson 12198000.0000 North America 1
Mitchell 11786000.0000 North America 2
Blythe 11162000.0000 North America 3
Reiter 8541000.0000 North America 4
Ito 7804000.0000 North America 5
Saraiva 7098000.0000 North America 6
Vargas 4365000.0000 North America 7
Campbell 4025000.0000 North America 8
Ansman-Wolfe 3551000.0000 North America 9
Mensa-Annan 2753000.0000 North America 10
Tsoflias 1687000.0000 Pacific 1
См. также
RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL)
Ранжирование функций (Transact-SQL)
Функции