PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day9 ウィンドウ関数①

SQL PostgreSQL
スポンサーリンク

Day9 後半のゴール

「“実務でどこに使うか”がパッと浮かぶようにする」

前半で ROW_NUMBER と RANK の動きはつかめました。
後半では、「これをどんな場面で使うのか」「どう書くと気持ちいいのか」を、実務寄りの例で固めていきます。

狙いはシンプルで、「あ、この処理はウィンドウ関数で一発で書けるな」と気づけるようになることです。

例題1：ユーザーごとに“最新の注文1件だけ”を取る

サブクエリ地獄をROW_NUMBERで一撃にする

よくある要件として、「ユーザーごとに一番新しい注文だけ欲しい」というものがあります。
素直に書こうとすると、サブクエリやJOINが絡んで、こうなりがちです。

SELECT o.*
FROM orders o
JOIN (
  SELECT user_id, MAX(created_at) AS max_created_at
  FROM orders
  GROUP BY user_id
) m
  ON o.user_id = m.user_id
 AND o.created_at = m.max_created_at;
SQL

動きは正しいですが、初心者には少し読みにくい形です。
これを ROW_NUMBER で書き直すと、一気にスッキリします。

WITH ranked_orders AS (
  SELECT
    id,
    user_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1;
SQL

ここでやっていることは、こうです。

ユーザーごとに、created_at の新しい順に並べる。
その順番で ROW_NUMBER を振る（1,2,3…）。
rn = 1 だけを取る＝「ユーザーごとの最新1件」。

「グループごとに1件だけ欲しい」という要件は本当に頻出なので、
「PARTITION BY ＋ ORDER BY ＋ ROW_NUMBER ＋ WHERE rn = 1」というパターンは、体に染み込ませておく価値があります。

例題2：カテゴリごとの“売上トップ3商品”を出す

RANKで“同率”をちゃんと扱う

次は、「カテゴリごとに売上トップ3の商品を出したい」という例です。
商品テーブルと売上テーブルがあるとします。

CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  category   TEXT NOT NULL,
  name       TEXT NOT NULL
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL,
  amount     INTEGER NOT NULL
);
SQL

カテゴリごとに、売上合計の高い順にランキングして、上位3位までを出したい。
しかも、「同じ売上なら同じ順位」にしたい、という要件だとします。

RANK を使うと、こう書けます。

WITH product_sales AS (
  SELECT
    p.id,
    p.category,
    p.name,
    SUM(oi.amount) AS total_amount
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  GROUP BY p.id, p.category, p.name
),
ranked_products AS (
  SELECT
    category,
    name,
    total_amount,
    RANK() OVER (
      PARTITION BY category
      ORDER BY total_amount DESC
    ) AS sales_rank
  FROM product_sales
)
SELECT *
FROM ranked_products
WHERE sales_rank <= 3
ORDER BY category, sales_rank, total_amount DESC;
SQL

ここでのポイントは、「集計」と「ランキング」を分けて考えていることです。

product_sales で「商品ごとの売上合計」を出す。
ranked_products で「カテゴリごとに売上ランキングを付ける」。
最後に「3位まで」を絞る。

RANK を使っているので、同じ売上のものは同じ順位になります。
例えば、あるカテゴリで売上がこうだった場合、

A: 1000
B: 900
C: 900
D: 800

順位は 1,2,2,4 となり、「3位の商品がいない」状態になります。
それでも sales_rank <= 3 で絞ると、「1位と2位（2つ）」だけが出てきます。
「同率をちゃんと同じ順位にしたい」場面では、RANK が素直です。

例題3：“重複データの片方だけ残す”クリーニング

ROW_NUMBERで“1つだけ残して他を消す”

現実のDBでは、「同じユーザーが重複して登録されてしまった」ような事故データが入っていることがあります。
例えば、email が同じ行が複数ある、というケースです。

「同じemailの中で、一番新しい行だけ残して、他は削除したい」というとき、
ROW_NUMBER がきれいに使えます。

まずは、「どれを残すか」を決めるための番号を振ります。

WITH numbered_users AS (
  SELECT
    id,
    email,
    name,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC
    ) AS rn
  FROM users
)
SELECT *
FROM numbered_users
WHERE rn > 1;
SQL

このSELECTで、「重複している中の“消したい側”」が全部見えます。
実際に削除するなら、CTEを使ってこう書けます。

WITH numbered_users AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (
  SELECT id FROM numbered_users WHERE rn > 1
);
SQL

ここでの重要ポイントは、「ROW_NUMBERで“残す1件”と“消す側”を明確に分けている」ことです。
RANK ではなく ROW_NUMBER を使っているのは、「同じcreated_atでも必ず1,2,3…と振り分けたい」からです。

例題4：ページネーションをSQL側でやる

ROW_NUMBERで“何ページ目か”を切り出す

Webアプリでは、「一覧を10件ずつ表示する」といったページネーションがよく出てきます。
PostgreSQLなら LIMIT / OFFSET でも書けますが、
ウィンドウ関数を使うと「どの行が何番目か」をはっきりさせた上で切り出せます。

例えば、「created_at の新しい順に並べたときの、2ページ目（11〜20件目）を取りたい」とします。

WITH numbered_orders AS (
  SELECT
    id,
    user_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
)
SELECT *
FROM numbered_orders
WHERE rn BETWEEN 11 AND 20
ORDER BY rn;
SQL

ROW_NUMBER で「全体の通し番号」を振り、
rn BETWEEN 11 AND 20 で「2ページ目」を切り出しています。

この書き方の良さは、「どの行が何番目か」がSQLの中で明示されていることです。
後から「3ページ目は？」となっても、BETWEEN 21 AND 30 に変えるだけで済みます。

ROW_NUMBER / RANK を選ぶときの思考パターン

「“順位”が欲しいのか、“ただの順番”が欲しいのか」

ここまでの例を踏まえて、頭の中の分岐をシンプルにしておきます。

「グループごとに1件だけ欲しい」「重複の片方だけ残したい」「ページングしたい」
こういうときは、「とにかく1,2,3…と振り分けたい」ので ROW_NUMBER が向いています。

「ランキングを出したい」「同点を同じ順位にしたい」
こういうときは、「順位として意味のある数字」が欲しいので RANK が向いています。

どちらも OVER (PARTITION BY ... ORDER BY ...) の形は同じなので、
「同点をどう扱いたいか」だけを意識すれば、どちらを選ぶかは自然に決まります。

CTE＋ウィンドウ関数で“読みやすく強いSQL”にする

「集計・ランキング・フィルタを段階に分ける」

Day8でやったCTEと、Day9のウィンドウ関数は、組み合わせると一気に威力が増します。

例えば、さっきの「カテゴリごとの売上トップ3商品」のSQLは、
CTEとウィンドウ関数の教科書的な組み合わせでした。

product_sales で集計。
ranked_products でランキング。
最後のSELECTでフィルタ。

この「集計」「ランキング」「フィルタ」を1文に押し込めることもできますが、
CTEで段階に分けることで、「何をしているか」が圧倒的に読みやすくなります。

PostgreSQLは、CTEもウィンドウ関数も標準装備で強力なので、
「ちょっと複雑な集計やランキングが出てきたら、まずCTE＋ウィンドウ関数を思い出す」
という癖をつけておくと、アプリ側で無理やりループを書いたり、
複雑なサブクエリ地獄に落ちたりする回数が減ります。

Day9 後半のまとめ

ROW_NUMBER は「グループごとに1,2,3…と必ず連番を振る」性質を活かして、ユーザーごとの最新注文1件を取る（PARTITION BY user_id ORDER BY created_at DESC ＋ WHERE rn = 1）、重複データの片方だけ残す（PARTITION BY email ORDER BY created_at DESC ＋ WHERE rn > 1）、ページネーションで「何番目の行か」をはっきりさせる、といった場面で“実務の武器”として使える。
RANK は「同じ値には同じ順位を付け、次の順位が飛ぶ（1,2,2,4…）」という性質を活かして、カテゴリごとの売上トップ3商品など「同率をちゃんと同じ順位にしたいランキング」をきれいに表現でき、どちらも OVER (PARTITION BY ... ORDER BY ...) の形で「どのグループを、どんな順番で並べるか」を指定する。
そして、CTEと組み合わせて「集計 → ランキング → フィルタ」という段階を WITH で分けて書くことで、サブクエリ地獄に陥らず“読みやすくて強いSQL”にできる――「順位が欲しいならRANK、ただの順番ならROW_NUMBER」という判断軸を持つことが、Day9 後半の着地点になる。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました