SQL 競合分析 — 複数CTE・フレーム句・FIRST_VALUEの応用

SELECT
  company_name, quarter, revenue,
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY company_name               -- 企業別に独立ウィンドウ
      ORDER BY     quarter
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 直前2行+当行の計3行で平均
    ), 1
  ) AS moving_avg_3q,
  SUM(revenue) OVER (
    PARTITION BY company_name               -- 企業別に独立ウィンドウ
    ORDER BY     quarter
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭〜当行の累積合計
  ) AS cumulative_rev
FROM   quarterly_revenue
ORDER BY company_name, quarter;

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

SELECT
  category, company_name, annual_revenue,
  ROUND(
    (PERCENT_RANK() OVER (
      PARTITION BY category             -- カテゴリ別に独立ウィンドウ
      ORDER BY     annual_revenue        -- 昇順: 最小値=0%, 最大値=100%
    ) * 100)::numeric, 1
  ) AS pct_rank,
  ROUND(
    (CUME_DIST() OVER (
      PARTITION BY category             -- カテゴリ別に独立ウィンドウ
      ORDER BY     annual_revenue        -- rank/n で自分以下の割合
    ) * 100)::numeric, 1
  ) AS cume_dist_pct
FROM   category_performance
ORDER BY category, annual_revenue DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM category_performance       → 行を読み込む
  2. PERCENT_RANK/CUME_DIST OVER     → ウィンドウ関数を評価（行数は保持）
  3. ROUND(...)                      → 値を整形
  4. SELECT                          → 列を評価
  5. ORDER BY category, annual_revenue DESC → 並び替えて出力
*/

WITH ranked AS (
  SELECT
    category, company_name, composite_score,
    FIRST_VALUE(company_name) OVER (
      PARTITION BY category                -- カテゴリ別に独立ウィンドウ
      ORDER BY     composite_score DESC   -- 降順: 先頭行が最高スコア
    ) AS leader_name,
    FIRST_VALUE(composite_score) OVER (
      PARTITION BY category                -- 同一ウィンドウで首位スコアを取得
      ORDER BY     composite_score DESC
    ) AS leader_score
  FROM   product_scores
)
SELECT
  category, company_name, composite_score,
  leader_name, leader_score,
  ROUND(composite_score - leader_score, 1) AS gap_to_leader  -- 首位との差分（負=遅れ）
FROM   ranked
ORDER BY CASE category
  WHEN 'クラウド' THEN 1
  WHEN 'セキュリティ' THEN 2
  WHEN 'データ分析' THEN 3
  END, composite_score DESC, company_name;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE ranked を定義       → FIRST_VALUE でウィンドウ関数を評価（行数は保持）
  2. 外側クエリ              → 差分を評価し ROUND で値を整形
  3. ORDER BY CASE category WHEN 'クラウド' THEN 1 WHEN 'セキュリティ' THEN 2 WHEN 'データ分析' THEN 3 END, composite_score DESC, company_name → 並び替えて出力
*/

WITH share_with_prev AS (
  SELECT
    company_name, period, market_share_pct,
    LAG(market_share_pct) OVER (
      PARTITION BY company_name    -- 企業別に独立ウィンドウ
      ORDER BY     period          -- 期の昇順で1行前のシェアを参照
    ) AS prev_share               -- 先頭期(2023H1)は前行がなく NULL
  FROM   market_share_trend
),
deltas AS (
  SELECT
    company_name, period, market_share_pct, prev_share,
    ROUND(market_share_pct - prev_share, 1) AS share_delta,  -- 前期比変化量
    CASE
      WHEN market_share_pct > prev_share THEN '▲ 拡大'
      WHEN market_share_pct < prev_share THEN '▼ 縮小'
      ELSE                                    '— 横ばい'
    END AS trend
  FROM   share_with_prev
  WHERE  prev_share IS NOT NULL           -- 先頭期（NULL行）を除外
)
SELECT
  company_name, period,
  market_share_pct, prev_share, share_delta, trend,
  SUM(CASE WHEN trend = '▲ 拡大' THEN 1 ELSE 0 END) OVER (
    PARTITION BY company_name          -- 企業別に累積
    ORDER BY     period               -- 期順に拡大フラグを積み上げ
  ) AS expanding_periods
FROM   deltas
ORDER BY company_name, period;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE share_with_prev を定義 → LAG でウィンドウ関数を評価（行数は保持）
  2. CTE deltas を定義          → WHERE で絞り込み、変化量とラベルを評価
  3. 外側クエリ                 → SUM OVER でウィンドウ関数を評価（行数は保持）
  4. ORDER BY company_name, period → 並び替えて出力
*/