はじめに
今回のブログではQuicksightでデータを分析するために、階層データの持ち方を工夫した話を紹介します。
まず、はじめにQuicksightでどのようなダッシュボードを作りたいかをイメージとして載せます。
この図のように階層がありそれぞれの階層を上部のコントロールで絞りこみができるようなダッシュボードを作りたいと思います。
上記のようなダッシュボードを作りたいと思っていますが、持っている階層データの形は以下のように直近の親の情報のみを持つデータでQSで利用しにくいものでした（以下図のようなデータ）。後ほど、紹介しますがこのデータはいわゆるナイーブツリー（隣接リスト）と呼ばれているデータ構造になります。本ブログでは、このデータを加工して利用しやすい形にしたいと思います。
階層データのパターン
使いやすい階層データを作るために、階層データにはどのような形があるのか調べてみました。代表的なものでいうと以下のようなものがあります。
ナイーブツリー（隣接リスト）
経路列挙(Path Enumeeration)
入れ子集合(Nested Set)
閉包テーブル
ナイーブツリー（隣接リスト）
以下のように隣接している親子関係の情報を持っています。
経路列挙(Path Enumeeration)
先祖へと辿るためのパスを属性として持つのが特徴です。
入れ子集合(Nested Set)
以下のような形でデータを持ち、子孫の集合に関する情報を各ノードに格納します。各ノードではルールに基づいてnsleftとnsrightの値が割り当てられます。具体的にはnsleft値にはそのノードより下の階層にある全てのノードが持つ値より小さな値が、nsright値にはそのノードより下の階層にある全てのノードが持つ値より大きな値が与えられます。
図示すると以下のようになります。3Cの子孫を取得したい場合は3Cノードのnsleftとnsrightの間にnsleftが含まれるノード全てを検索すれば取得できます。
閉包テーブル
直接の親子関係だけでなく、ツリー全体のパスを格納するを持ちます。パス情報のテーブルとID自身の情報の２テーブル構成となります。
言葉とレコードの構成だけだとイメージを掴みづらいと思うので、以下のように図を用意しました。TreePathテーブルでは、以下のように各ノードに対して自分自身のノードに加えて自身のノードの子孫となるノードの関連を全て持ちます。
どのパターンを採用すべきか？
何個か階層テーブルのパターンを紹介してきましたが、どのパターンを採用すべきでしょうか？
そのぞれのパターンの概要を以下表に示します。（この表の内容はSQLアンチパターン 初版30pの表から転記しています。備考は私が付け足しました。）
この表によると、閉包テーブルのパターンに優位性がありそうです。ということで、閉包テーブルモデルを採用しました。
QSで扱い易いテーブルへの加工
閉包テーブルモデルを採用しましたが、これをそのままQSで利用するには少々扱いづらいです。そこで、以下表の下側の表のようなテーブルを作りQSで参照するようにしました。各レベルの階層のIDを属性として持つような構成です。このようにすることで、各レベルのdistinctを取ることでコントロールの選択肢に利用できたりします。各階層を属性として持つ関係上、階層深さに制限が出来てしまいますが階層の深さが固定であればそんなに問題になりません。
まとめ
階層データを分析するために、データの加工方法について紹介しました。階層データを取り扱う時は是非参考にしてください。
付録（閉包テーブルの取り扱いについて）
データ参照方法について
こちらのような閉包テーブルデータの参照方法について、簡単に説明します。
子孫を取得する場合
ノード2Aの子孫を取得したい場合は以下のように書きます。
select *
from ID自身の情報 as i
inner join TreePath as t on i.ID=t.子孫ID
where t.先祖ID='2A';
先祖を取得する場合
ノード2Aの先祖を取得したい場合は以下のように書きます。
select *
from ID自身の情報 as i
inner join TreePath as t on i.ID=t.先祖ID
where t.子孫ID='2A';
直近の親や子の参照を簡単にするための改善
閉包テーブルは隣接リストや経路列挙に比べて直近の親や子へのクエリがやや複雑です。ですが、それを容易にするための工夫としてTreePathテーブルへのpath_length属性の追加ができます。ノードの自己参照は0、直近の子は1、孫は2という風に割り当てできます。そうすることで、以下のように簡単に取得することができます。
select *
from TreePath
where 先祖ID ='2A' and path_length=1;
Comments