SQL ServerのRANK, DENSE_RANK, ROW_NUMBERの違い

※メモの中で出てくる人物名は仮名です。たまたま同性同名の方がいらっしゃいましたらごめんなさい。

目次

RANKとDENSE_RANKとROW_NUMBERの振る舞い

RANKとDENSE_RANKとROW_NUMBERはそれぞれ似ているけどちょっとだけ違うので違いをメモしておきます。

データ

以下のようなデータを持つテーブルUserがあるとします。

Scoreについて、RANK、DENSE_RANK、ROW_NUMBERをそれぞれ取得すると以下のようになります。

SELECT [Name]
      ,[Age]
      ,[Score]
      ,RANK() OVER(ORDER BY Score) AS RANK
      ,DENSE_RANK() OVER(ORDER BY Score) AS DENSE_RANK
      ,ROW_NUMBER() OVER(ORDER BY Score) AS ROW_NUMBER
FROM [dbo].[User]

取得結果

それぞれは以下のような特徴がある。

• RANKは同率があった場合順位は同じになり、その次の順位は飛ばします。
  • 上記の例だと、3位が2名いるため、4位が飛ばされています。
• DENSE_RANKも同率があった場合は順位は同じになりますが、その次の順位は飛ばしません。
  • 上記の例では、野中さんが4位となっています。
• ROW_NUMBERは、同率があっても同じ順位にはならず、出現順でカウントします。
  • (注意) 出現順は不定なので、上記の例では、たとえば北島さんと永田さんは2名ともスコアは80で順位はひっくり返ることもあります。
    • ちゃんと順序付けしたい場合は、OVER句のORDER BYできちんと指定する必要があります。
      • 例) OVER(ORDER BY Score, Age) …スコアで並べ替えた後、同率ならAgeでも並べ替えるため、順位は一意に決まる。

PARTITION BY句で区切って順位付けする

RANKとDENSE_RANKとROW_NUMBERの振る舞いで使ったテーブルをちょっと加工して、
以下のようなデータを持つテーブルUserを準備します。

このデータについて、Class別に順位付けしたい場合、以下のようにOVER句の中で、PARTITION BYで区切りたい列を指定します。

SELECT [Name]
      ,[Age]
      ,[Score]
      ,[Class]
      ,RANK() OVER(PARTITION BY Class ORDER BY Score) AS RANK
      ,DENSE_RANK() OVER(PARTITION BY Class ORDER BY Score) AS DENSE_RANK
      ,ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Score, Age DESC) AS ROW_NUMBER
FROM [dbo].[User]
ORDER BY [Class], [Score]

取得結果

• 結果として、Classごとの順位付けした結果が取得されます。
• 上記例のROW_NUMBERは、OVER句内のORDER BYの2番目でAgeにDESCで指定しているので、ClassがCの竹中さん、三上さん、緒方さんは年齢の上の人の方からROW_NUMBERが振られます。

参考

参考サイト

• 集計関数（RANK,DENSE_RANK,ROW_NUMBER）の結果の違い
• (Qiita) 分析関数（ウインドウ関数）をわかりやすく説明してみた