SQL データ整形・変換 — TO_CHAR・SPLIT_PARTの応用

SELECT
  id,
  SPLIT_PART(email, '@', 1)               AS email_user,      -- '@'で分割し1番目(ユーザー名)を取得
  SPLIT_PART(email, '@', 2)               AS email_domain,    -- '@'で分割し2番目(ドメイン)を取得
  REGEXP_REPLACE(phone, '-', '', 'g')    AS phone_normalized  -- '-'を全て空文字に置換('g'=全件)
FROM   contacts
ORDER BY id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM contacts                        → 全3行を読み込む
  2. SPLIT_PART(email, '@', 1)            → '@' で分割し1番目フィールド(左側)を返す
  3. REGEXP_REPLACE(phone, '-', '', 'g')  → '-' にマッチした全箇所を '' に置換
  4. ORDER BY id                          → id 昇順で出力
  */

SELECT
  product_id,
  name,
  price,
  CASE category                              -- 単純CASE：カテゴリで税率を決定
    WHEN 'food' THEN 0.08                    -- 食品は軽減税率8%
    ELSE            0.10                     -- それ以外は標準税率10%
  END                        AS tax_rate,
  ROUND(                                     -- 小数点以下を四捨五入して整数に
    price * (1 + CASE category              -- CASE WHENを計算式の中にインライン展開
      WHEN 'food' THEN 0.08
      ELSE 0.10
    END)
  )                          AS price_with_tax,
  CASE                                       -- 検索CASE：税抜き価格で帯域を判定
    WHEN price >= 3000 THEN 'premium'        -- 上位条件を先に書く（検索CASEの鉄則）
    WHEN price >= 1000 THEN 'standard'
    ELSE                    'budget'
  END                        AS price_rank
FROM   products
ORDER BY product_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM products             → 全5行を読み込む
  2. CASE category             → カテゴリで税率を決定
  3. price * (1 + 税率) / ROUND  → 税込金額を計算し四捨五入
  4. CASE WHEN price …         → 価格でランク付け
  5. ORDER BY product_id       → 昇順で出力
  */

SELECT
  project_id,
  COUNT(*)                                               AS task_count,
  SUM(CASE WHEN completed THEN 1 ELSE 0 END)           AS completed_count, -- 完了タスクだけ1を加算
  COALESCE(SUM(points), 0)                              AS total_points,    -- NULL行は無視、全行NULLなら0
  ROUND(
    SUM(CASE WHEN completed THEN 1 ELSE 0 END)::NUMERIC  -- INTEGER→NUMERIC変換で小数演算を確保
    / NULLIF(COUNT(*), 0) * 100                           -- COUNT=0のときNULLを返しゼロ除算防止
  )                                                       AS completion_rate
FROM   tasks
GROUP BY project_id
ORDER BY project_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM tasks                                     → 全6行を読み込む
  2. GROUP BY project_id
  3. COUNT(*)                                       → グループ内の全行数 (NULLも含む)
  4. SUM(CASE WHEN completed THEN 1 ELSE 0 END)     → true行のみ1を加算
  5. COALESCE(SUM(points), 0)                       → NULL行は SUM が自動で無視
  6. SUM(...)::NUMERIC / NULLIF(COUNT(*), 0) * 100  → 完了率を小数精度で計算
  7. ROUND(...)                                     → 小数点以下を四捨五入して整数
  8. ORDER BY project_id                            → project_id 昇順で出力
  */

SELECT
  'INV-' || LPAD(invoice_id::TEXT, 6, '0')  AS invoice_no,      -- ①整数をTEXTにキャスト ②6桁0埋め ③プレフィックス結合
  TO_CHAR(
    ROUND(amount * 1.1),                    -- 税込金額を四捨五入して整数化
    'FM999,999,999'                          -- FM=先頭スペース除去、カンマ区切り書式
  )                                          AS amount_with_tax,
  TO_CHAR(issued_at, 'YYYY年MM月DD日')       AS issued_at_jp,    -- 日付を日本語形式に変換
  COALESCE(
    TO_CHAR(paid_at, 'YYYY-MM-DD'),          -- paid_at が非NULLなら日付文字列に変換
    '未払い'                                   -- paid_at が NULL なら '未払い'
  )                                          AS paid_status,
  CASE status                               -- 単純CASE：英語ステータスを日本語ラベルに変換
    WHEN 'paid'    THEN '支払済'
    WHEN 'pending' THEN '支払待ち'
    WHEN 'overdue' THEN '延滞'
    ELSE               '不明'               -- 想定外の値に備えてELSEを必ず書く
  END                                        AS payment_label
FROM   invoices
ORDER BY invoice_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM invoices                           → 全4行を読み込む
  2. invoice_id::TEXT                        → 整数をTEXTにキャスト（LPAD引数の型合わせ）
  3. amount * 1.1                            → 税込金額を計算 (INTEGER×NUMERIC
  4. TO_CHAR(issued_at, 'YYYY年MM月DD日')       → DATE型を日本語文字列に変換
  5. TO_CHAR(paid_at, 'YYYY-MM-DD')          → paid_atが非NULLなら日付文字列に変換
  6. CASE status WHEN 'paid' THEN '支払済' ...  → ステータスコードを日本語に変換
  7. ORDER BY invoice_id                     → invoice_id 昇順で出力
  */