GLASSでは様々なお客様の広告、サイト、業務データなどを分析しております。分析にはBigQueryを利用することが多く、例えば、業務データをお客様のシステムからBigQueryに転送してSQLを駆使して様々な角度からデータを整形、分析、可視化しています。
今回、お客様が扱う業務データの中に再帰的な自己参照を行う階層テーブルが含まれていたので、BiqQueryにてこれを分析しやすい形に展開する必要がありました。そこで、本記事はBiqQueryで2023年から利用できるようになったWITH RECURSIVEを使ってデータを展開する方法をご紹介いたします。
階層テーブルとは
階層テーブルは名前の通り階層構造を持つデータを表現するのに利用されるテーブル構造です。階層構造を持つデータととしては例えば組織のデータ（事業所＞部＞課）や商品カテゴリ（生鮮食品＞野菜＞根菜＞人参）などがあります。
これを該当レコードに対する親階層のレコードのIDを使って表現すると以下のような形になります。
| カテゴリID | カテゴリ名 | 親カテゴリID |
| 10 | 生鮮食品 | null |
| 1010 | 野菜 | 10 |
| 1020 | 肉 | 10 |
| 1030 | 魚 | 10 |
| 101010 | 根菜 | 1010 |
| 10101010 | 人参 | 101010 |
| 10101020 | 大根 | 101010 |
| 102010 | 豚肉 | 1020 |
| 102020 | 牛肉 | 1020 |
| 102030 | 鶏肉 | 1020 |
これを以下のような商品テーブルと結合します。カテゴリIDは末端のカテゴリのみが格納されており、その親のカテゴリは、直接は格納されていません。
| 商品ID | 商品名 | カテゴリID |
| A1000 | 豚コマ | 102010 |
| B2000 | 朝採れ人参 | 10101010 |
| C3000 | 大根（カット） | 10101020 |
今回は例として各商品がどのカテゴリに属しているか、展開するような形で以下のように結合することにします。
| 商品ID | 商品名 | カテゴリID | カテゴリ名 |
| A1000 | 豚コマ | 102010 | 豚肉 |
| A1000 | 豚コマ | 1020 | 肉 |
| A1000 | 豚コマ | 10 | 生鮮食品 |
| B2000 | 朝採れ人参 | 10101010 | 人参 |
| B2000 | 朝採れ人参 | 101010 | 根菜 |
| B2000 | 朝採れ人参 | 1010 | 野菜 |
| B2000 | 朝採れ人参 | 10 | 生鮮食品 |
| C3000 | 大根（カット） | 10101020 | 大根 |
| C3000 | 大根（カット） | 101010 | 根菜 |
| C3000 | 大根（カット） | 1010 | 野菜 |
| C3000 | 大根（カット） | 10 | 生鮮食品 |
これを実現するためのSQLはWITH RECURSIVEを使う場合と使わない場合で以下のように書けます。
階層テーブルの結合（WITH RECURESIVEを使わない場合）
-- 最初のカテゴリ (自分自身)
SELECT
item.item_id,
item.item_name,
category1.category_id AS category_id,
category1.category_name AS category_name,
FROM `<project_id>.<dataset>.item` AS item
JOIN `<project_id>.<dataset>.category` AS category1 ON item.category_id = category1.category_id
UNION ALL
-- 2階層目のカテゴリ (親カテゴリ)
SELECT
item.item_id,
item.item_name,
category2.category_id AS category_id,
category2.category_name AS category_name,
'レベル2' AS category_level
FROM `<project_id>.<dataset>.item` AS item
JOIN `<project_id>.<dataset>.category` AS category1 ON item.category_id = category1.category_id
JOIN `<project_id>.<dataset>.category` AS category2 ON category1.parent_category_id = category2.category_id
UNION ALL
-- 3階層目のカテゴリ (親の親カテゴリ)
SELECT
item.item_id,
item.item_name,
category3.category_id AS category_id,
category3.category_name AS category_name,
FROM `<project_id>.<dataset>.item` AS item
JOIN `<project_id>.<dataset>.category` AS category1 ON item.category_id = category1.category_id
JOIN `<project_id>.<dataset>.category` AS category2 ON category1.parent_category_id = category2.category_id
JOIN `<project_id>.<dataset>.category` AS category3 ON category2.parent_category_id = category3.category_id
UNION ALL
-- 4階層目のカテゴリ (親の親の親カテゴリ)
SELECT
item.item_id,
item.item_name,
category4.category_id AS category_id,
category4.category_name AS category_name,
FROM `<project_id>.<dataset>.item` AS item
JOIN `<project_id>.<dataset>.category` AS category1 ON item.category_id = category1.category_id
JOIN `<project_id>.<dataset>.category` AS category2 ON category1.parent_category_id = category2.category_id
JOIN `<project_id>.<dataset>.category` AS category3 ON category2.parent_category_id = category3.category_id
JOIN `<project_id>.<dataset>.category` AS category4 ON category3.parent_category_id = category4.category_idWITH RECURSIVEを使わない場合は1階層ごとにJOINする必要があります。上記は4階層の場合ですが、5階層以上のデータの場合はさらに記述を追加する必要があります。
つまり、WITH RECURSIVEを使わないこの記述方法は、あらかじめ階層数が分かっている場合にしか利用できません。
結果は以下のようになります。
階層テーブルの結合（WITH RECURSIVEを使う場合）
WITH RECURSIVEを使う場合は以下の通りです。
WITH RECURSIVE category_hierarchy AS (
-- 最初のレベルのカテゴリ
SELECT
item.item_id,
item.item_name,
category.category_id,
category.category_name,
category.parent_category_id
FROM `<project_id>.<dataset>.item` AS item
JOIN `<project_id>.<dataset>.category` AS category ON item.category_id = category.category_id
UNION ALL
-- 再帰的に親カテゴリをたどる
SELECT
ch.item_id,
ch.item_name,
parent.category_id,
parent.category_name,
parent.parent_category_id
FROM category_hierarchy ch --★１
JOIN `<project_id>.<dataset>.category` AS parent ON ch.parent_category_id = parent.category_id
)
-- 結果を表示
SELECT item_id, item_name, category_id, category_name FROM category_hierarchyWITH RECURSIVEを使う場合はコンパクトですね。こちらの場合は階層数があらかじめわかっていなくても対応が可能です。
ポイントはFROM句にWITH RECURSIVEで指定したCTEの名前指定することです（★１）。これにより再帰的な呼び出しを実現しています。プログラムで言えば「最初のレベルのカテゴリ」が末端の処理本体で、「再帰的に親カテゴリをたどる」の部分が再帰呼び出しだと思ってもらえると理解しやすいかと思います。
ただし、階層数が何段でも対応できる便利さの反面、データの誤りで階層構造がループしてしまい、無限の呼び出しが発生するケースが考えられます。そのため、反復回数には500回の上限が設けられています。
BigQuery の反復処理の上限は 500 回です。再帰 CTE が反復処理の最大数に達すると、CTE の実行は中止され、エラーになります。（https://cloud.google.com/bigquery/docs/recursive-ctes?hl=ja）
結果はWITH RECURSIVEを使わない場合と同様、以下のようになります。
まとめ
今回はWITH RECURSIVEを使って再帰的な階層テーブルを展開する方法を紹介しました。
データ分析を行っていると様々なデータを取り扱うことになると思います。分析対象に階層テーブルが現れたときに今回の記事が参考になれば幸いです。
GLASSでは広告、サイト、業務データなどの様々なデータを可視化、分析してお客様のビジネスをサポートしています。ご興味があれば以下から気軽にお問い合わせください。
株式会社GLASS CTO。2010年、株式会社日立製作所にてIT新技術の調査・検証、Project Management Office、システム開発、マーケティング業務に携わる。2017年、株式会社Webios 取締役副社長 / CTO に就任。2018年、GLASS開発チームに参画。
AWS,GCPでのクラウドシステム開発、Ruby,PHPでのスクラッチ開発、CMS,ECパッケージでのサイト構築（WordPress / WooCommerce, MovableType, EC-CUBEなど）、kintoneでのシステム開発などに携わっている。
