SQLでオークションの順位をつけてみる（RANK関数）

備忘録としてメモ。使用環境は、SQLServerです。

(1)全体での順位をふる

以下のようなオークションデータのテーブルがあるとします。
（分かりやすくするため、テーブル名や列名は、日本語にしています。）

商品ID	入札者ID	入札金額
39d8s20	253150	5300
4kkso31	303150	500
39d8s20	303150	5320
39d8s20	100010	5320
4kkso31	432000	800
4kkso31	100010	1200
4kkso31	253150	1300

　　※本ページの末尾に、上記データを作成するSQLを載せています。

まずは入札金額が多い順に、順位をふってみます。RANK 関数を使います。

記述例

SELECT RANK() OVER(ORDER BY [入札金額] DESC) AS [順位]
       , [商品ID], [入札者ID], [入札金額]
FROM [オークションデータ]
ORDER BY [商品ID], [入札金額] DESC

実行結果

順位	商品ID	入札者ID	入札金額
1	39d8s20	303150	5320
1	39d8s20	100010	5320
3	39d8s20	253150	5300
4	4kkso31	253150	1300
5	4kkso31	100010	1200
6	4kkso31	432000	800
7	4kkso31	303150	500

入札金額の多い順に順位がふられました。
金額が同じだったら、きちんと同じ順位になっていますね。素晴らしい！
でも、これでは、商品に関係なく順位がふられてしまうので、おかしいですね！

(2)商品ごとの順位をふる

次に、商品ごとに、入札金額が多い順に、順位をふります。

記述例

SELECT RANK() OVER(PARTITION BY [商品ID] ORDER BY [入札金額] DESC) AS [順位]
       , [商品ID], [入札者ID], [入札金額]
FROM [オークションデータ]
ORDER BY [商品ID], [入札金額] DESC

OVER 句の中に、PARTITION BY [商品ID] が追加されています。

実行結果

順位	商品ID	入札者ID	入札金額
1	39d8s20	303150	5320
1	39d8s20	100010	5320
3	39d8s20	253150	5300
1	4kkso31	253150	1300
2	4kkso31	100010	1200
3	4kkso31	432000	800
4	4kkso31	303150	500

出品商品ごとに、入札金額が多い順に、順位がふられました。
実行結果の上位３行は、「39d8s20」という商品について、入札金額が多い順に１位から３位まで順位が振られています。
４行目～は、「4kkso31」という商品について、１位から４位まで順位が振られています。
想定通りの結果になりました！

感想

プログラムのコードを書くこと無く、SQLで一発で順位をふる事ができるのは、便利と思いました。

テストデータ

本ページのSQLを試すためのテストデータです。

テストデータ

CREATE TABLE [オークションデータ](
    [オークションID] bigint IDENTITY(1,1) NOT NULL,
    [商品ID] varchar(8) NOT NULL,
    [入札者ID] int NULL,
    [入札金額] money NULL,
 CONSTRAINT [PK_オークションデータ] PRIMARY KEY CLUSTERED 
(
    [オークションID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('39d8s20', 253150, 5300);
INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('4kkso31', 303150, 500);
INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('39d8s20', 303150, 5320);
INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('39d8s20', 100010, 5320);
INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('4kkso31', 432000, 800);
INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('4kkso31', 100010, 1200);
INSERT INTO [オークションデータ] ([商品ID], [入札者ID], [入札金額]) VALUES('4kkso31', 253150, 1300);