SQL 再帰CTE — 数列生成・BOM展開・循環検出の基礎

WITH RECURSIVE bom_tree AS (

  -- ① アンカー部: 完成品A（part_id=1）を qty=1, depth=0 で起点に設定
  SELECT
    p.part_id,
    p.part_name,
    p.unit_cost,
    1                     AS qty,    -- 完成品は1個からスタート
    0                     AS depth,
    p.part_name           AS path
  FROM  parts p
  WHERE p.part_id = 1

  UNION ALL

  -- ② 再帰部: BOM テーブルで子部品を取得し数量を掛け算で累積
  SELECT
    p.part_id,
    p.part_name,
    p.unit_cost,
    bt.qty * b.qty        AS qty,   -- 累積数量 = 親qty × BOM qty
    bt.depth + 1,
    bt.path || ' > ' || p.part_name
  FROM  bom b
  JOIN  bom_tree bt ON b.parent_id = bt.part_id  -- 親のpart_idとBOMのparent_idを結合
  JOIN  parts p    ON b.child_id  = p.part_id   -- 子部品の詳細をpartsから取得

)
SELECT
  depth,
  path,
  part_name,
  qty,
  unit_cost,
  qty * unit_cost         AS line_cost  -- 累積数量 × 単価 = 行合計コスト
FROM  bom_tree
WHERE part_id <> 1                      -- 完成品自身（unit_cost=0）を除外
ORDER BY depth, part_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1回目
  3. 再帰2回目
  4. 再帰3回目
  5. 外側クエリ
  */

WITH RECURSIVE date_series AS (

  -- ① アンカー部: 開始日 2024-01-01 を設定（テーブル参照なし）
  SELECT DATE '2024-01-01' AS d

  UNION ALL

  -- ② 再帰部: 1日ずつ加算（PostgreSQL の date 型にキャスト）
  -- MySQL: SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d < '2024-01-07'
  SELECT (d + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  d < DATE '2024-01-07'    -- 終了日に達したら停止

)
SELECT
  ds.d                                     AS sale_date,
  COALESCE(s.amount, 0)                   AS amount,  -- NULL → 0 に補完
  CASE WHEN s.sale_date IS NULL
       THEN '欠損' ELSE '有'
  END                                      AS status
FROM  date_series ds
LEFT JOIN sales_daily s ON ds.d = s.sale_date  -- 売上なし日はNULLで結合
ORDER BY ds.d;

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1〜5回目
  3. 再帰6回目
  4. 再帰7回目試行
  5. 外側クエリ
  */

WITH RECURSIVE paths AS (

  -- ① アンカー部: ノード'A'から出るエッジを直接取得（hops=1）
  SELECT
    from_node,
    to_node,
    1                            AS hops,
    from_node || '->' || to_node AS path
  FROM  edges
  WHERE from_node = 'A'

  UNION ALL

  -- ② 再帰部: 現在の to_node から出るエッジを追加して1ホップ延長
  SELECT
    p.from_node,
    e.to_node,
    p.hops + 1,
    p.path || '->' || e.to_node
  FROM  edges e
  JOIN  paths p ON e.from_node = p.to_node   -- 現在の末端ノードから次へ
  WHERE p.hops + 1 <= 5                    -- ループ防止の深さガード
    AND  p.path NOT LIKE '%' || e.to_node || '%'  -- 既訪問ノードへの再訪を防止

)
, ranked AS (
  SELECT
    to_node,
    hops,
    path,
    ROW_NUMBER() OVER (
      PARTITION BY to_node
      ORDER BY hops, path
    ) AS rn  -- 最短ホップ、同率なら辞書順最小を1位にする
  FROM  paths
)
SELECT
  to_node,
  hops,
  path
FROM  ranked
WHERE rn = 1
ORDER BY hops, to_node;

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1回目
  3. 再帰2回目
  4. 外側クエリ
  */

WITH RECURSIVE tree AS (

  -- ① アンカー部: ルートノード（parent_id IS NULL）を起点に設定
  SELECT
    node_id,
    name,
    parent_id,
    0                                   AS depth,
    ',' || node_id || ','              AS path,      -- カンマ区切りでIDを記録
    FALSE                               AS is_cycle  -- アンカーは循環なし
  FROM  nodes
  WHERE parent_id IS NULL

  UNION ALL

  -- ② 再帰部: 子ノードを取得し、path に既に含まれていれば is_cycle=TRUE
  SELECT
    n.node_id,
    n.name,
    n.parent_id,
    t.depth + 1,
    t.path || n.node_id || ','         AS path,
    t.path LIKE '%,' || n.node_id || ',%' AS is_cycle  -- path に ',node_id,' が含まれていれば循環を検出
  FROM  nodes n
  JOIN  tree t ON n.parent_id = t.node_id
  WHERE t.depth + 1 <= 10                              -- 深さガード（循環でも最大10階層）
    AND  NOT t.is_cycle                                -- 既に循環検出済みの行は展開しない

)
SELECT
  node_id,
  name,
  parent_id,
  depth,
  path,
  is_cycle
FROM  tree
ORDER BY node_id, depth;

/*
  実行順序（SQLの論理的な評価順）:
  1. アンカー部
  2. 再帰1回目
  3. 再帰2回目
  4. 再帰3回目
  5. 循環の検出
  6. 外側クエリ
  */