SQL カーディナリティ — LEFT JOIN・ROW_NUMBERの基礎

WITH ranked_logins AS (
  SELECT
    user_id,
    login_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_at DESC   -- 最新が rn=1 になる
    ) AS rn
  FROM login_history
)
SELECT
  u.name,
  r.login_at AS last_login
FROM users AS u
INNER JOIN ranked_logins AS r
  ON  u.user_id = r.user_id
  AND r.rn = 1              -- 最新1件のみと結合 → 1:1 になる
ORDER BY u.user_id;

/*
  実行順序:
  1. CTE ranked_logins                → login_history を読み込む
  2. ROW_NUMBER() OVER (...)          → ユーザーごとに最新順で番号付け
  3. FROM users AS u                  → users を読み込む
  4. INNER JOIN ranked_logins (rn=1)  → 最新行のみ結合
  5. SELECT u.name, r.login_at        → 2列を射影
  6. ORDER BY u.user_id               → 並び替えて出力
  */

WITH emp_agg AS (
  SELECT dept_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY dept_id            -- 1:N → 1:1 に変換（dept_id が一意になる）
),
sales_agg AS (
  SELECT dept_id, SUM(amount) AS total_sales
  FROM dept_sales
  GROUP BY dept_id            -- 1:N → 1:1 に変換（dept_id が一意になる）
)
SELECT
  d.dept_name,
  e.total_salary,
  s.total_sales
FROM departments AS d
INNER JOIN emp_agg  AS e ON d.dept_id = e.dept_id  -- 1:1
INNER JOIN sales_agg AS s ON d.dept_id = s.dept_id  -- 1:1
ORDER BY d.dept_id;

/*
  実行順序:
  1. CTE emp_agg           → employees を集計（dept_id 一意）
  2. CTE sales_agg         → dept_sales を集計（dept_id 一意）
  3. FROM departments      → departments を読み込む
  4. INNER JOIN emp_agg    → dept_id で 1:1 結合
  5. INNER JOIN sales_agg  → dept_id で 1:1 結合
  6. SELECT, ORDER BY      → 列を射影し並び替え
  */

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', sale_date)::DATE AS month,  -- 日次を月次粒度に変換
    dept_id,
    SUM(revenue)                              AS total_revenue
  FROM daily_sales
  GROUP BY DATE_TRUNC('month', sale_date)::DATE, dept_id
)
SELECT
  b.month,
  b.dept_id,
  b.budget,
  ms.total_revenue,
  ROUND(ms.total_revenue::NUMERIC / b.budget * 100, 1) AS achievement_rate
FROM monthly_budget AS b
LEFT JOIN monthly_sales AS ms
  ON  b.month   = ms.month
  AND b.dept_id = ms.dept_id
ORDER BY b.month, b.dept_id;

/*
  実行順序:
  1. CTE monthly_sales              → daily_sales を読み込む
  2. DATE_TRUNC('month', ...)       → 月初に丸める
  3. GROUP BY month, dept_id        → 月×部門でグループ化
  4. SUM(revenue)                   → 月次売上を集計
  5. FROM monthly_budget AS b       → 予算を読み込む
  6. LEFT JOIN monthly_sales AS ms  → month+dept_id で結合
  7. ROUND(...)                     → 達成率(%)を計算
  8. SELECT, ORDER BY               → 列を射影し並び替え
  */

-- ✗ AND: 同一行に「コーヒー」かつ「ケーキ」は不可能 → 0件
-- WHERE p.product_name = 'コーヒー' AND p.product_name = 'ケーキ'

SELECT
  u.user_id,
  u.name
FROM users AS u
INNER JOIN purchases AS p
  ON u.user_id = p.user_id
WHERE p.product_name IN ('コーヒー', 'ケーキ')  -- まずORで候補行を絞る
GROUP BY u.user_id, u.name
HAVING COUNT(DISTINCT p.product_name) = 2  -- 2種類とも持つグループのみ残す
ORDER BY u.user_id;

/*
  実行順序:
  1. FROM users AS u                      → users を読み込む
  2. INNER JOIN purchases AS p            → 結合（1:N で展開）
  3. WHERE product_name IN (...)          → 対象商品に絞る
  4. GROUP BY u.user_id, u.name           → グループ化
  5. HAVING COUNT(DISTINCT product_name)  → 2種類購入者のみ残す
  6. SELECT u.user_id, u.name             → 2列を射影
  7. ORDER BY u.user_id                   → 並び替えて出力
  */