SQL の分析関数である RANK と DENSE_RANK を使うと、順序が付けられた値のセットの中で何番目の値かを返すことができる。今回は RANK と DENSE_RANK を使って、動きを確認していく。

実行環境

テストの場所は、SQL Fiddleで試した。

エンジンのバージョンはPostgreSQL 9.6を使っている。

https://www.postgresql.jp/document/9.3/html/functions-window.html

データ投入

今回使用するデータはclass,score の列からなっていて、10行分のデータが入っている。

CREATE TABLE RANK_TEST
(
 class  CHAR(3) ,
 score INTEGER
);

BEGIN TRANSACTION;
INSERT INTO RANK_TEST VALUES ('A', 90);
INSERT INTO RANK_TEST VALUES ('A', 90);
INSERT INTO RANK_TEST VALUES ('A', 40);
INSERT INTO RANK_TEST VALUES ('A', 30);
INSERT INTO RANK_TEST VALUES ('A', 20);
INSERT INTO RANK_TEST VALUES ('B', 100);
INSERT INTO RANK_TEST VALUES ('B', 90);
INSERT INTO RANK_TEST VALUES ('B', 40);
INSERT INTO RANK_TEST VALUES ('B', 40);
INSERT INTO RANK_TEST VALUES ('B', 10);
COMMIT;

シンプルな実行

SQL を組み立てて実行する。 RANK 関数とDENSE_RANK関数は次の形で指定する。

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

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

実際の SQL にすると次になる。 score の列を対象に、ランク付けしている。並び順は score に従っている。

SELECT 
      class
      ,score
      ,RANK() over (order by score desc)
      ,DENSE_RANK() over (order by score desc)
FROM RANK_TEST

class関係なく、スコア通りに順位がランク付けされている。ここまでRANK関数・DENSE_RANK関数の違いの説明なくきたが、結果を見てもらうとすぐわかると思う。 RANK関数は重複している順位があれば、次の数字は重複している数の分だけスキップされその後順位付けが再開される。 DENSE_RANK関数は重複している順位があっても、次の数字をスキップすることなく、その後順位付けが再開される。その結果、RANK関数は最後がランク10になっているのに対し、DENSE_RANK関数は最後のランクが6になっている。 DENSEの意味は「（人や物が）密集している」「（物の）密度が高い」である。この場合はランクの数が密集しているとなり、数がスキップされていないことがわかる。

| class | score | rank | dense_rank | |-------|-------|------|------------| | B | 100 | 1 | 1 | | A | 90 | 2 | 2 | | A | 90 | 2 | 2 | | B | 90 | 2 | 2 | | A | 40 | 5 | 3 | | B | 40 | 5 | 3 | | B | 40 | 5 | 3 | | A | 30 | 8 | 4 | | A | 20 | 9 | 5 | | B | 10 | 10 | 6 |

PARTITON BY

PARTITION BYにscoreを指定して、クラスごとにスコアを比較する。

SELECT 
      class
      ,score
      ,RANK() over (partition by class order by score desc)
      ,DENSE_RANK() over (partition by class order by score desc)
FROM RANK_TEST

クラスごとにまとめられ、その中でスコアでソート、その後ランク付けがおこなわれているのがわかる。

| class | score | rank | dense_rank |
|-------|-------|------|------------|
|   A   |    90 |    1 |          1 |
|   A   |    90 |    1 |          1 |
|   A   |    40 |    3 |          2 |
|   A   |    30 |    4 |          3 |
|   A   |    20 |    5 |          4 |
|   B   |   100 |    1 |          1 |
|   B   |    90 |    2 |          2 |
|   B   |    40 |    3 |          3 |
|   B   |    40 |    3 |          3 |
|   B   |    10 |    5 |          4 |