SQL バッチ処理 — DISTINCT・NULL処理・LIKEの基礎

-- ① NULLを安全なデフォルト値に置き換えて全ユーザーを取得
SELECT
  user_id,
  COALESCE(display_name, name) AS shown_name,  -- NULL なら name を使う
  COALESCE(phone, '未登録')    AS phone_display  -- NULL なら '未登録'
FROM  users
ORDER BY user_id;

-- ② phone が NULL のユーザー数を集計
SELECT
  COUNT(*) AS null_phone_count  -- IS NULL で絞った後の件数
FROM  users
WHERE phone IS NULL;            -- NULL は IS NULL で判定（= NULL は不可）

/*
  実行順序（① NULLデフォルト変換クエリ）:
  1. FROM users          → 行を読み込む
  2. SELECT              → 列を評価（COALESCE で NULL を補正）
  3. ORDER BY user_id    → 並び替えて出力

  実行順序（② NULL件数カウントクエリ）:
  1. FROM users          → 行を読み込む
  2. WHERE phone IS NULL → 行を絞り込む
  3. SELECT null_phone_count → 集計関数を評価
*/

-- ① name に 'コーヒー' を含む商品を price 昇順で取得
SELECT
  product_id,
  name,
  sku,
  price
FROM   products
WHERE  name ILIKE '%コーヒー%'  -- 大小文字を区別しない部分一致（PostgreSQL）
ORDER BY price ASC;

-- ② sku が 'BEV-' で始まる商品数をカウント
SELECT
  COUNT(*) AS bev_count
FROM   products
WHERE  sku LIKE 'BEV-%';  -- 前方一致（'BEV-' で始まる）

/*
  実行順序（① ILIKE部分一致クエリ）:
  1. FROM products            → 行を読み込む
  2. WHERE name ILIKE ...     → 行を絞り込む
  3. SELECT                   → 列を評価
  4. ORDER BY price ASC       → 並び替えて出力

  実行順序（② LIKE前方一致カウントクエリ）:
  1. FROM products            → 行を読み込む
  2. WHERE sku LIKE 'BEV-%'   → 行を絞り込む
  3. SELECT bev_count         → 集計関数を評価
*/

-- ① status が completed または pending の注文を取得（IN を使用）
SELECT
  order_id,
  user_id,
  amount,
  status
FROM   orders
WHERE  status IN ('completed', 'pending')  -- いずれかに一致（OR を IN で簡潔に）
ORDER BY order_id;

-- ② purchases テーブルに存在しない注文（NOT EXISTS を使用）
SELECT
  o.order_id,
  o.user_id,
  o.amount,
  o.status
FROM   orders o
WHERE  NOT EXISTS (                  -- サブクエリが0件＝存在しない注文
  SELECT 1                           -- 存在確認なので値は何でもよい
  FROM   purchases p
  WHERE  p.purchase_id = o.order_id  -- 外側と相関（相関サブクエリ）
)
ORDER BY o.order_id;

/*
  実行順序（① IN クエリ）:
  1. FROM orders          → 行を読み込む
  2. WHERE status IN (...) → 行を絞り込む
  3. SELECT               → 列を評価
  4. ORDER BY order_id    → 並び替えて出力

  実行順序（② NOT EXISTS クエリ）:
  1. FROM orders o        → 行を読み込む
  2. WHERE NOT EXISTS     → サブクエリを評価して行を絞り込む
  3. SELECT               → 列を評価
  4. ORDER BY o.order_id  → 並び替えて出力
*/

-- ① 月ごとの注文件数と合計金額を集計（新しい月順）
SELECT
  TO_CHAR(DATE_TRUNC('month', ordered_at), 'YYYY-MM') AS month,  -- 月初に切り捨て→'YYYY-MM' 文字列に
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM     orders
GROUP BY DATE_TRUNC('month', ordered_at)   -- 月単位でグループ化
ORDER BY DATE_TRUNC('month', ordered_at) DESC;  -- 新しい月順

-- ② 過去30日以内の注文のみ取得
SELECT
  order_id,
  user_id,
  amount,
  ordered_at
FROM   orders
WHERE  ordered_at >= DATE '2024-06-03' - INTERVAL '30 days'  -- 直近30日
ORDER BY ordered_at DESC;

/*
  実行順序（① 月次集計クエリ）:
  1. FROM orders            → 行を読み込む
  2. GROUP BY DATE_TRUNC(月) → グループ化
  3. SELECT                 → 集計関数を評価（COUNT, SUM）
  4. ORDER BY ... DESC      → 並び替えて出力

  実行順序（② 過去30日クエリ）:
  1. FROM orders            → 行を読み込む
  2. WHERE ordered_at >= ...→ 行を絞り込む
  3. SELECT                 → 列を評価
  4. ORDER BY ordered_at DESC → 並び替えて出力
*/