SQL 競合分析 — Anti Join・移動平均・累積シェアの基礎

SELECT
  company_name,
  quarter,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY company_name            -- 企業ごとに独立したウィンドウ
      ORDER BY     quarter               -- 四半期昇順でフレームの「現在行」を定義
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 直近3四半期を対象
    ), 1
  ) AS moving_avg_3q
FROM   quarterly_revenue
ORDER BY company_name, quarter;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM quarterly_revenue          → 行を読み込む
  2. AVG(revenue) OVER (...)         → ウィンドウ関数を評価（行数は保持）
  3. SELECT                          → 列を評価（moving_avg_3q）
  4. ORDER BY company_name, quarter  → 並び替えて出力
*/

SELECT
  company_name,
  SUM(revenue)                    AS total_revenue,
  ROUND(AVG(churn_rate_pct), 1)  AS avg_churn_pct,
  SUM(new_customers)              AS total_new_customers
FROM   competitor_metrics
GROUP BY company_name
HAVING   SUM(revenue)          >= 1000   -- 条件①: 年間売上1,000億以上
     AND AVG(churn_rate_pct)   <=    5.0  -- 条件②: 平均解約率5.0%以下
     AND SUM(new_customers)    >=   50   -- 条件③: 年間新規顧客50万件以上
ORDER BY total_revenue DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM competitor_metrics      → 行を読み込む
  2. GROUP BY company_name        → グループ化
  3. SUM/AVG                      → 集計関数を評価
  4. HAVING（3条件）              → グループを絞り込む
  5. SELECT                       → 列を評価
  6. ORDER BY total_revenue DESC  → 並び替えて出力
*/

SELECT
  c.company_name,
  cat.category_name,
  COALESCE(sd.annual_revenue, 0) AS annual_revenue  -- NULLを0に置換
FROM       companies   c
CROSS JOIN categories  cat                     -- 3社×3カテゴリ=9通りの全組み合わせ
LEFT JOIN  sales_data  sd
           ON  sd.company_id  = c.company_id
           AND sd.category_id = cat.category_id  -- 両キーで照合
ORDER BY c.company_name, cat.category_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM companies c                    → 行を読み込む
  2. CROSS JOIN categories cat           → 結合（直積）
  3. LEFT JOIN sales_data sd             → 結合（左表を全行保持）
  4. SELECT                              → 列を評価（annual_revenue を整形）
  5. ORDER BY company_name, category_id  → 並び替えて出力
*/

SELECT
  company_name,
  annual_revenue,
  SUM(annual_revenue) OVER (
    ORDER BY annual_revenue DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭〜現在行の累積
  ) AS cumulative_revenue,
  ROUND(
    SUM(annual_revenue) OVER (
      ORDER BY annual_revenue DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) * 100.0 /
    SUM(annual_revenue) OVER (),                      -- 分母: 全体合計（OVER()=全行）
    1
  ) AS cumulative_share_pct
FROM   competitor_revenue
ORDER BY annual_revenue DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM competitor_revenue       → 行を読み込む
  2. SUM(...) OVER ()              → 全体合計を全行に付与
  3. SUM(...) OVER (...)           → 累積集計（Running Total）
  4. 累積値 / 全体合計                    → 累積シェア(%)を算出
  5. SELECT                        → 列を選択
  6. ORDER BY annual_revenue DESC  → 並び替えて出力
  */