Day9 前半のゴール
「“集計しながら行ごとに順位を付ける”感覚をつかむ」
今日はウィンドウ関数の入り口、ROW_NUMBER と RANK です。
MySQLやSQLiteでも最近は使えますが、PostgreSQLはこのあたりがとても得意な領域です。
前半のゴールはこうです。ROW_NUMBER が「グループごとに1,2,3…と連番を振る関数」だと理解できる。RANK が「同点を同じ順位にして、次の順位が飛ぶ関数」だと説明できる。OVER (PARTITION BY ... ORDER BY ...) の形を見て、「どのグループに、どんな順番で番号を振っているか」をイメージできる。
ここではまず、「文法」より「動きのイメージ」を優先していきます。
ウィンドウ関数とは何か
「“行をまとめて見ながら”、でも“行ごとに値を返す”関数」
普通の集計関数（SUM, COUNT, MAX など）は、こういう動きをします。
テーブル全体、またはGROUP BYしたグループごとに、1行だけ結果を返す。
例えば、ユーザーごとの売上合計を出すときはこうです。
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;
ここでは、「ユーザーごとに1行」しか返ってきません。
個々の注文行は、集計の中に“吸い込まれて”しまいます。
ウィンドウ関数は違います。
「行をまとめて見ながら、でも元の行を残したまま、行ごとに追加情報をくっつける」関数です。
イメージとしては、「テーブルに“計算結果の列”を後付けする」感じです。ROW_NUMBER や RANK は、その代表的な例で、「順位」や「連番」を行ごとに付けてくれます。
基本形：OVER (PARTITION BY … ORDER BY …)
「“どのグループを、どんな順番で並べるか”を指定する」
ウィンドウ関数の基本形は、こんな形をしています。
関数名() OVER (
PARTITION BY グループ分けのカラム
ORDER BY 並べる順番のカラム
)
PARTITION BY は「どの単位でグループ分けするか」。ORDER BY は「そのグループの中で、どんな順番で並べるか」。
例えば、「ユーザーごとに、注文金額の大きい順に順位を付けたい」とします。
そのときはこう書きます。
RANK() OVER (
PARTITION BY user_id
ORDER BY amount DESC
)
これで、「user_idごとに、amountの大きい順に1位、2位、3位…」という順位が付きます。
PARTITION BY を省略すると、「テーブル全体を1つのグループ」として扱います。
つまり、「全体での順位」を付けたいときは、PARTITION BY なしで ORDER BY だけ書きます。
ROW_NUMBER のイメージ
「“かならず 1,2,3… と連番を振る”シンプルな番号」
ROW_NUMBER は、一番シンプルなウィンドウ関数です。
「指定した順番で並べたときに、上から1,2,3…と番号を振る」だけです。
例として、注文テーブルを考えます。
SELECT
id,
user_id,
amount,
ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM orders;
このクエリは、「created_at の古い順に並べたときの通し番号」を row_num として付けます。
結果はこんなイメージです。
id: 10, created_at: 一番古い → row_num = 1
id: 5, created_at: 2番目に古い → row_num = 2
id: 7, created_at: 3番目に古い → row_num = 3
同じ created_at の行があっても、とにかく「1,2,3…」と連番が振られます。
「同点だから同じ番号にする」ということはしません。
PARTITION BY を使うと、「グループごとに連番を振る」こともできます。
SELECT
id,
user_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at
) AS user_row_num
FROM orders;
これで、「ユーザーごとに、注文の古い順に1,2,3…」という番号が付きます。
RANK のイメージ
「“同点は同じ順位、次の順位は飛ぶ”」
RANK は、「順位」を付けるためのウィンドウ関数です。ROW_NUMBER と違って、「同じ値には同じ順位を付ける」という特徴があります。
例えば、「注文金額の大きい順に、全体のランキングを付けたい」とします。
SELECT
id,
user_id,
amount,
RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM orders;
ここで、amount がこうだったとします。
行A: amount = 1000
行B: amount = 900
行C: amount = 900
行D: amount = 800
このときの RANK の結果はこうなります。
行A: 1位（1000）
行B: 2位（900）
行C: 2位（900）
行D: 4位（800）
BとCは同じ金額なので、どちらも2位になります。
その次のDは「3位」ではなく「4位」になります。
ここが RANK の特徴です（順位が“飛ぶ”）。
PARTITION BY を使えば、「ユーザーごとに金額ランキングを付ける」こともできます。
SELECT
id,
user_id,
amount,
RANK() OVER (
PARTITION BY user_id
ORDER BY amount DESC
) AS user_amount_rank
FROM orders;
これで、「user_idごとに、amountの大きい順に1位、2位…」というランキングが付きます。
ROW_NUMBER と RANK の違いを整理する
「“同点をどう扱うか”だけを意識すればOK」
ここまでの話を、感覚レベルでまとめます。
ROW_NUMBER
同じ値でも、必ず 1,2,3… と連番を振る。
「とにかく順番が欲しい」ときに使う。
RANK
同じ値には同じ順位を付ける。
同点があると、次の順位が飛ぶ（1,2,2,4…）。
「“順位”として意味のある番号が欲しい」ときに使う。
どちらも OVER (PARTITION BY ... ORDER BY ...) の形で使い、
「どのグループを、どんな順番で並べるか」を指定するのは同じです。
まずは、「同点を同じ順位にしたいならRANK」「とにかく連番ならROW_NUMBER」という分け方で覚えておけば十分です。
Day9 前半のまとめ
ウィンドウ関数は、「行をまとめて見ながらも、元の行を残したまま“追加の列”として計算結果をくっつける」仕組みで、関数() OVER (PARTITION BY ... ORDER BY ...) の形で「どのグループを、どんな順番で並べるか」を指定する。ROW_NUMBER は、その順番に対して必ず 1,2,3… と連番を振る関数で、PARTITION BY user_id ORDER BY created_at のように書けば「ユーザーごとに、古い順に1,2,3…」という番号を付けられる。RANK は、「同じ値には同じ順位を付け、次の順位が飛ぶ（1,2,2,4…）」という“順位”らしい振る舞いをする関数で、ORDER BY amount DESC と組み合わせれば「金額の大きい順のランキング」を行ごとに付けられる――まずはこの「ROW_NUMBER＝連番」「RANK＝同点同順位」という感覚を押さえるのが Day9 前半の着地点になる。