PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day15 中間課題

SQL PostgreSQL
スポンサーリンク

Day15 後半のゴール

「“現場でそのまま使える分析SQL”まで一段ギアを上げる」

前半では「売上集計＋ランキング」の基本フォームを作りました。
後半では、それをもう一歩だけ実戦寄りにしていきます。

期間条件をパラメータっぽく扱う。
「売上ランキング＋補助情報（割合・累積）」を出す。
CTEを重ねて“読みやすい分析SQL”に仕上げる。

ここまで来ると、「ダッシュボードの裏側に置けるSQL」のイメージにかなり近づきます。

例題1：期間をパラメータ化した商品別売上ランキング

「“直近30日”も“今年”も同じ型で書けるようにする」

まずは、前半で作った商品別売上ランキングに「期間」をちゃんと組み込みます。
ここでは「直近30日」を例にします。

WITH product_sales AS (
  SELECT
    product_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY product_id
)
SELECT
  product_id,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales
ORDER BY sales_rank
LIMIT 20;
SQL

ここでのポイントは、期間条件を「集計の中」に入れていることです。

WHERE ordered_at >= CURRENT_DATE – INTERVAL ’30 days’
→ 集計対象の行を「直近30日」に絞る。

この WHERE を、例えば「今年」に変えたければこうです。

WHERE ordered_at >= date_trunc('year', CURRENT_DATE)
SQL

「期間を変えるだけで、同じ型のSQLがそのまま使える」状態を作るのが大事です。
現場では、アプリ側から「開始日・終了日」をパラメータで渡して、
WHERE ordered_at BETWEEN :from AND :to のように書くことが多いです。

例題2：売上ランキングに“売上構成比”を足す

「ランキングだけでなく“全体の中での重さ”も見る」

単純なランキングだけだと、「1位がどれくらい強いのか」が分かりません。
そこで、「全体売上に対する割合（構成比）」を一緒に出してみます。

WITH product_sales AS (
  SELECT
    product_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY product_id
),
total AS (
  SELECT SUM(sales_amount) AS total_sales
  FROM product_sales
)
SELECT
  ps.product_id,
  ps.sales_amount,
  ROUND(ps.sales_amount / t.total_sales * 100, 2) AS sales_ratio_percent,
  RANK() OVER (ORDER BY ps.sales_amount DESC) AS sales_rank
FROM product_sales ps
CROSS JOIN total t
ORDER BY sales_rank
LIMIT 20;
SQL

流れを分解するとこうです。

product_sales
→ 商品別売上合計（直近30日）。

total
→ その期間の「全商品合計売上」。

最終SELECT
→ 各商品の売上を全体売上で割って、パーセントを出す。

CROSS JOIN total は、「全行に同じ total_sales をくっつける」ためのテクニックです。
これで、「1位の商品が全体の何％を占めているか」が一目で分かります。

例えば、結果がこうだったら：

1位：30%
2位：10%
3位：8%

「1位が圧倒的に強い市場だな」という解釈ができます。
ランキングに“意味”を持たせるために、構成比はかなり効きます。

例題3：売上ランキングに“累積構成比”を足す

「“上位何商品で全体の何％を占めるか”を見る」

さらに一歩進めて、「累積構成比」を出してみます。
これは、「上から順に足していったとき、どこまでで何％になるか」を見る指標です。

さっきのSQLに、ウィンドウ関数を1つ足します。

WITH product_sales AS (
  SELECT
    product_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY product_id
),
total AS (
  SELECT SUM(sales_amount) AS total_sales
  FROM product_sales
),
ranked AS (
  SELECT
    ps.product_id,
    ps.sales_amount,
    ps.sales_amount / t.total_sales AS sales_ratio,
    RANK() OVER (ORDER BY ps.sales_amount DESC) AS sales_rank
  FROM product_sales ps
  CROSS JOIN total t
)
SELECT
  product_id,
  sales_amount,
  ROUND(sales_ratio * 100, 2) AS sales_ratio_percent,
  ROUND(
    SUM(sales_ratio) OVER (ORDER BY sales_rank
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 100,
    2
  ) AS cumulative_ratio_percent,
  sales_rank
FROM ranked
ORDER BY sales_rank
LIMIT 20;
SQL

ここでのキモは、この部分です。

SUM(sales_ratio) OVER (
  ORDER BY sales_rank
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
SQL

これは、「売上比率（sales_ratio）を、ランキング順に上から累積していく」ウィンドウ関数です。

結果イメージ：

1位：売上比率 30%、累積 30%
2位：売上比率 10%、累積 40%
3位：売上比率 8%、累積 48%
…

「上位3商品で全体の48%を占めている」といった読み方ができます。
これは、いわゆる「パレート分析（80:20）」の入口です。

「上位何商品で80%に到達するか」を見ることで、
「どこまでを重点商品として扱うか」の判断材料になります。

例題4：ユーザー別売上ランキング＋“優良顧客候補”のフラグ

「ランキング結果を“セグメント”に落とす」

今度はユーザー別売上ランキングに、「優良顧客候補」のフラグを付けてみます。
例えば、「直近90日で上位10%に入っているユーザー」を優良候補とする、というルールを仮定します。

WITH user_sales AS (
  SELECT
    user_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  WHERE ordered_at >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY user_id
),
ranked AS (
  SELECT
    user_id,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank,
    COUNT(*) OVER () AS user_count
  FROM user_sales
)
SELECT
  user_id,
  sales_amount,
  sales_rank,
  CASE
    WHEN sales_rank <= user_count * 0.1 THEN true
    ELSE false
  END AS is_premium_candidate
FROM ranked
ORDER BY sales_rank
LIMIT 100;
SQL

ここでのポイントは2つです。

COUNT(*) OVER () で「ユーザー総数」を、全行に持たせている。
sales_rank <= user_count * 0.1 で「上位10%以内かどうか」を判定している。

これで、「売上ランキングの上位10%に入っているユーザー」に is_premium_candidate = true が立ちます。

単なるランキングを、「セグメント（優良候補／それ以外）」に落とし込むことで、
マーケティング施策や通知の対象を決める材料になります。

例題5：分析SQLを“読みやすくする”CTEの分割

「1CTE＝1ステップ」にすると、あとから見ても怖くない

ここまでの例を見て、「CTE多くない？」と思ったかもしれません。
でも、分析SQLは「1CTE＝1ステップ」と割り切った方が、長期的には読みやすくなります。

例えば、さっきの「商品別売上ランキング＋構成比＋累積構成比」は、
ざっくりこういうステップに分かれています。

product_sales
→ 商品別売上合計（期間条件込み）。

total
→ 全体売上。

ranked
→ 売上比率とランキングを付ける。

最終SELECT
→ 累積比率を計算して、表示用に整形する。

この「ステップの名前」が、そのままSQLの可読性になります。

CTEをケチって1つのSELECTに全部詰め込むと、
「何をやっているのか」が一気に分かりにくくなり、
バグっても直しづらくなります。

分析SQLは、「多少長くても、意味が追えること」の方が圧倒的に大事です。
Day15 の中間課題では、「CTEを遠慮なく使って、手順をそのままSQLにする」感覚を持ってほしいです。

Day15 後半のまとめ

中間課題レベルの分析SQLでは、「期間条件を WHERE にきちんと組み込み、商品別・ユーザー別などの粒度で売上合計を出すCTEを作り、その上に RANK() を重ねてランキングを付ける」だけでなく、「全体売上を別CTEで計算して構成比（sales_amount / total_sales）を出し、さらに SUM(sales_ratio) OVER (ORDER BY sales_rank ...) で累積構成比を計算する」といった“意味のある指標”まで一気に出してしまうのが実戦的になる。
ユーザー別ランキングに COUNT(*) OVER () を組み合わせて「上位10%に入っているかどうか」のフラグを付ければ、単なる順位表が「優良顧客候補セグメント」に変わり、CTEを「1ステップ＝1意味」で分割することで、SQL自体が“分析の手順書”として読めるようになる。
Day15 後半の着地点は、「売上・ランキングSQLを、期間・構成比・累積・セグメントまで含めて、CTE＋ウィンドウ関数で素直に組み立てられる自分の型を持つこと」。

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