第5章: サブクエリ・CTE・ウィンドウ関数 — モダンSQLの3本柱

-- 非相関サブクエリ: 外側を参照しない → 1回だけ実行して使いまわせる
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
--             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1回評価してキャッシュ可能

-- 相関サブクエリ: 外側の行を参照する → 外側の各行ごとに再評価 (遅い)
SELECT p1.name, p1.price
FROM products p1
WHERE p1.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category_id = p1.category_id  -- ← 外側を参照 = 相関
);
-- 各行で評価されるので O(n^2) に近いコストになる場合が多い
-- ウィンドウ関数で書き換えるほうが速いケースが多い

-- ✅ IN と EXISTS は基本的に等価
SELECT * FROM orders o WHERE o.user_id IN (SELECT id FROM vip_users);
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM vip_users v WHERE v.id = o.user_id);

-- ⚠️ NOT IN の罠: サブクエリが1つでもNULLを返すと結果が空になる
SELECT * FROM orders o WHERE o.user_id NOT IN (SELECT id FROM banned_users);
-- ↑ banned_users.id に NULL が1つでもあれば「全部NULL = UNKNOWN」扱いで
--   どの行も残らない

-- ✅ NOT EXISTS なら NULL安全
SELECT * FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM banned_users b WHERE b.id = o.user_id);

-- ❌ ネストしたサブクエリは読みづらい
SELECT category, avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM (
    SELECT category, price
    FROM products
    WHERE in_stock = TRUE
  ) t
  GROUP BY category
) t2
WHERE avg_price > 1000;

-- ✅ CTEなら上から下に読める
WITH in_stock_products AS (
  SELECT category, price
  FROM products
  WHERE in_stock = TRUE
),
category_avg AS (
  SELECT category, AVG(price) AS avg_price
  FROM in_stock_products
  GROUP BY category
)
SELECT category, avg_price
FROM category_avg
WHERE avg_price > 1000;

-- 組織の階層を全展開(id, name, manager_id を持つ employees テーブル)
WITH RECURSIVE org AS (
  -- アンカー: CEO (manager_idがNULL)
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 再帰部: 前ステップの結果から、それを上司とする部下を取ってくる
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', level) || name AS hierarchy, level
FROM org
ORDER BY level, name;

-- 各社員と、その部署の平均給与を並べて表示
SELECT
  name,
  dept,
  salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

-- 結果:
-- name  | dept | salary | dept_avg
-- Alice | Eng  | 800    | 700      <- Engの平均
-- Bob   | Eng  | 600    | 700
-- Carol | Sales| 500    | 550      <- Salesの平均
-- Dave  | Sales| 600    | 550

-- 部署ごとの給与ランキング Top 3 を抽出(モダンSQL定番パターン)
SELECT *
FROM (
  SELECT
    name, dept, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 3;

-- 前月比売上の増減率(LAGで1行前を参照)
SELECT
  month,
  sales,
  LAG(sales) OVER (ORDER BY month) AS prev_sales,
  (sales - LAG(sales) OVER (ORDER BY month)) * 100.0
    / LAG(sales) OVER (ORDER BY month) AS growth_pct
FROM monthly_sales;

-- 7日間移動平均
SELECT
  date, daily_users,
  AVG(daily_users) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM user_stats;

-- アンチパターン: 相関サブクエリで各行の「部署内順位」を計算
SELECT
  e1.name, e1.salary,
  (SELECT COUNT(*) + 1
   FROM employees e2
   WHERE e2.dept = e1.dept AND e2.salary > e1.salary) AS rank_in_dept
FROM employees e1;
-- O(n^2)に近いコスト

-- リファクタ後: ウィンドウ関数で1回走査
SELECT
  name, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
FROM employees;
-- O(n log n) になる