DENSE_RANK (Transact-SQL)
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウス
この関数は、結果セット パーティション内の各行の順位値をギャップなしで返します。 特定の行の順位は、その行より 1 つ前の順位値に 1 を加算したものになります。
構文
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
<partition_by_clause>
最初に、FROM 句によって生成された結果セットをパーティションに分割します。その後、DENSE_RANK 関数が各パーティションに適用されます。 PARTITION BY 構文の詳細については、OVER 句 (Transact-SQL) に関するページを参照してください。
<order_by_clause>
DENSE_RANK 関数がパーティション内の行に適用される順序を決定します。
戻り値の型
bigint
解説
2 つ以上の行で同じパーティションの順位値が同じになる場合、それぞれの行に同じ順位が与えられます。 たとえば、上位 2 人の販売員の SalesYTD 値が同じ場合は、両方に順位値 1 が与えられます。 SalesYTD が次に高い販売員には、順位値 2 が与えられます。 これは、対象となる行より 1 つ前の順位値に 1 を加算したものになります。 したがって、DENSE_RANK 関数からは、ギャップのない、連続する順位値が常に返されます。
クエリ全体に使用される並べ替え順序によって、結果セットにおける行の順序が決まります。 つまり、順位が 1 位である行が必ずしもパーティションの先頭の行とは限りません。
DENSE_RANK は非決定的です。 詳細については、「決定的関数と非決定的関数」を参照してください。
例
A. パーティション内の行に順位を付ける
この例では、指定された在庫場所の在庫内の製品を数量に応じて順位付けしています。 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. 結果セット内のすべての行に順位を付ける
この例では、給与に順位を付け、トップ 10 の従業員を返します。 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. 同じクエリで順位付け関数を 4 つ使う
この例では、次の 4 つの順位付け関数
が同じクエリで使用されます。 各順位付け関数をそれぞれの例で確認してください。
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 | 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 |
例: Azure Synapse Analytics、Analytics Platform System (PDW)
D:パーティション内の行に順位を付ける
この例では、売上合計に応じて販売区域ごとに販売担当者をランク付けします。 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)
関数
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示