【SQL】Window関数 ~ RANK / DENSE_RANK ~

■ はじめに

SQL で、順位付け・ランキングをする際に使用する
RANK / DENSE_RANK について、学ぶ。

目次

【1】RANK / DENSE_RANK
 1)RANK
 2)DENSE_RANK
【2】RANK + PARTITION BY
【3】サンプル - SQL Server
 使用しているテーブル
 例1:顧客ごとの売上順位
 例2:売上の上位3位の顧客を取得

【1】RANK / DENSE_RANK

1)RANK

* 同じ順位があった場合、順位は同じになり、その次は順位を飛ばす。
* 例:1位、1位、3位・・・

2)DENSE_RANK

* 同じ順位があった場合、順位は同じになり、その次は順位を飛ばさない。
* 1位、1位、2位・・・

cf. dense = 密集した、密度の高い

【2】RANK + PARTITION BY

以下の関連記事を参照のこと。

ROW_NUMBER / RANK + PARTITION BY
https://dk521123.hatenablog.com/entry/2021/03/11/210937

【3】サンプル - SQL Server

使用しているテーブル

Orders

CREATE TABLE [Orders](
    [orderID] [bigint] NOT NULL,
    [customerID] [varchar](15) NOT NULL,
    [orderDateTime] [datetime] NOT NULL,
    [Remark] [nvarchar](100) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [orderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 OrderDetails

CREATE TABLE [OrderDetails](
    [orderID] [bigint] NOT NULL,
    [productID] [nvarchar](50) NOT NULL,
    [UnitPrice] [money] NULL,
    [Quantity] [int] NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [orderID] ASC,
    [productID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 例1:顧客ごとの売上順位

 SQL

SELECT
      DENSE_RANK() OVER(ORDER BY SUM([UnitPrice]*[Quantity]) DESC) AS Ranking
      ,[customerID]
      ,SUM([UnitPrice]*[Quantity]) AS TotalSales
  FROM [Orders]
  INNER JOIN [OrderDetails]
  ON [Orders].orderID = [OrderDetails].orderID
  GROUP BY [customerID]

 例2:売上の上位3位の顧客を取得

 * SQL ServerのTOP句を使うと、最後が同じだった場合、
 問答無用で切り捨てられてしまう

 SQL

SELECT
Ranking
,customerID
,TotalSales
FROM
(SELECT
      DENSE_RANK() OVER(ORDER BY SUM([UnitPrice]*[Quantity]) DESC) AS Ranking
      ,[customerID]
      ,SUM([UnitPrice]*[Quantity]) AS TotalSales
  FROM [SampleDB].[dbo].[Orders]
  INNER JOIN [SampleDB].[dbo].[OrderDetails]
  ON [Orders].orderID = [OrderDetails].orderID
  GROUP BY [customerID]) AS RankingTable
  WHERE  RankingTable.Ranking BETWEEN 1 AND 3

 参考資料

http://stdman.blogspot.jp/2008/08/blog-post.html
http://handcraft.blogsite.org/Memo/Article/Archives/126

関連記事

Window関数 ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2023/02/22/000000
Window関数 ~ ROW_NUMBER ~
https://dk521123.hatenablog.com/entry/2021/09/10/092850
Window関数 ~ ROW_NUMBER / RANK + PARTITION BY ~
https://dk521123.hatenablog.com/entry/2021/03/11/210937
Window関数 ~ LAG / LEAD ~
https://dk521123.hatenablog.com/entry/2021/09/10/092850
サブクエリ / 副問合せ
https://dk521123.hatenablog.com/entry/2015/12/21/002727
期間が重なるデータを取得するには
https://dk521123.hatenablog.com/entry/2011/12/17/025502