Snowflakeで階層問合せを試してみた
分析屋の中田(ナカタ)です。
SnowflakeのCONNECT BY句を試してみました。
CONNECT BY句とは
階層的な、親子関係を扱うクエリを実行したい場合に用いる文法です。
発祥はOracleだと思われますが、完全な互換性があるわけではないようです。
CONNECT_BY | Snowflake Documentation
今回やること
社員マスタを元に、階層構造を表示します。
ちょっとした組織図的なイメージです。
環境
Snowflakeのエディション:ビジネスクリティカル版(トライアル期間)
クラウド:AWS(東京リージョン)
サンプルデータ
公式ドキュメントのサンプルクエリをまんま使っています。
CONNECT_BY | Snowflake Documentation
-- 社員マスタの空テーブル作成
CREATE OR REPLACE TABLE employees (
title VARCHAR
,employee_ID INTEGER
,manager_ID INTEGER
);
-- 社員データのINSERT
INSERT INTO employees (title, employee_ID, manager_ID)
VALUES
('President', 1, NULL) -- The President has no manager.
,('Vice President Engineering', 10, 1)
,('Programmer', 100, 10)
,('QA Engineer', 101, 10)
,('Vice President HR', 20, 1)
,('Health Insurance Analyst', 200, 20)
;社員マスタの中身はこうなりました。
TITLE列:役職
EMPLOYEE_ID列:従業員コード
MANAGER_ID列:管理者コード(上長)
役職President(社長)の上司はいないので、MANAGER_IDがnullになっています。
CONNECT_BY句の検証
サンプルのクエリをたたいてみます。
-- 階層構造の表示
SELECT
employee_ID
,manager_ID
,title
FROM
employees
START WITH
title = 'President'
CONNECT BY
manager_ID = PRIOR employee_id
ORDER BY
employee_ID ASC
;実行結果
President(社長)を起点に、階層構造を表示できています。
START WITH：どの行を起点にするか、条件を指定します。
CONNECT BY：親子関係の紐づけ方法を指定します。
manager_ID = PRIOR employee_id
これは、管理者IDが前の従業員コードと同じ行を紐づけるという意味になります。
他に、SELECT句にCONNECT_BY_ROOTを記述すれば大元の系列を表示することもできます。
-- 階層構造の表示
SELECT
employee_ID
,manager_ID
,title
,CONNECT_BY_ROOT title AS root_title
FROM
employees
START WITH
title = 'President'
CONNECT BY
manager_ID = PRIOR employee_id
ORDER BY
employee_ID ASC
;
すべてPresident配下であることが分かります。
最後に
OracleからSnowflakeに移植する場合に、WITH句の再帰クエリで書き直すのが面倒なのを容易にするために用意されたのかなと感じました。
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
これまでの記事はこちら！
株式会社分析屋について
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。