SQL バッチ処理 — UPDATE・論理削除・EXISTSの基礎

-- ① 論理削除: comment_id=3 の deleted_at に現在時刻を記録
UPDATE comments
SET    deleted_at = NOW()   -- 削除日時をセット＝論理削除（DELETE しない）
WHERE  comment_id = 3
  AND  deleted_at IS NULL;  -- 未削除の行だけ

-- ② 有効コメント一覧取得: deleted_at が NULL の行のみ返す
SELECT
  comment_id,
  post_id,
  body,
  deleted_at
FROM   comments
WHERE  deleted_at IS NULL  -- 未削除（IS NULL で判定）
ORDER BY comment_id ASC;

/*
  実行順序（SQLの論理的な評価順 / ②の一覧取得）:
  1. FROM comments             → 行を読み込む
  2. WHERE deleted_at IS NULL  → 行を絞り込む
  3. SELECT                    → 列を評価
  4. ORDER BY comment_id       → 並び替えて出力
*/

WITH
  recent_orders AS (               -- CTE①: 過去30日の注文をユーザー別に集計
    SELECT
      user_id,
      COUNT(*) AS order_count
    FROM   orders
    WHERE  ordered_at >= DATE '2024-06-01' - INTERVAL '30 days'  -- 問題文の基準日から直近30日
    GROUP BY user_id
  ),
  vip_users AS (                   -- CTE②: 2件以上のユーザーに絞り込み
    SELECT user_id, order_count
    FROM   recent_orders            -- CTE① を参照
    WHERE  order_count >= 2
  )
SELECT
  v.user_id,
  u.name,
  v.order_count
FROM       vip_users v
INNER JOIN users    u              -- VIP に氏名を結合
  ON  v.user_id = u.user_id
ORDER BY v.order_count DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE① recent_orders       → CTE を定義
  2. CTE② vip_users           → CTE を定義
  3. FROM vip_users v          → 行を読み込む
  4. INNER JOIN users u        → 結合（一致行のみ）
  5. SELECT                    → 列を評価
  6. ORDER BY order_count DESC → 並び替えて出力
*/

SELECT
  u.user_id,
  u.name
FROM  users u
WHERE NOT EXISTS (              -- 注文が1件も無いユーザーだけ
  SELECT 1                      -- 存在判定なので値は何でもよい
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- 外側と相関（注文の有無を判定）
)
ORDER BY u.user_id ASC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users u             → 行を読み込む
  2. NOT EXISTS (サブクエリ)  → サブクエリを先に評価
  3. SELECT                   → 列を評価
  4. ORDER BY u.user_id       → 並び替えて出力
*/

SELECT
  order_id,
  amount,
  CASE                              -- 金額で購買ランクを分岐
    WHEN amount >= 10000 THEN 'premium'
    WHEN amount >=  5000 THEN 'standard'  -- 上から順に評価（最初の一致を採用）
    ELSE                       'basic'
  END AS rank_label,
  CASE status                      -- 列の値で分岐（単純 CASE）
    WHEN 'completed' THEN '完了'
    WHEN 'pending'   THEN '処理中'
    WHEN 'cancelled' THEN 'キャンセル'
    ELSE '不明'                      -- 想定外の値への備え
  END AS status_label

FROM   orders
ORDER BY ordered_at DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders              → 行を読み込む
  2. SELECT                   → 列を評価（rank_label, status_label）
  3. ORDER BY ordered_at DESC      → 並び替えて出力
*/