DENSE_RANK (Transact-SQL)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric

Diese Funktion gibt den Rang jeder Zeile innerhalb einer Resultsetpartition ohne Lücken in den Rangwerten zurück. Der Rang einer bestimmten Zeile ist 1 plus die Anzahl der unterschiedlichen Rangwerte vor dieser Zeile.

Transact-SQL-Syntaxkonventionen

Syntax

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) oder früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

<partition_by_clause>
Hierdurch wir das von der FROM-Klausel erzeugte Resultset zunächst partitioniert. Anschließend wird die DENSE_RANK-Funktion auf alle Partitionen angewendet. Weitere Informationen zur PARTITION BY-Syntax finden Sie unter OVER-Klausel (Transact-SQL).

<order_by_clause>
Bestimmt die Reihenfolge, in der die DENSE_RANK-Funktion auf die Zeilen in einer Partition angewendet wird.

Rückgabetypen

bigint

Bemerkungen

Wenn zwei oder mehr Zeilen den gleichen Rangwert in derselben Partition aufweisen, erhalten diese Zeilen den gleichen Rang. Wenn beispielsweise zwei Vertriebsmitarbeiter denselben SalesYTD-Wert aufweisen, erhalten beide den Rangwert 1. Der Vertriebsmitarbeiter mit dem nächsthöchsten SalesYTD-Wert erhält den Rangwert 2. Dies überschreitet die Anzahl der unterschiedlichen Zeilen, die vor der fraglichen Zeile stehen, um 1. Deshalb weisen die von der DENSE_RANK-Funktion zurückgegebenen Zahlen keine Lücken auf und bilden stets fortlaufende Rangwerte.

Die für die gesamte Abfrage verwendete Sortierreihenfolge bestimmt die Reihenfolge der Zeilen im Resultset. Daraus geht hervor, dass eine als Rang 1 festgelegte Zeile nicht notwendigerweise die erste Zeile in der Partition sein muss.

DENSE_RANK ist nicht deterministisch. Weitere Informationen finden Sie unter Deterministische und nicht deterministische Funktionen.

Beispiele

A. Ordnen von Zeilen innerhalb einer Partition

In diesem Beispiel wird die Rangfolge der Produkte im Bestand für die angegebenen Lagerstandorte gemäß ihren Mengen bestimmt. DENSE_RANK partitioniert das Resultset nach LocationID und sortiert es logisch nach Quantity. Beachten Sie, dass die Produkte 494 und 495 die gleiche Menge haben. Da beide den gleichen Mengenwert aufweisen, haben sie einen Rangwert von 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

Hier ist das Resultset.

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. Ordnen aller Zeilen in einem Resultset

In diesem Beispiel werden die ersten zehn Mitarbeiter nach ihrem Gehalt geordneten zurückgegeben. Da die SELECT-Anweisung keine PARTITION BY-Klausel angegeben hat, gilt die DENSE_RANK-Funktion für alle Resultsetzeilen.

USE AdventureWorks2022;  
GO  
SELECT TOP(10) BusinessEntityID, Rate,   
       DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM HumanResources.EmployeePayHistory;

Hier ist das Resultset.

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. Vier Rangfolgefunktionen, die in derselben Abfrage verwendet werden

In diesem Beispiel werden die vier Rangfolgefunktionen veranschaulicht:

Diese werden hier in derselben Abfrage verwendet. Funktionsspezifische Beispiele finden Sie unter der jeweiligen Rangfolgefunktion.

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;

Hier ist das Resultset.

FirstName LastName Row Number Rank Dense Rank Quartile SalesYTD PostalCode
Michael Blythe 1 1 1 1 4557045,0459 98027
Linda 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
David 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

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

D: Ordnen von Zeilen innerhalb einer Partition

Im diesem Beispiel wird die Rangfolge der Vertriebsmitarbeiter in jedem Vertriebsgebiet auf Grundlage von deren Gesamtumsatz bestimmt. DENSE_RANK partitioniert das Rowset nach SalesTerritoryGroup und sortiert das Resultset nach 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;

Hier ist das Resultset.

 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

Weitere Informationen

RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL)
Rangfolgefunktionen (Transact-SQL)
Funktionen