BigQueryの再帰CTEを活用できた

こんにちは。今回はBigQueryで実際に活用した再帰CTEについて紹介します。

この記事は次のユーザーを対象としています。

• SQLの基本知識がある
• 再帰の基本概念を理解している

再帰CTE（Recursive CTE）とは

再帰CTE（Common Table Expression：CTEを使用した再帰クエリ）とは、 自身を繰り返し参照しながらデータを取得するSQLの手法です。 通常、階層構造のデータ（組織ツリー、カテゴリ構造、経路探索など）を扱う際に用いられます。

再帰CTEをBigQueryで作成する方法

再帰CTEの基本構造は以下のようになります。

WITH RECURSIVE 再帰CTE名 AS (
	-- アンカー部（最初の結果を取得）
	初期SELECT文
	UNION ALL
	-- 再帰部（自己結合によって次のレベルを取得）
	再帰SELECT文（CTEを自己参照）
)
SELECT * FROM 再帰CTE名;

例

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 5
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC

再帰CTE名: CTE_1

初期SELECT文: SELECT 1 AS iteration

再帰部: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 5

結果

/*-----------*
 | iteration |
 +-----------+
 | 1         |
 | 2         |
 | 3         |
 | 4         |
 | 5         |
 *-----------*/

ユースケース

現状

ECシステムのデータベースには受注情報を格納する受注テーブル (order) があり、この中に受注番号カラム (order_num) が存在します。

顧客が注文した後、何らかの理由で受注番号が変更されるケースがあります。

その追跡のために新しいレコードが追加され、order_numには変更後の値が設定され、変更前の受注番号はprevious_order_numカラムに記録されます。

以下は受注テーブルの例です。（※ 今回は必要なカラムのみに絞り、関連しないカラムは省略しています。）

order_num	member_no(会員）	previous_order_num
A	10000	NULL
B	20000	NULL
C	20000	B
D	30000	NULL
E	30000	D
F	30000	F
G	40000	NULL
H	40000	G
I	40000	H
K	40000	I

• 会員10000の受注は、受注番号の変更はなしです。
• 会員20000の受注には受注番号の変更が1回ありました。元：B → 1回変更： C
• 会員30000の受注には受注番号の変更が2回ありました。元：D → 1回変更： E → 2回変更： F
• 会員40000の受注には受注番号の変更が3回ありました。元：G → 1回変更： H → 2回変更： I → ３回変更： K

ゴール

分析に向けて受注番号が2回以上変更された受注とその履歴を抽出したいです。

結果は以下のイメージになります。

order_num	order_num_history	order_num_update_times
F	D → E → F	2
K	G → H → I → K	3

実装方法

再帰CTEを利用して、order_numとprevious_order_numを条件として繰り返し紐付けることで、変更履歴を追跡するチェーン状の構造を作成しながら、受注番号の変更回数を計算します。

以下は実装クエリになります。

WITH RECURSIVE order_history AS (
  -- アンカー部（最初の結果を取得）
  SELECT
    order_num,
    previous_order_num,
    0 AS order_num_update_times,
    order_num AS order_num_history,
    [order_num] AS order_num_history_list,
  FROM
    order
  WHERE
    previous_order_num IS NULL -- 元の受注番号のレコード

  UNION ALL
  -- 再帰部: 
  SELECT
    o.order_num,
    o.previous_order_num,
    h.order_num_update_times + 1 AS order_num_update_times,
    CONCAT(h.order_num_history, '->',　o.order_num) AS order_num_history,
    ARRAY_CONCAT(h.order_num_history_list, [o.order_num]) AS order_num_history_list
  FROM
    order o
  INNER JOIN
    order_history h
  ON
    o.previous_order_num = h.order_num
  WHERE
    NOT o.node IN UNNEST(h.visited_nodes) -- 終了条件
)

SELECT order_num, order_num_history, order_num_update_times
FROM order_history
WHERE order_num_update_times > 2

再帰分の説明

上記のorder_historyのCTEは再帰的に実装されるため、各再帰ステップの結果を確認しましょう。

• 初期

order_num	previous_order_num	order_num_update_times	order_num_history	order_num_history_list
A	NULL	0	A	[A]
B	NULL	0	B	[B]
D	NULL	0	D	[D]
G	NULL	0	G	[G]

• 1回目（1回目の再帰）

order_num	previous_order_num	order_num_update_times	order_num_history	order_num_history_list
A	NULL	0	A	[A]
B	NULL	0	B	[B]
D	NULL	0	D	[D]
G	NULL	0	G	[G]
C	B	1	B → C	[B, C]
E	D	1	D → E	[D, E]
H	G	1	G → H	[G, H]

• 2回目（2回目の再帰）

order_num	previous_order_num	order_num_update_times	order_num_history	order_num_history_list
A	NULL	0	A	[A]
B	NULL	0	B	[B]
D	NULL	0	D	[D]
G	NULL	0	G	[G]
C	B	1	B → C	[B, C]
E	D	1	D → E	[D, E]
H	G	1	G → H	[G, H]
F	E	2	D → E → F	[D,E,F]
I	H	2	G → H → I	[G,H,I]

• 3回目（3回目の再帰）

order_num	previous_order_num	order_num_update_times	order_num_history	order_num_history_list
A	NULL	0	A	[A]
B	NULL	0	B	[B]
D	NULL	0	D	[D]
G	NULL	0	G	[G]
C	B	1	B → C	[B, C]
E	D	1	D → E	[D, E]
H	G	1	G → H	[G, H]
F	E	2	D → E → F	[D,E,F]
I	H	2	G → H → I	[G,H,I]
K	I	3	G → H → I → K	[G,H,I,K]

最後結果

order_num	order_num_history	order_num_update_times
F	D → E → F	2
K	G → H → I → K	3

注意点

無限再帰に注意！

• 再帰 CTE では、終了条件を満たさないまま再帰的表現が継続的に実行されると、無限再帰が発生する可能性があります。
• 無限再帰を終了するために、再帰 CTE ごとに反復処理の上限が適用されます。BigQuery の反復処理の上限は 500 回です。（再帰 CTE が反復処理の最大数に達すると、CTE の実行は中止され、エラーになります。）→ 無限再帰を防ぐために、終了条件（WHERE区）を必ず確認してください。
• BigQuery の反復処理の上限を変更したい場合は、カスタマーケアに連絡することで変更できる場合があります。（ただし懸念事項として、クエリ実行時間が長くなるため、上限をつけることを推奨します。）

まとめ

今回はBigQueryの再帰CTEを使って、受注番号の変更履歴を追跡する方法についてまとめてみました。

再帰CTEは階層データを扱う際に非常に便利ですが、無限再帰に注意して終了条件をしっかり設定することが重要です。ぜひ実際のデータで試してみてください！