■ はじめに
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