ウィンドウ関数① ― 順位をつける

導入

「価格の高い順に順位を振りたい」。ORDER BY で並べ替えはできても、「1位・2位・3位…」という順位の番号そのものは付きません。これを一発で付けてくれるのが ウィンドウ関数 です。集計関数（SUM など）が複数行を1つにまとめてしまうのに対し、ウィンドウ関数は行を保ったまま、周りの行を見て計算します。

説明

順位付けの代表は ROW_NUMBER()・RANK()・DENSE_RANK() の3つ。いずれも OVER (ORDER BY ...) を付けて「どの順で番号を振るか」を指定します。

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS 順位
FROM products;

OVER (...) の中の ORDER BY は、SELECT 全体の並び順とは別物で、「順位計算のための並び」を表します。3つの違いは、同じ値（同順位）が出たときの振る舞いです。

さらに強力なのが PARTITION BY です。「グループごとに順位を振り直す」指定で、たとえば「カテゴリごとの価格ランキング」が書けます。

SELECT category_id, name, price,
  RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS カテゴリ内順位
FROM products;

flowchart LR
    P["PARTITION BY category_id<br/>カテゴリで区切る"] --> O["ORDER BY price DESC<br/>各区切りの中で並べて順位を振る"]

やってみよう

初期表示のクエリを実行し、価格の高い順に順位が付くことを確認しましょう（ノートPCが1位）。次に上の PARTITION BY category_id の例を実行して、カテゴリごとに順位が 1 から振り直されることを見てください。RANK() を ROW_NUMBER() や DENSE_RANK() に変えて、違いも試しましょう。

演習

users を対象に、年齢の高い順の順位を ROW_NUMBER() で振ってください。取り出す列は name・age と、順位の別名 順位。