SQL データ整形・変換 — LPAD/CONCAT_WS・REGEXPの応用

SELECT
  user_id,
  plan,
  expires_at::DATE - DATE '2024-12-15'       AS days_remaining,      -- 教材の固定基準日との差
  CASE                                                                   -- CASE WHEN で3段階の有効期限ステータスを判定
    WHEN expires_at::DATE < DATE '2024-12-15'       THEN '期限切れ'
    WHEN (expires_at::DATE - DATE '2024-12-15') <= 30 THEN '期限間近'
    ELSE                                                    '有効'
  END                                              AS status,
  TO_CHAR(                                                               -- 次回更新日 = 現在の有効期限 + 1年 (INTERVAL で日付加算 → TO_CHAR で書式化)
    expires_at::DATE + INTERVAL '1 year',
    'YYYY-MM-DD'
  )                                                AS renewal_at
FROM   subscriptions
ORDER BY days_remaining;                                                 -- 残日数の昇順（期限切れが先頭）

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM subscriptions                    → 全4行を読み込む
  2. expires_at::DATE - DATE '2024-12-15'       → 残日数を計算
  3. CASE WHEN                             → 残日数で3段階に分類
  4. expires_at::DATE + INTERVAL '1 year'  → 1年後の日付を算出
  5. TO_CHAR(..., 'YYYY-MM-DD')            → 文字列にフォーマット
  6. ORDER BY days_remaining               → 残日数の昇順で出力
  */

-- CTE で割引率を先に計算（CASE WHEN 式の重複を防ぐ DRY 設計）
WITH ranked AS (
  SELECT
    order_id,
    customer_id,
    customer_rank,
    subtotal,
    CASE customer_rank                                          -- CASE 値 WHEN の等値比較形式（CASE WHEN rank = 'x' THEN ... と等価で簡潔）
      WHEN 'platinum' THEN 0.20                                 -- プラチナ: 20%割引
      WHEN 'gold'     THEN 0.10                                 -- ゴールド: 10%割引
      WHEN 'silver'   THEN 0.05                                 -- シルバー:  5%割引
      ELSE                 0.00                                 -- ブロンズ（その他）: 割引なし
    END                            AS discount_rate
  FROM   orders
)
SELECT
  order_id,
  customer_rank,
  subtotal,
  discount_rate,
  ROUND(subtotal * (1 - discount_rate))        AS discounted,   -- 割引後金額を整数に四捨五入（CTE で計算済みの discount_rate を参照）
  GREATEST(                                                     -- GREATEST で最低価格 500円を保証（price floor / 下限クランプ）
    ROUND(subtotal * (1 - discount_rate)),
    500
  )                                             AS final_price
FROM   ranked
ORDER BY order_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE (WITH ranked)
  2. メインクエリ
  */

SELECT
  category,
  COUNT(*)                                                          AS total_sales,
  COUNT(*) FILTER (WHERE status = 'completed')                   AS completed_count,       -- FILTER句で完了した取引のみカウント（GROUP BYのグループ行数は変えない）
  SUM(amount) FILTER (WHERE status = 'completed')               AS completed_amount,       -- FILTER句で完了した取引の金額のみ集計（キャンセル・返金は含まない）
  ROUND(                                                                                   -- 達成率: 100.0 を掛けてNUMERIC除算にしてから小数1桁に丸める
    100.0 * COUNT(*) FILTER (WHERE status = 'completed')
           / COUNT(*),
    1
  )                                                                   AS completion_rate,
  STRING_AGG(DISTINCT product_name, ', '                                                   -- DISTINCTで重複商品名を除いて辞書順に連結（ステータス問わず全商品を対象）
    ORDER BY product_name)                                          AS products
FROM   sales
GROUP BY category
ORDER BY category;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM sales                           → 全8行を読み込む
  2. GROUP BY category                    → 3グループに分類
  3. COUNT(*)                             → グループ全行数
  4. COUNT(*) FILTER (...)                → 完了行のみカウント
  5. SUM(amount) FILTER (...)             → 完了行の金額を合計
  6. ROUND(100.0 * completed / total, 1)  → 完了率を算出
  7. STRING_AGG(DISTINCT ...)             → 重複排除し辞書順で連結
  8. ORDER BY category                    → カテゴリ昇順で出力
  */

SELECT
  log_id,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[1]          AS log_level,  -- ログを3分解→[1]レベル [2]コード [3]パス
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[2]::INTEGER AS status_code,           -- 2番目のグループ（ステータスコード文字列）を INTEGER にキャスト
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[3]          AS endpoint,              -- 3番目のグループ（エンドポイントパス）
  REGEXP_REPLACE(log_date, '^(\d{4})(\d{2})(\d{2})$', '\1-\2-\3') AS formatted_date,  -- REGEXP_REPLACE でキャプチャグループを使って日付書式を変換／'^(\d{4})(\d{2})(\d{2})$' → \1-\2-\3 で 'YYYYMMDD' → 'YYYY-MM-DD'
  log_message ~ '^(ERROR|WARN)'                                  AS is_error  -- ~ は正規表現マッチ（行頭が ERROR/WARN か）
FROM   app_logs
ORDER BY log_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM app_logs        → 全5行を読み込む
  2. REGEXP_MATCHES(...)  → 正規表現で3グループを抽出
  3. REGEXP_REPLACE(...)  → 日付文字列を整形
  4. log_message の行頭判定    → ERROR/WARN を BOOLEAN で判定
  5. ORDER BY log_id      → 昇順で出力
  */