ウィンドウ関数 — 順位を付ける

ウィンドウ関数とは

GROUP BY で集計すると、 行がまとまって 元の行が消えます。

SELECT category, AVG(price) FROM products GROUP BY category;
-- → カテゴリごとに 1 行。 個別の商品名はもう出せない

ウィンドウ関数 は 「元の行を残したまま、 まわりの行を見て計算する」 仕組みです。

行数が減らないのが GROUP BY との決定的な違いです。

関数名() OVER (PARTITION BY 区切る列 ORDER BY 並べる列)

OVER が 「どの範囲を見るか」 の指定です。 これが付いた時点で ウィンドウ関数になります。

順位を付ける 3 つの関数

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

OVER の中の ORDER BY は 並び順を決めるためではなく、 順位の基準 です。

表示順を変えたいなら 外側にも ORDER BY が要ります。

3 つの違いは 同じ値 (同順位) が出たとき に現れます。

orders の quantity で並べると 差がはっきり出ます。

SELECT id, quantity,
       RANK()       OVER (ORDER BY quantity DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY quantity DESC) AS dns
FROM orders;
-- quantity=2 が 2 件あるので、RANK は 3,3 のあと 5 に飛び、
-- DENSE_RANK は 3,3 のあと 4 になる

「上位 3 件を取りたい」 なら 同率を含めたい場合は RANK、

きっかり 3 行にしたい場合は ROW_NUMBER を選びます。

PARTITION BY — グループごとに順位を付け直す

SELECT category, name, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

PARTITION BY を付けると、 その列の値が変わるたびに 順位が 1 に戻ります。

GROUP BY のように行はまとまらず、 「カテゴリごとの何位か」 が各行に付きます。

グループごとの 1 位だけを取り出す

ウィンドウ関数で 一番よく使う型がこれです。

SELECT category, name, price FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) WHERE rn = 1;

WHERE で rn を直接絞れない ので、 いったんサブクエリにしてから外側で絞ります。

これは SQL の評価順のせいで、 WHERE はウィンドウ関数より先に処理されるためです。

覚えておくと エラーの原因がすぐ分かります。

よくあるつまずき

- WHERE の中でウィンドウ関数を使ってエラー — 上のとおり サブクエリに包みます。

- OVER を書き忘れる — RANK() だけでは ただの関数呼び出しでエラーになります。

- OVER の ORDER BY と 外側の ORDER BY を混同する — 前者は順位の基準、

後者は表示順。 両方書いて構いません。

実務での使いどころ

「部署ごとの給与トップ 3」 「顧客ごとの最新の注文」 「カテゴリ別の売れ筋 1 位」 —

どれも PARTITION BY + ROW_NUMBER の型で解けます。 サブクエリを何段も重ねずに

1 回のスキャンで済むので、 行数が増えたときの差も大きく出ます。