先日、Frosty Friday Live Challenge@SnowVillageに初参加をさせていただきました！
初めての経験で戸惑うこともありましたが、収録はとても楽しく、早速2回目の申し込みを行い、第36回に参加することになりました！
2回目の参加となった、第36回のホスト役は、tomoさん、がくさん、あれさん、ゲストは、たきこみさんと私の5人で行い、勝手知ったる皆さんとワイワイ楽しく収録をさせていただきました！
そんな収録を元にした配信内容はこちらから！
さて、この記事ではFrosty Friday Live Challengeの紹介と私が担当したFrosty-Friday Week_073の回答解説をお伝えしようと思います。
先に配信を観ても良いですし、こちらの記事をそのまま読み進んでいただいても構いません！
Frosty Friday Live Challenge @SnowVillageとは
すでに皆さんもご存じだと思いますが、番組の紹介もさせていただきます！
Frosty Friday Live Challengeは、Snowflakeユーザーコミュニティ「SnowVillage」が運営する、エンジニア向けのスキルアップ配信チャレンジ番組です！
Frosty Fridayが提供する、Snowflakeの実践的な課題に隔週で挑戦し、SQLやPython、Snowpark、Streamlitなどの最新機能を駆使した課題解決や、Snowflakeの最新機能やコミュニティ内外での新しい事例や記事などの紹介を行う、皆さんのSnowflake活用をレベルアップしてくれる超お役立ちチャンネルです！
Frosty-Friday Week_073の解説
話変わって、ここからは私がこの週録で担当したFrosty Fridayチャレンジは、WEEK_073の 階層データの可視化チャレンジ: 組織構造を整理せよ というテーマとその回答の解説を行っていきたいと思います！
このセッションでは、FakeCompany Inc.という架空の会社で働くDBAs（データベース管理者）たちが、社内の部門構造を把握できなくなってしまったという状況から始まります。部門テーブルはあるものの、「どの部門がどの部門の下にあるのか」「各部門は全体の中でどのレベルにいるのか」といった階層関係が一目では分からない状態になっているのです。
「部門構造が把握できなくなってしまった！」というのはだいぶ笑えない状況なのですが、
「データはあるけれど、関係性が分かりにくい」という状態だと解釈しました。
このFakeCompany Inc.の部門テーブルから参照用の一覧表を作成することが求められています。ただし、この部門テーブルは特殊な構造を持っています
・各部門は自分のdepartment_IDを持つ
・各部門は親部門のhead_department_IDを持つ
・最上位部門（Research & Development）は親部門がないため、head_department_IDがNULL
つまり、同じテーブル内のレコード同士が親子関係を持つ「自己参照テーブル」と呼ばれる構造です。
組織マスタのような年度ごとに階層が変わる情報ではよく見るデータ構造ですね。
多分、前回洗い替えを担当した社員が辞めちゃったのかもしれない。。大丈夫か、FakeCompany Inc.
とりあえず、なんとか助けてあげるのが私のミッションなので、テーブルを確認しつつ、階層構造を分かるようにしていきたいと思います！
自己参照テーブルにおける階層構造の表示
アウトプットイメージ
初期セットアップ
CREATE OR REPLACE table departments (department_name varchar, department_ID int, head_department_ID int);
INSERT INTO departments (department_name, department_ID, head_department_ID) VALUES
('Research & Development', 1, NULL), -- The Research & Development department is the top level.
('Product Development', 11, 1),
('Software Design', 111, 11),
('Product Testing', 112, 11),
('Human Resources', 2, 1),
('Recruitment', 21, 2),
('Employee Relations', 22, 2);
構造を見ると、department_IDに対する、head_department_IDが親部門コードとなっており、最上位の「Research & Development」部門は親部門コードがnullとなっていることが分かります。
階層データ処理の実行
階層パスの生成（標準SQLアプローチ）
先ほどの構造を元にSQLで再現すると、CTE（Common Table Expression）による解決が良いように思いました。
階層データを扱う際、CTEには以下のメリットがあります。
- ポータビリティ: PostgreSQL、SQL Server、MySQL、Snowflakeなど幅広いDBMSで動作
- 可読性: 複雑な階層処理を段階的に記述でき、コードが理解しやすい
- 標準準拠: ベンダーロックインを避けられる
WITH RECURSIVE dept_hierarchy AS (
-- ベースケース: 最上位部門を取得
SELECT
department_ID,
department_name,
head_department_ID,
department_name AS full_path,
1 AS level
FROM departments
WHERE head_department_ID IS NULL
UNION ALL
-- 展開ケース: 親部門から子部門を探す
SELECT
d.department_ID,
d.department_name,
d.head_department_ID,
dh.full_path || ' → ' || d.department_name AS full_path,
dh.level + 1 AS level
FROM departments AS d
JOIN dept_hierarchy AS dh
ON d.head_department_ID = dh.department_ID
)
SELECT * FROM dept_hierarchy ORDER BY level, department_ID;
出力結果
| CONNECTION_TREE | DEPARTMENT_ID | HEAD_DEPARTMENT_ID | DEPARTMENT_NAME |
|---|---|---|---|
| → Research & Development | 1 | Research & Development | |
| → Research & Development → Human Resources | 2 | 1 | Human Resources |
| → Research & Development → Product Development | 11 | 1 | Product Development |
| → Research & Development → Human Resources → Recruitment | 21 | 2 | Recruitment |
| → Research & Development → Human Resources → Employee Relations | 22 | 2 | Employee Relations |
| → Research & Development → Product Development → Software Design | 111 | 11 | Software Design |
| → Research & Development → Product Development → Product Testing | 112 | 11 | Product Testing |
出力イメージと同じ結果で出力でき、問題は解決できました！
改善案の検討
ただ、このクエリを見ていて、「構文長いな～」と思いました。
この方法は標準SQLで実装できる、実績のあるアプローチですが、もっと良いやり方があったようなと思い、自分の記憶を辿っているとあるSQL構文を思い出しました。
それはOracle固有の階層クエリ機能にある、CONNECT BY句です。
Oracle互換関数のSnowflake対応
実は、このOracle独自の階層関数ですが、2019年頃よりSnowflakeが対応しています！（サポートに一般提供時期を確認しました！）
しかもそれぞれの使い分けまでドキュメントに書かれています！
ということで、CONNECT BY句で実装してみました！
Oracleをご存じない方には、ピンとこないと思いますので、別解答をお伝えした上で、少し解説をさせていただきます！
CONNECT BYによる別解答例
結論を先に見せてしまいますと、CONNECT BYを使うことで、CTEsよりも格段のコード量が少なく、かなりシンプルな構文を実現できます！
ちなみに件数少ないこともありますが、どちらもSサイズで実行して500Msec前後で出力されました。
SELECT
department_ID,
department_name,
head_department_ID,
LEVEL AS hierarchy_level,
SYS_CONNECT_BY_PATH(department_name, ' → ') AS connection_tree
FROM departments
START WITH head_department_ID IS NULL
CONNECT BY head_department_ID = PRIOR department_ID
ORDER BY LEVEL, department_ID;
出力結果
| CONNECTION_TREE | DEPARTMENT_ID | HEAD_DEPARTMENT_ID | DEPARTMENT_NAME |
|---|---|---|---|
| → Research & Development | 1 | Research & Development | |
| → Research & Development → Human Resources | 2 | 1 | Human Resources |
| → Research & Development → Product Development | 11 | 1 | Product Development |
| → Research & Development → Human Resources → Recruitment | 21 | 2 | Recruitment |
| → Research & Development → Human Resources → Employee Relations | 22 | 2 | Employee Relations |
| → Research & Development → Product Development → Software Design | 111 | 11 | Software Design |
| → Research & Development → Product Development → Product Testing | 112 | 11 | Product Testing |
出力イメージ、CTEsと同じ結果で出力できました！
この結果を見て、皆さんもこの関数に少し興味が湧いてきたのではないでしょうか？
それでは、この便利な関数である、CONNECT BYの解説をしていきたいと思います。
CONNECT BY句およびその関連要素
【主要構文】
・CONNECT BY句：親子関係を定義する必須の句
・START WITH句：階層の開始点を指定する句
【利用可能な疑似列・関数・演算子】
・LEVEL疑似列：各行の階層レベルを返す
・CONNECT_BY_ROOT演算子：ルート行の値を取得
・SYS_CONNECT_BY_PATH関数：ルートから現在行までのパスを生成
・PRIOR演算子：親行を参照する
各要素の詳細説明案
1. CONNECT BY句
機能：階層データの親子関係を定義し、テーブルを自分自身に結合して階層構造を展開します。
基本構文
SELECT column_list
FROM table_name
START WITH condition -- 階層の開始点
CONNECT BY PRIOR parent_col = child_col -- 親子関係の定義
動作原理
-
START WITHで指定された条件に一致する行をルート（開始点）とする -
CONNECT BY条件に基づいて、親行から子行を再帰的に検索 -
PRIOR演算子で親行の値を参照し、現在評価中の行と比較 - 条件に一致する限り階層を下位に展開し続ける
2. LEVEL疑似列
機能：階層クエリにおいて、各行の階層レベル（深さ）を自動計算して返す疑似列です。
特徴
・ルート行（最上位）：LEVEL = 1
・ルートの子：LEVEL = 2
・ルートの孫：LEVEL = 3
・以下、階層が深くなるごとに +1
実用例
SELECT employee_id, name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER BY LEVEL, employee_id;
注意点
・疑似列のため、実際のテーブルには存在しない
・SELECT句、WHERE句、ORDER BY句で使用可能
・START WITH句では使用不可
3. SYS_CONNECT_BY_PATH関数
機能：階層クエリにおいて、ルートから現在の行までの階層パスを指定した区切り文字で連結した文字列を返します。
構文
SYS_CONNECT_BY_PATH(column, delimiter)
パラメータ
・column：パスに含めたい列名（文字型である必要）
・delimiter：各要素間の区切り文字（例：'/', ' → ', ' | '）
動作原理
- ルート行：
delimiter + column_value - 子行：
親のパス + delimiter + 現在のcolumn_value - 階層を下るごとに文字列が連結される
実用例
-- 社員階層のパス表示
SELECT
SYS_CONNECT_BY_PATH(title, ' -> ') AS hierarchy_path,
employee_ID,
title
FROM employees
START WITH title = 'President'
CONNECT BY employee_id = PRIOR manager_ID ;
-- 結果例:
-- -> President
-- -> President -> Vice President Engineering
-- -> President -> Vice President Engineering -> Programmer
4. CONNECT_BY_ROOT演算子
機能：階層内の各行について、その階層のルート（最上位）行の値を取得します。
構文
CONNECT_BY_ROOT column_name
動作原理
- 階層内のどの深さの行でも、その階層ツリーの最上位の値を参照できる
- 集計や分析で「この部門は最終的にどの親部門に属するか」を特定する際に有用
- 各行に対してルート情報を付与することで、グループ化や集計が容易になる
実用例
-- 各部門がどの最上位部門に属するかを表示
SELECT
department_ID,
department_name,
CONNECT_BY_ROOT department_name AS root_department,
LEVEL AS hierarchy_level
FROM departments
START WITH head_department_ID IS NULL
CONNECT BY head_department_ID = PRIOR department_ID
ORDER BY LEVEL, department_ID;
-- 結果イメージ:
-- department_ID | department_name | root_department | hierarchy_level
-- 1 | Research & Development| Research & Development | 1
-- 11 | Product Development | Research & Development | 2
-- 111 | Software Design | Research & Development | 3
-- 112 | Product Testing | Research & Development | 3
-- 2 | Human Resources | Research & Development | 2
活用シーン
- コスト集計：下位部門のコストを最上位部門ごとに集計する際に使用
- 権限管理：各ユーザーがどの最上位組織に属するかを特定
- レポート生成：階層全体を最上位単位でグループ化して分析
階層クエリにおける注意点：循環参照
階層データを扱う際、**循環参照（Circular Reference）**に注意が必要です
循環参照への対処
階層データでは、部門Aの親が部門B、部門Bの親が部門Aという循環参照が発生する可能性があります。SnowflakeはOracleのNOCYCLE句を現在サポートしていないため、以下の対策を推奨します。
- データ投入時にバリデーションを実施し、循環参照を事前に防止
- 再帰CTEを使用する場合は
WHERE level < 10等で階層深度を制限 - 定期的なデータ品質チェックで異常な親子関係を検出
そのような状態をチェックするためのSQLも作成可能です。
-- 2階層の循環参照を検出
SELECT d1.department_id, d1.head_department_id
FROM departments d1
JOIN departments d2 ON d1.head_department_id = d2.department_id
WHERE d2.head_department_id = d1.department_id;
CONNECT_BY_ROOT の活用
各行に対してルート部門を取得したい場合は、CONNECT_BY_ROOT department_nameを使用できます。これにより、下位部門のデータを最上位部門ごとに集計する際に便利です。
SELECT
department_name,
CONNECT_BY_ROOT department_name AS root_dept,
LEVEL
FROM departments
START WITH head_department_ID IS NULL
CONNECT BY head_department_ID = PRIOR department_ID;
ここまでの説明でCONNECT BYに関する理解を深まったと思います。
なお、実行計画を比較するとCONNECT BY句（右側）の方が処理ステップ的には少し複雑になっています。
より大きなデータサイズでのパフォーマンスは、皆さんもぜひ検証いただければと思います！
まとめ
という訳で元々のお題はアウトプットイメージの画像を見る限り、おそらくCTEsでの解答を想定していたと思います。
しかし、皆さん馴染みのCTEsではなく、Oracle互換のSQL関数を使うことで、より効率的により可読性の高い解答を行うことができました。
これはお題が間違っているとかそういう指摘ではなく、Oracle独自の構文や関数が非常に多く、それらの多くはデータ操作を効率良く実装できるようになっています。
そして、SnowflakeはOracleを含め、他DBの互換対応は丁寧に実装してくれている一方で、このような多言語固有の関数を知っている人も限られているので、今回を機にぜひシェア出来ればと思った次第です！
収録時にもこれらの解説をした時も、皆さんから、「へー！」「ほー！」「知らなかった！」などの期待通りの反応をいただきました！
収録では、それ以外にも私が書いた記事の解説などお話する時間をいただき、様々な知見を共有しながら、みんなで楽しい時間を過ごさせていただきました！
私が紹介させていただいた2件の記事
タイミングがあえば、またいつか収録に参加したいと思いました！
興味をもっていただいた皆さんもぜひゲスト参加にチャレンジしてみてください！
SnowVillageについて
さて、このような記事を書くきっかけとなったのは、SnowflakeのユーザーコミュニティであるSnowVillageやFrosty Fridayを運営しているコミュニティメンバーとの出会いがあったからです。
コミュニティに参加するだけで様々な知見や学びを得られますし、イベントに参加することでより実践的で深い技術やノウハウを学ぶことが出来ます。それらを通じてデータエンジニアとして成長する機会も大きく増えると思いますので、少しでも興味を持った方はぜひご参加ください。
またいきなりコミュニティに入るのは・・という方もまずはSnowVillageが運営するYoutubeチャネルでアーカイブを観たり、配信を視聴してはいかがでしょうか？最新のアップデートや技術ニュースなど様々なことを楽しく学べると思います！
SnowVillage
SnowVillageチャネル &Frosty Friday Live Challenge
イベント案内
Snowflake データクラウドのユーザ会 SnowVillage のメンバーで運営しています。 Publication参加方法はこちらをご参照ください。 zenn.dev/dataheroes/articles/db5da0959b4bdd
Discussion