SQL ウィンドウ関数 — フレーム句・Top-N抽出の応用

SELECT
  sale_date,
  revenue,
  ROUND(
    AVG(revenue) OVER(
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- ORDER BY で行の順序を確定し、フレームの基準を設定
    ), 1                                        -- 現在行の直前2行 + 現在行（最大3行）をフレームとして定義／先頭行や2行目はPRECEDINGが存在しないため、ある行数のみで計算
  ) AS moving_avg_3d                            -- ROUND(..., 1) で小数点第1位に丸める
FROM daily_sales
ORDER BY sale_date;

/*
  実行順序:
  1. FROM daily_sales                          → 5行取得
  2. OVER(ORDER BY sale_date)                  → 日付昇順で確定
  3. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  → 移動平均のフレームを定義
  4. ROUND(..., 1)                             → 丸め
  5. SELECT 出力                                 → 列を出力
  */

SELECT
  student_name,
  subject,
  score,
  ROUND(
    PERCENT_RANK() OVER(
      PARTITION BY subject
      ORDER BY score ASC    -- subject ごとに独立してパーセンタイルを計算
    )::numeric, 2          -- numericへcastして小数点第2位に丸める
  ) AS pct_rank             -- ROUND(..., 2) で小数点第2位に丸める
FROM test_scores
ORDER BY
  CASE subject WHEN '数学' THEN 1 WHEN '英語' THEN 2 END,
  score;

/*
  実行順序:
  1. FROM test_scores      → 5行取得
  2. PARTITION BY subject  → 科目で分割
  3. ORDER BY score ASC    → 科目内をスコア昇順
  4. PERCENT_RANK()        → 各行に適用
  5. ROUND(..., 2)         → 小数第2位に丸め
  6. SELECT 出力             → 明示した科目順・スコア昇順で列を出力
  */

-- CTEで LAG() を一度だけ計算し、外側クエリで成長率を再利用する
WITH base AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER(ORDER BY month) AS prev_revenue  -- 前月の revenue（先頭行は NULL）
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
      / NULLIF(prev_revenue, 0), 1  -- 小数除算を保証。prev が NULL または 0 なら NULL
  ) AS growth_rate_pct
FROM base
ORDER BY month;

/*
  実行順序:
  1. CTE (WITH base)                   → monthly_revenue を取得
  2. OVER(ORDER BY month)              → 月順でソート
  3. LAG(revenue)                      → 前月の収益を付与
  4. 外側 (revenue - prev) * 100.0 / NULLIF(prev, 0)  → 前月比を安全に計算
  5. ROUND(..., 1)                     → 丸めて出力
  */

SELECT
  category,
  product,
  amount
FROM (
  -- サブクエリ内でウィンドウ関数を評価し、行番号を付与する
  SELECT
    category,
    product,
    amount,
    ROW_NUMBER() OVER(
      PARTITION BY category
      ORDER BY amount DESC, product ASC  -- カテゴリごとに金額降順・商品名昇順で一意に並べる
    ) AS rn                         -- 同額でもタイブレーカーにより行番号を確定
  FROM product_sales
) ranked
WHERE rn <= 2                -- サブクエリの外側で計算済みの rn をフィルタ
ORDER BY
  CASE category WHEN 'ドリンク' THEN 1 WHEN 'フード' THEN 2 END,
  amount DESC, product ASC;
/*
  実行順序:
  1. サブクエリ FROM product_sales  → 6行取得
  2. PARTITION BY category     → カテゴリで分割
  3. ORDER BY amount DESC, product ASC  → 金額降順・商品名昇順にソート
  4. ROW_NUMBER()              → 連番を付与
  5. 外側 WHERE rn <= 2          → 上位2件に絞る
  6. SELECT / ORDER BY         → category, product, amount を明示順で出力
  */