SQL バッチ処理 — ウィンドウ関数・UNION・UPSERTの基礎

SELECT
  oi.item_id,
  oi.order_id,
  p.name         AS product_name,
  COALESCE(c.name, '未分類') AS category_name, -- 未分類＝LEFT JOIN で外れた商品
  oi.qty,
  oi.price * oi.qty AS subtotal        -- 小計 = 単価 × 数量

FROM       order_items oi
INNER JOIN products   p             -- 商品は必須なので INNER JOIN
  ON oi.product_id = p.product_id
LEFT JOIN  categories c             -- カテゴリ無しの商品も残す（LEFT JOIN）
  ON p.category_id = c.category_id

ORDER BY oi.item_id;

/*
  実行順序（3テーブルJOINクエリ）:
  1. FROM order_items oi          → 行を読み込む
  2. INNER JOIN products p ON ... → 結合（一致行のみ）
  3. LEFT JOIN categories c ON ...→ 結合（左表を全行保持）
  4. SELECT                       → 列を評価（subtotal）
  5. ORDER BY oi.item_id          → 並び替えて出力
*/

SELECT
  'email'  AS notification_type,  -- 固定値で種別を付与
  user_id,
  subject  AS content,            -- 列名を content に統一
  sent_at
FROM  email_notifications

UNION ALL                         -- 縦に結合（重複除去なし＝高速）
SELECT
  'push'   AS notification_type,  -- 固定値で種別を付与
  user_id,
  message  AS content,            -- email と列名を揃える
  sent_at
FROM  push_notifications

ORDER BY sent_at DESC;  -- UNION 後の全体を新しい順に

/*
  実行順序（UNION ALLクエリ）:
  1. 上の SELECT email_notifications → 行を読み込む
  2. 下の SELECT push_notifications  → 行を読み込む
  3. UNION ALL                      → 和集合をとる
  4. ORDER BY sent_at DESC          → 並び替えて出力
*/

BEGIN;  -- トランザクション開始（COMMIT まで未確定）

-- ① orders テーブルに注文ヘッダを追加
INSERT INTO orders (order_id, user_id, total_amount, status)
VALUES (1001, 'U01', 1500, 'pending');

-- ② order_items テーブルに注文明細を追加
INSERT INTO order_items (order_id, product_id, qty, price)
VALUES (1001, 'P01', 3, 500);

-- ③ products の在庫を購入数量分だけ減らす
UPDATE products
SET   stock = stock - 3    -- 在庫を3減らす（相対更新）
WHERE product_id = 'P01';  -- WHERE 必須（無いと全行更新）

COMMIT;  -- 全操作成功 → 変更を永続確定（他セッションに反映）

-- COMMIT 後の状態を検証
SELECT order_id, user_id, total_amount, status FROM orders ORDER BY order_id;
SELECT order_id, product_id, qty, price FROM order_items ORDER BY order_id, product_id;
SELECT product_id, name, stock FROM products ORDER BY product_id;

/*
  実行順序（正常系）:
  1. BEGIN               → トランザクション開始
  2. INSERT orders       → 行を挿入（未確定）
  3. INSERT order_items  → 行を挿入（未確定）
  4. UPDATE products     → 行を更新（未確定）
  5. COMMIT              → 変更を永続確定
  6. SELECT × 3          → 確定後の3テーブルを検証
  */

-- U01 の同期: 既存行 → name と updated_at を更新（emailは変更しない）
INSERT INTO user_profiles (user_id, name, email, updated_at)
VALUES (
  'U01',
  '田中 太郎（改名）',
  'tanaka@example.com',
  NOW()                               -- 同期時刻を設定
)
ON CONFLICT (user_id)               -- user_id が重複した（既存行がある）場合の処理
DO UPDATE SET                       -- 競合したときにUPDATEを実行する
  name       = EXCLUDED.name,      -- EXCLUDED: 挿入しようとした新しい値を参照するキーワード
  updated_at = EXCLUDED.updated_at; -- email は SET に書かないことで元の値を保持する

-- U03 の同期: 新規行 → INSERT される（ON CONFLICT は発生しない）
INSERT INTO user_profiles (user_id, name, email, updated_at)
VALUES (
  'U03',
  '鈴木 一郎',
  'suzuki@example.com',
  NOW()
)
ON CONFLICT (user_id)               -- 同じUPSERT構文を書いても、競合なし → 通常のINSERT
DO UPDATE SET
  name       = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

/*
  実行順序（UPSERT / U01 のケース）:
  1. INSERT INTO user_profiles → 一意制約をチェック
  2. 一意制約違反を検出        → 既存行と競合
  3. ON CONFLICT DO UPDATE     → 既存行を上書き

  実行順序（UPSERT / U03 のケース）:
  1. INSERT INTO user_profiles → 一意制約をチェック
  2. 競合なし                  → 制約に抵触しない
  3. 通常のINSERT             → 行を挿入
*/