【SQL】Window関数 ～ RANK / DENSE_RANK ～

SQL

■ はじめに

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

目次

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

【１】RANK / DENSE_RANK

１）RANK

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

２）DENSE_RANK

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

【２】RANK + PARTITION BY

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

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

【３】サンプル - 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]

 例１：顧客ごとの売上順位

 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]

 例２：売上の上位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