総合演習② ― 会員別レポートを作る

導入

締めくくりは、この章の集大成。「会員ごとの購入総額を出し、一度も買っていない人も 0 として含め、金額でランク分けする」――CTE・複数 JOIN・LEFT JOIN・COALESCE・CASE を1つのクエリに束ねた、実務そのものの会員レポートを作ります。

説明

考え方を分解すると、こうなります。

flowchart TB
    S["① CTE spend で<br/>会員ごとの購入総額を集計<br/>（orders×order_items×products）"] --> J["② users に LEFT JOIN<br/>（買っていない人も残す）"]
    J --> C["③ COALESCE で 0 埋め・<br/>CASE で VIP/一般に区分"]

WITH spend AS (...) で先に「会員IDと購入総額」を作っておき、本体で users に LEFT JOIN。買った実績がない会員（山本さん）は spend に現れないので、LEFT JOIN ＋ COALESCE(s.total, 0) で「0円」として拾います。仕上げに CASE で「10万円以上なら VIP」と区分しています。

WITH spend AS (
  SELECT o.user_id, SUM(oi.quantity * p.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  GROUP BY o.user_id
)
SELECT u.name,
  COALESCE(s.total, 0) AS 購入額,
  CASE WHEN COALESCE(s.total, 0) >= 100000 THEN 'VIP' ELSE '一般' END AS 区分
FROM users u
LEFT JOIN spend s ON s.user_id = u.id
ORDER BY 購入額 DESC;

第1章の SELECT から始まった旅の到達点が、この1クエリです。取り出す・つなぐ・まとめる・振り分ける――部品はすべて、これまでの章で学んだものです。

やってみよう

初期表示のクエリを実行し、会員別の購入額ランキングと VIP/一般の区分が出ることを確認しましょう。山本さんが「0円・一般」で最下位に並ぶはずです。VIP のしきい値 100000 を変えたり、区分 に「優良」ランクを足したり（CASE に WHEN ... THEN を増やす）して、レポートを育ててみてください。

演習

上のクエリに、購入額の高い順の順位を RANK() で足してください（第7章のウィンドウ関数を使います）。SELECT に RANK() OVER (ORDER BY COALESCE(s.total, 0) DESC) AS 順位 を追加します。