SQL ウィンドウ関数 — NTILE・FIRST/LAST VALUEの応用

SELECT
  customer_id,
  name,
  total_amount,
  NTILE(4) OVER(ORDER BY total_amount DESC) AS quartile  -- 降順で4等分（各2行・1=最高額VIP）
FROM customers
ORDER BY total_amount DESC;

/*
  実行順序:
  1. FROM customers              → 8行取得
  2. Window関数 NTILE(4)           → total_amount DESC で4分割し帯番号付与
  3. SELECT 出力                   → 列を出力
  4. ORDER BY total_amount DESC  → 最終整列
  */

SELECT
  measured_date,
  temp,
  ROUND(
    AVG(temp) OVER(
      ORDER BY measured_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 2行前〜現在の3行フレーム（先頭は不足分で平均）
    )
  , 1) AS moving_avg_3d   -- ROUND(..., 1): 小数点第1位に丸める
FROM daily_temperature
ORDER BY measured_date;

/*
  実行順序:
  1. FROM daily_temperature  → 6行取得
  2. Window関数                → フレームで移動平均を計算
  3. ROUND                   → 小数第1位に丸め
  4. SELECT 出力               → 列を出力
  */

SELECT
  stock,
  trade_date,
  price,
  FIRST_VALUE(price) OVER(
    PARTITION BY stock
    ORDER BY     trade_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 全行フレーム（LAST_VALUE と対称に明示）
  ) AS first_price,
  LAST_VALUE(price) OVER(
    PARTITION BY stock
    ORDER BY     trade_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- ★全行フレーム必須（省略すると現在のピア行まで）
  ) AS last_price

FROM stock_prices
ORDER BY stock, trade_date;

/*
  実行順序:
  1. FROM stock_prices → 6行取得
  2. PARTITION BY stock → TYK(3行)とOSK(3行)に分割
  3. 各パーティション内で ORDER BY trade_date で昇順に並べる
  4. FIRST_VALUE → パーティション内の最初の行の price を全行に付与
  5. LAST_VALUE  → UNBOUNDED FOLLOWING指定により、
     パーティション内の最後の行の price を全行に付与する
  6. SELECT 出力
  7. ORDER BY stock, trade_date で整列
*/

WITH ranked AS (
  -- CTE内でウィンドウ関数を計算する（ここでは直接WHERE絞り込みはできない）
  SELECT
    dept,
    emp_name,
    sales,
    ROW_NUMBER() OVER(
      PARTITION BY dept        -- deptごとに番号をリセット
      ORDER BY     sales DESC, emp_name  -- 売上降順・同率は氏名順で 1, 2, 3... と振る
    ) AS rn
  FROM emp_sales
)
SELECT
  dept,
  emp_name,
  sales
FROM ranked
WHERE rn <= 2                  -- 部署ごとの上位2名に絞り込む
ORDER BY
  CASE dept WHEN '営業' THEN 1 WHEN '開発' THEN 2 END,
  sales DESC;

/*
  実行順序:
  1. CTE(ranked) 内部クエリ   → PARTITION+ORDER+ROW_NUMBER で順位付与
  2. CTE(ranked) の結果を保持  → 仮想テーブルとして参照
  3. 外側 SELECT           → WHERE rn でフィルタし整列
  */