【Snowflake】再帰SQLを使って単方向リストをさかのぼる！

分析業務をしていると、単方向リストのデータを扱うことがあります。

単方向リストとは、各要素が次または前の要素の情報を保持しており先頭から末尾まで数珠繋ぎに要素が並んでいるリストのことです。

実際のデータで説明すると、A、B、Cという各要素が、A⇒B⇒Cと数珠つなぎに繋がっているリストのことで、テーブルで表すと以下のようなデータとなります。

ID	前のID
A
B	A
C	B

「ID」が各要素を表し、「前のID」がIDと他のIDの関係性を表します。

分析・集計業務で単方向リストを扱う場合、末尾レコードに先頭レコードのIDを紐付ける作業を行うケースがあります。

上記データ例で説明すると、Cというレコードに対して、起点となるレコードがAという情報を付与する作業になります。

今回の記事は、再帰ＳＱＬを使い、単方向リストをさかのぼり、先頭レコードを取得する方法を説明します。

課題とサンプルデータ
再帰SQL (with RECURSIVE句）を使った単方向リストのさかのぼり方法
再帰SQLの実行結果について
まとめ

課題とサンプルデータ

今回の記事では、単方向リストを含んだデータに対して、単方向リストの先頭レコードが持つIDを紐付けることを課題とします。

サンプルデータとしては、IDと前のID（IT_A)を保持した以下のデータを利用します。

ID	ID_A
111
112
113
114
115	114
116
117	116
118	117

上記サンプルデータは、単方向リストとして、114⇒115、116⇒117⇒118の2パターンを含んでいます。

Snowfakeでサンプルデータを作成するSQLは次の通りとなります。

create or replace temporary table test as

select '111' as id , null as id_a
union all
select '112' as id , null as id_a
union all
select '113' as id , null as id_a
union all
select '114' as id , null as id_a
union all
select '115' as id , '114' as id_a
union all
select '116' as id , null as id_a
union all
select '117' as id , '116' as id_a
union all
select '118' as id , '117' as id_a;

再帰SQL (with RECURSIVE句）を使った単方向リストのさかのぼり方法

単方向リストをさかのぼるには、再帰SQLを使用します。再帰SQL作成のポイントは以下となります。

①再起用サブクエリの定義
②非再帰クエリ（初回実行クエリ）の記述
③再帰クエリの記述
④再起用サブクエリの呼び出し

以下今回使用するサンプルSQLを示します。

// ①再帰用のサブクエリを定義
with RECURSIVE my_cte (id , id_a,id_moto,LV) as (

    // ②非再帰クエリの記述
    select a.id,a.id_a,a.id,1 from test as a where id_a is null
    union all
    // ③再帰クエリの記述
    select b.id,b.id_a,a.id_moto,a.LV+1 from my_cte as a,test  as b
    where a.id = b.id_a
)
// ④再帰用サブクエリの呼び出し
select * from my_cte;

各ポイントの詳細を以下で説明します。