SQL サブクエリ — 相関・派生テーブル×JOINの応用

SELECT
  user_id,
  ROUND(AVG(amount)) AS avg_amount  -- 表示用にROUNDで整数化
FROM   orders
WHERE  status = 'completed'         -- 集計前フィルタ（行単位）
GROUP BY user_id                    -- ユーザーごとにグループ化
HAVING AVG(amount) > (              -- 集計後フィルタ（グループ単位）
  SELECT AVG(amount)                -- スカラーSQ: completed全体の平均を先行評価
  FROM   orders
  WHERE  status = 'completed'       -- 外側と同じ条件で揃える
)
ORDER BY avg_amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. スカラーSQ先行評価                 → completed の平均を閾値として確定
  2. FROM orders                → 全行読み込み
  3. WHERE status='completed'   → completed に絞る
  4. GROUP BY user_id           → ユーザーで集計
  5. HAVING AVG(amount) > 閾値    → 平均超のグループを残す
  6. SELECT ROUND(AVG(amount))  → 平均を出力
  7. ORDER BY avg_amount DESC   → 降順
  */

SELECT
  u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (                   -- 条件①: completedの注文が1件以上ある
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id
    AND  o.status  = 'completed'  -- completedのみ対象
)
  AND NOT EXISTS (                -- 条件②: レビューが1件もない
  SELECT 1
  FROM   reviews r
  WHERE  r.user_id = u.user_id    -- reviewsに行が存在しないことを確認
)
ORDER BY u.user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users u                      → 1行ずつ処理
  2. EXISTS（completed注文）               → completed ありを判定
  3. AND NOT EXISTS（レビュー不在）            → レビューなしを判定
  4. SELECT u.user_id, u.name, u.plan  → 通過行を選択
  5. ORDER BY u.user_id                → user_id 昇順
  */

SELECT
  u.user_id,
  u.name,
  u.plan,
  s.order_count,
  s.total_amount,
  s.avg_amount
FROM   users u
INNER JOIN (                          -- 集計結果を派生テーブルとして INNER JOIN
  SELECT
    user_id,
    COUNT(*)           AS order_count, -- 注文件数
    SUM(amount)         AS total_amount,-- 合計金額
    ROUND(AVG(amount))  AS avg_amount   -- 平均金額（整数化）
  FROM   orders
  WHERE  status = 'completed'          -- 集計前に completed のみに絞る
  GROUP BY user_id
) AS s ON u.user_id = s.user_id      -- AS エイリアス必須。ON で結合キー指定
ORDER BY s.total_amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. 派生テーブル（s）を評価
  2. FROM users u                  → users 全5行を読み込む
  3. INNER JOIN s ON user_id       → user5はsに行がないため結合から自動除外
  4. SELECT u.*, s.*               → 必要列を選択
  5. ORDER BY s.total_amount DESC  → 合計金額降順
  */

SELECT
  product_id, name, category, price
FROM   products
WHERE  category = (              -- 最多売上カテゴリ名と一致する商品を取り出す
  SELECT p2.category            -- ② カテゴリ名を取得（スカラーSQ）
  FROM   products p2
  INNER JOIN order_items oi ON p2.product_id = oi.product_id
  INNER JOIN orders o       ON oi.order_id   = o.order_id
  WHERE  o.status = 'completed'  -- completed 注文のみ対象
  GROUP BY p2.category
  HAVING SUM(oi.qty) = (       -- カテゴリ別合計qty が最大値と一致
    SELECT MAX(cat_qty)          -- ① 最大の合計qty を先に確定（最内側SQ）
    FROM (
      SELECT SUM(oi2.qty) AS cat_qty -- カテゴリ別の合計qty
      FROM   products p3
      INNER JOIN order_items oi2 ON p3.product_id = oi2.product_id
      INNER JOIN orders o2      ON oi2.order_id  = o2.order_id
      WHERE  o2.status = 'completed'
      GROUP BY p3.category
    ) AS cat_totals              -- 派生テーブルに必ずエイリアスを付ける
  )
)
ORDER BY price DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. 最内側SQ（派生テーブル cat_totals）を評価
  2. 2段目SQ: MAX(cat_qty) = 6（最大値を確定）
  3. 中間SQ: HAVING SUM(oi.qty) = 6 のカテゴリを取得
  4. 外側WHERE: products.category = 'electronics' で絞り込む
  5. SELECT ...           → 必要列を選択
  6. ORDER BY price DESC  → 価格降順
  */