SQL読解の応用編。ウィンドウ関数とCTEを素早く読み解くための実践問題。
白峰 リリ
ウィンドウ関数のクエリ、毎回こんがらがっちゃうんだよね…
神楽 モニカ 先生
OVER句の中のPARTITION BYとORDER BYを最初に読むのがコツなのよ。 そうすれば『何で区切って・何で並べた上で・何を計算するか』が見えてくるということね。
-- ▼ 問題例1: 各カテゴリ内での売上順位
SELECT
product_id,
category,
total_sales,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY total_sales DESC
) AS rank_in_cat
FROM product_summary;
-- 解読:
-- - category で区切り
-- - その中で total_sales 降順で並べ
-- - 1位から順に番号 (rank_in_cat=1, 2, 3, ...)
-- ▼ 問題例2: 累計と前月比
SELECT
month,
monthly_sales,
SUM(monthly_sales) OVER (
ORDER BY month
) AS cumulative,
monthly_sales - LAG(monthly_sales) OVER (
ORDER BY month
) AS diff_vs_prev
FROM monthly_summary
ORDER BY month;
-- 解読:
-- - SUM(...) OVER (ORDER BY ...) で累計
-- - LAG(...) で前月の値を取得
-- - 当月 - 前月 で前月比 (最初の月は NULL)
藍沢 しずく
ROW_NUMBERとRANKとDENSE_RANKの違い、整理してほしいですぅ〜。
紅林 かえで
金額が同じ2件があった場合、ROW_NUMBERは(1,2,3,4,5)で重複なし、RANKは(1,1,3,4,5)で同値同順位・次は飛ぶ、DENSE_RANKは(1,1,2,3,4)で同値同順位・連続、という違いがあります。 用途で使い分けるといいですね。
-- ▼ 問題例3: CTE で多段クエリ
WITH monthly AS (
SELECT
customer_id,
DATE_TRUNC('month', ordered_at) AS month,
SUM(amount) AS monthly_amt
FROM orders
GROUP BY 1, 2
),
ranked AS (
SELECT
*,
RANK() OVER (PARTITION BY month ORDER BY monthly_amt DESC) AS rk
FROM monthly
)
SELECT month, customer_id, monthly_amt
FROM ranked
WHERE rk <= 3
ORDER BY month, rk;
-- 解読:
-- 1段目 monthly: 顧客×月の売上集計
-- 2段目 ranked: 各月内の売上順位を付与
-- 最終: 各月のトップ3顧客を抽出
白峰 リリ
CTEだと、段階ごとに何してるか追えるから分かりやすいね!
神楽 モニカ 先生
そうそう、CTEは『先に小さなブロックを定義して、最後にそれらを組み合わせる』設計なのよ。 各CTEを独立に意味づけてから全体を見ると読みやすくなるということね。
紅林 かえで
DS検定★では、このレベルのCTEとウィンドウ関数の組み合わせ問題が出ます。 慣れれば30秒で読み切れるようになりますよ。
確認クイズ
RANK() OVER (PARTITION BY category ORDER BY price DESC) で、同カテゴリ内に同価格が2件並んだ場合の順位はどうなるか。
- 1, 2, 3, 4, 5(重複なし)
- 1, 1, 3, 4, 5(同値同順位、次は飛ぶ)
- 1, 1, 2, 3, 4(同値同順位、次も連続)
- ランダム順
こたえを見る
正解: 2. 1, 1, 3, 4, 5(同値同順位、次は飛ぶ)
1, 1, 3, 4, 5(同値同順位、次は飛ぶ)がRANK()の挙動。 順位が1位の人が2人いれば次は3位。 重複なしならROW_NUMBER、連続させたいならDENSE_RANK を使います。
