SQL 統計分析 — CORR・線形回帰・NTILEの基礎

WITH stats AS (
  -- REGR_* 関数で回帰パラメータを一括算出（CTE で slope・intercept を再利用）
  SELECT
    REGR_SLOPE(revenue, ad_cost)     AS slope,
    REGR_INTERCEPT(revenue, ad_cost) AS intercept,
    REGR_R2(revenue, ad_cost)        AS r_squared
  FROM ad_spend
)
SELECT
  ROUND(slope::numeric,                     4) AS slope,
  ROUND(intercept::numeric,                 2) AS intercept,
  ROUND(r_squared::numeric,                 4) AS r_squared,
  ROUND((slope * 700 + intercept)::numeric, 0) AS predicted_700  -- 回帰式でad_cost=700の予測値
FROM stats;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE stats — FROM ad_spend  → 6行読込
  2. REGR_SLOPE(...)            → 回帰の傾きを計算
  3. REGR_INTERCEPT(...)        → 切片を計算
  4. REGR_R2(...)               → 決定係数を計算
  5. FROM stats                 → CTE の1行を参照
  6. predicted_700              → 予測値を計算
  7. 各 ROUND 適用                 → 丸め
  8. SELECT                     → 1行出力
  */

SELECT
  rep_id,
  region,
  sales_amount,
  RANK()        OVER (PARTITION BY region ORDER BY sales_amount DESC) AS rank_val,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS dense_rank_val,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC, rep_id ASC) AS row_num  -- タイは rep_id で安定化
FROM   sales_rep
ORDER BY region, rank_val, rep_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM sales_rep                     → 9行読込
  2. PARTITION BY region                → 3グループに分割
  3. ORDER BY sales_amount DESC         → 各グループ内ソート
  4. RANK()                             → タイは同順位・次をスキップ
  5. DENSE_RANK()                       → タイは同順位・スキップなし
  6. ROW_NUMBER()                       → 常に連番
  7. ORDER BY region, rank_val, rep_id  → 最終ソート
  8. SELECT                             → 9行出力
  */

WITH segmented AS (
  -- NTILE(4) で降順購買額を4等分し、バケット番号（1=上位25%）を付与
  SELECT
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
  FROM user_spend
)
SELECT
  user_id,
  total_spent,
  quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Heavy'
    WHEN 3 THEN 'Middle'
    WHEN 4 THEN 'Light'
  END AS segment
FROM   segmented
ORDER BY quartile, total_spent DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE segmented — FROM user_spend      → 8行読込
  2. NTILE(4) OVER (...)                  → 4分位に割り当て
  3. 外側SELECT FROM segmented              → CTE を参照
  4. CASE quartile                        → 番号をビジネス用語に変換
  5. ORDER BY quartile, total_spent DESC  → 最終ソート
  6. SELECT                               → 8行出力
  */

WITH lagged AS (
  -- LAG で前月売上を同じ行に並べる（CTE で先に確定させ外側で再利用）
  SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue)::numeric / prev_revenue * 100,
  2) AS mom_growth_pct  -- prev_revenue=NULL の場合も NULL が自動伝播
FROM   lagged
ORDER BY month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE lagged — FROM monthly_revenue  → 6行読込
  2. LAG(revenue, 1) OVER (...)         → 前月の revenue を付与
  3. FROM lagged                        → CTE を参照
  4. (revenue - prev) / prev * 100      → 前月比成長率を計算
  5. ROUND(..., 2)                      → 小数第2位で丸め
  6. ORDER BY month                     → 時系列昇順
  7. SELECT                             → 6行出力
  */