DENSE_RANK (Transact-SQL)DENSE_RANK (Transact-SQL)

• 03/16/2017
• • c
  • o
  • h
  • O

適用対象:Applies to: SQL ServerSQL Server (サポートされているすべてのバージョン) SQL ServerSQL Server (all supported versions) Azure SQL データベースAzure SQL DatabaseAzure SQL データベースAzure SQL Database Azure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Parallel Data WarehouseParallel Data WarehouseParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: SQL ServerSQL Server (サポートされているすべてのバージョン) SQL ServerSQL Server (all supported versions) Azure SQL データベースAzure SQL DatabaseAzure SQL データベースAzure SQL Database Azure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Parallel Data WarehouseParallel Data WarehouseParallel Data WarehouseParallel Data Warehouse

この関数は、結果セット パーティション内の各行の順位値をギャップなしで返します。This function returns the rank of each row within a result set partition, with no gaps in the ranking values. 特定の行の順位は、その行より 1 つ前の順位値に 1 を加算したものになります。The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

Transact-SQL 構文表記規則 Transact-SQL Syntax Conventions

構文Syntax

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

引数Arguments

<partition_by_clause>
最初に、FROM 句によって生成された結果セットをパーティションに分割します。その後、DENSE_RANK 関数が各パーティションに適用されます。First divides the result set produced by the FROM clause into partitions, and then the DENSE_RANK function is applied to each partition. PARTITION BY 構文の詳細については、OVER 句 (Transact-SQL) に関するページを参照してください。See OVER Clause (Transact-SQL) for the PARTITION BY syntax.

<order_by_clause>
DENSE_RANK 関数がパーティション内の行に適用される順序を決定します。Determines the order in which the DENSE_RANK function applies to the rows in a partition.

戻り値の型Return Types

bigintbigint

解説Remarks

2 つ以上の行で同じパーティションの順位値が同じになる場合、それぞれの行に同じ順位が与えられます。If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank. たとえば、上位 2 人の販売員の SalesYTD 値が同じ場合は、両方に順位値 1 が与えられます。For example, if the two top salespeople have the same SalesYTD value, they will both have a rank value of one. SalesYTD が次に高い販売員には、順位値 2 が与えられます。The salesperson with the next highest SalesYTD will have a rank value of two. これは、対象となる行より 1 つ前の順位値に 1 を加算したものになります。This exceeds the number of distinct rows that come before the row in question by one. したがって、DENSE_RANK 関数からは、ギャップのない、連続する順位値が常に返されます。Therefore, the numbers returned by the DENSE_RANK function do not have gaps, and always have consecutive rank values.

クエリ全体に使用される並べ替え順序によって、結果セットにおける行の順序が決まります。The sort order used for the whole query determines the order of the rows in the result set. つまり、順位が 1 位である行が必ずしもパーティションの先頭の行とは限りません。This implies that a row ranked number one does not have to be the first row in the partition.

DENSE_RANK は非決定的です。DENSE_RANK is nondeterministic. 詳細については、「決定的関数と非決定的関数」を参照してください。See Deterministic and Nondeterministic Functions for more information.

例Examples

A.A. パーティション内の行に順位を付けるRanking rows within a partition

この例では、指定された在庫場所の在庫内の製品を数量に応じて順位付けしています。This example ranks the products in inventory, by the specified inventory locations, according to their quantities. DENSE_RANK は LocationID で結果セットをパーティション分割し、Quantity で論理的に結果セットを並べ替えます。DENSE_RANK partitions the result set by LocationID and logically orders the result set by Quantity. 494 と 495 の製品が同じ数量であることを確認します。Notice that products 494 and 495 have the same quantity. いずれの数量値も同じであるため、両方に順位値 1 が与えられます。Because they both have the same quantity value, they both have a rank value of one.

USE AdventureWorks2012;  
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  

結果セットは次のようになります。Here is the result set.

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.B. 結果セット内のすべての行に順位を付けるRanking all rows in a result set

この例では、給与に順位を付け、トップ 10 の従業員を返します。This example returns the top ten employees ranked by their salary. SELECT ステートメントで PARTITION BY 句が指定されなかったため、DENSE_RANK 関数はすべての結果セット行に適用されました。Because the SELECT statement did not specify a PARTITION BY clause, the DENSE_RANK function applied to all result set rows.

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

結果セットは次のようになります。Here is the result set.

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.C. 同じクエリで順位付け関数を 4 つ使うFour ranking functions used in the same query

この例では、次の 4 つの順位付け関数This example shows the four ranking functions

• DENSE_RANK()DENSE_RANK()
• NTILE()NTILE()
• RANK()RANK()
• ROW_NUMBER()ROW_NUMBER()

が同じクエリで使用されます。used in the same query. 各順位付け関数をそれぞれの例で確認してください。See each ranking function for function-specific examples.

USE AdventureWorks2012;  
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;  

結果セットは次のようになります。Here is the result set.

例: Azure Synapse AnalyticsAzure Synapse Analytics、Parallel Data WarehouseParallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics and Parallel Data WarehouseParallel Data Warehouse

D:パーティション内の行に順位を付けるD: Ranking rows within a partition

この例では、売上合計に応じて販売区域ごとに販売担当者をランク付けします。This example ranks the sales representatives in each sales territory according to their total sales. DENSE_RANK は SalesTerritoryGroup で行セットをパーティション分割し、SalesAmountQuota で結果セットを並べ替えます。DENSE_RANK partitions the rowset by SalesTerritoryGroup, and sorts the result set by 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;  

結果セットは次のようになります。Here is the result set.

 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 

参照See Also

RANK (Transact-SQL) RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL) ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL) NTILE (Transact-SQL)
順位付け関数 (Transact-SQL) Ranking Functions (Transact-SQL)
関数Functions