Tweet
Tweet

Slide 1

Slide 1 text

RDB でのツリー表現⼊⾨

Slide 2

Slide 2 text

lagénorhynque (defprofile lagénorhynque :id @lagenorhynque :reading "/laʒenɔʁɛ̃ k/" :aliases [" カマイルカ "] :languages [Clojure Haskell English français] :interests [programming language-learning law mathematics] :commits ["github.com/lagenorhynque/duct.module.pedestal" "github.com/lagenorhynque/duct.module.cambium"] :contributes ["github.com/japan-clojurians/clojure-site-ja"])

Slide 3

Slide 3 text

0. 事前準備 1. 隣接リスト(adjacency list) 2. 経路列挙(path enumeration) 3. ⼊れ⼦集合(nested sets) 4. 閉包テーブル(closure table)

Slide 4

Slide 4 text

事前準備

Slide 5

Slide 5 text

サンプルコード : ローカルDB: tree-representations-in-rdb docker-compose.yml mariadb:10.5.5

Slide 6

Slide 6 text

ツリーの本体データ⽤テーブル department CREATE TABLE `department` ( `department_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`department_id`) );

Slide 7

Slide 7 text

department の初期データ

Slide 8

Slide 8 text

表現したいツリー

Slide 9

Slide 9 text

隣接リスト (adjacency list)

Slide 10

Slide 10 text

「隣接リスト」モデル

Slide 11

Slide 11 text

ツリー管理⽤のテーブル parent_id: 親ノード 本体データ⽤テーブルに含めても良い CREATE TABLE `department_adjacency_list` ( `department_id` int(10) unsigned NOT NULL, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`department_id`), FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT, FOREIGN KEY (`parent_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT );

Slide 12

Slide 12 text

department_adjacency_list の初期データ

Slide 13

Slide 13 text

すべてのノードとその階層情報の取得 distance: ルートノードからの距離 cf. WITH RECURSIVE department_tree AS ( SELECT d.*, dal.parent_id, 0 distance FROM department d JOIN department_adjacency_list dal USING (department_id) WHERE dal.parent_id IS NULL UNION ALL SELECT d.*, dal.parent_id, dt.distance + 1 FROM department d JOIN department_adjacency_list dal USING (department_id) JOIN department_tree dt ON dal.parent_id = dt.department_id ) SELECT * FROM department_tree; WITH - MariaDB Knowledge Base

Slide 14

Slide 14 text

No content

Slide 15

Slide 15 text

特定のノードからの⼦孫ノードの取得 distance: ノード 10 からの距離 WITH RECURSIVE department_tree AS ( SELECT d.*, dal.parent_id, 0 distance FROM department d JOIN department_adjacency_list dal USING (department_id) WHERE d.department_id = 10 UNION ALL SELECT d.*, dal.parent_id, dt.distance + 1 FROM department d JOIN department_adjacency_list dal USING (department_id) JOIN department_tree dt ON dal.parent_id = dt.department_id ) SELECT * FROM department_tree;

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

特定のノードの⼦ノードの取得 SELECT d.* FROM department d JOIN department_adjacency_list dal USING (department_id) WHERE dal.parent_id = 10;

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_adjacency_list dal ON d.department_id = dal.parent_id WHERE dal.department_id = 10;

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

PROS モデルとして単純( 良くも悪くもナイーブ) 直近の親/ ⼦ノードに対するアクセスが容易 挿⼊操作が容易 参照整合性が保証できる

Slide 22

Slide 22 text

CONS 再帰CTE (recursive common table expressions) が使えないと任意階層に対するクエリが困難 削除操作が煩雑 parent_id で NULL が現れてしまう ルートノード管理⽤のテーブルを⽤意するこ とで回避可能

Slide 23

Slide 23 text

経路列挙 (path enumeration) a.k.a. 経路実体化(materialized path)

Slide 24

Slide 24 text

「経路列挙」モデル

Slide 25

Slide 25 text

ツリー管理⽤のテーブル path: 先祖からそのノードまでの経路( パス) 本体データ⽤テーブルに含めても良い CREATE TABLE `department_path_enumeration` ( `department_id` int(10) unsigned NOT NULL, `path` varchar(1000) NOT NULL, PRIMARY KEY (`department_id`), FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT );

Slide 26

Slide 26 text

department_path_enumeration の初期データ

Slide 27

Slide 27 text

すべてのノードとその階層情報の取得 depth: ルートノードからの距離 SELECT d.*, dpe.path, CHAR_LENGTH(dpe.path) - CHAR_LENGTH(REPLACE(dpe.path, '/', '')) - 1 depth FROM department d JOIN department_path_enumeration dpe USING (department_id);

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

特定のノードからの⼦孫ノードの取得 depth: ルートノードからの距離 SELECT d.*, dpe.path, CHAR_LENGTH(dpe.path) - CHAR_LENGTH(REPLACE(dpe.path, '/', '')) - 1 depth FROM department d JOIN department_path_enumeration dpe USING (department_id) WHERE dpe.path LIKE CONCAT(( SELECT path FROM department_path_enumeration WHERE department_id = 10 ), '%');

Slide 30

Slide 30 text

No content

Slide 31

Slide 31 text

特定のノードの⼦ノードの取得 SELECT d.* FROM department d JOIN department_path_enumeration dpe USING (department_id) WHERE dpe.path REGEXP CONCAT(( SELECT path FROM department_path_enumeration WHERE department_id = 10 ), '\\d+/$');

Slide 32

Slide 32 text

No content

Slide 33

Slide 33 text

特定のノードの親ノードの取得 SELECT d.* FROM department d WHERE d.department_id = ( SELECT CASE WHEN path = CONCAT(department_id, '/') THEN NULL ELSE REGEXP_REPLACE(path, '^(?:\\d+/)*(\\d+)/\\d+/$', '\\1') END FROM department_path_enumeration WHERE department_id = 10 );

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

PROS ⼦孫/ 先祖ノードに対するアクセスが容易 更新系操作が容易

Slide 36

Slide 36 text

CONS パス⽂字列をアプリケーションコードで管理しな ければならない 正規化されていない( 第1 正規形でさえない) 参照整合性が保証できない

Slide 37

Slide 37 text

⼊れ⼦集合 (nested sets) cf. ⼊れ⼦区間(nested intervals)

Slide 38

Slide 38 text

「⼊れ⼦集合」モデル

Slide 39

Slide 39 text

ツリー管理⽤のテーブル left, right: 数直線上の左右の点を表す depth: ルートノードからの距離( 必須ではない) 本体データ⽤テーブルに含めても良い CREATE TABLE `department_nested_sets` ( `department_id` int(10) unsigned NOT NULL, `left` int(11) NOT NULL, `right` int(11) NOT NULL, `depth` int(10) unsigned NOT NULL, PRIMARY KEY (`department_id`), FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT );

Slide 40

Slide 40 text

department_nested_sets の初期データ

Slide 41

Slide 41 text

すべてのノードとその階層情報の取得 SELECT d.*, dns.left, dns.right, dns.depth FROM department d JOIN department_nested_sets dns USING (department_id);

Slide 42

Slide 42 text

No content

Slide 43

Slide 43 text

特定のノードからの⼦孫ノードの取得 SELECT d.*, dns.left, dns.right, dns.depth FROM department d JOIN department_nested_sets dns USING (department_id) JOIN department_nested_sets dns2 ON dns.left BETWEEN dns2.left AND dns2.right WHERE dns2.department_id = 10;

Slide 44

Slide 44 text

No content

Slide 45

Slide 45 text

特定のノードの⼦ノードの取得 SELECT d.* FROM department d JOIN department_nested_sets dns USING (department_id) JOIN department_nested_sets dns2 ON dns.left BETWEEN dns2.left AND dns2.right WHERE dns2.department_id = 10 AND dns.depth = ( SELECT depth FROM department_nested_sets WHERE department_id = 10 ) + 1;

Slide 46

Slide 46 text

No content

Slide 47

Slide 47 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_nested_sets dns USING (department_id) JOIN department_nested_sets dns2 ON dns2.left BETWEEN dns.left AND dns.right WHERE dns2.department_id = 10 AND dns.depth + 1 = ( SELECT depth FROM department_nested_sets WHERE department_id = 10 );

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

PROS ⼦孫/ 先祖ノードに対するアクセスが容易 削除操作が容易

Slide 50

Slide 50 text

CONS 左右の点の値をアプリケーションコードで管理し なければならない 正規化されていない( 第1 正規形でさえない) 挿⼊操作が⾮常に煩雑でコストも⾼い 「⼊れ⼦区間」では改善される 参照整合性が保証できない

Slide 51

Slide 51 text

閉包テーブル (closure table)

Slide 52

Slide 52 text

「閉包テーブル」モデル

Slide 53

Slide 53 text

ツリー管理⽤のテーブル ancestor, descendant: 先祖/ ⼦孫関係にある ノードの組 path_length: ancestor から descendant ま での距離( 必須ではない) CREATE TABLE `department_closure_table` ( `ancestor` int(10) unsigned NOT NULL, `descendant` int(10) unsigned NOT NULL, `path_length` int(10) unsigned NOT NULL, PRIMARY KEY (`ancestor`, `descendant`), FOREIGN KEY (`ancestor`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT, FOREIGN KEY (`descendant`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT );

Slide 54

Slide 54 text

department_closure_table の初期データ

Slide 55

Slide 55 text

すべてのノードとその階層情報の取得 depth: ルートノードからの距離 MAX(dct.path_length) でも求められる SELECT d.*, COUNT(*) - 1 depth FROM department d JOIN department_closure_table dct ON d.department_id = dct.descendant GROUP BY dct.descendant;

Slide 56

Slide 56 text

No content

Slide 57

Slide 57 text

特定のノードからの⼦孫ノードの取得 distance: ノード 10 からの距離 depth: ルートノードからの距離 SELECT d.*, dct.path_length distance, dct2.depth FROM department d JOIN department_closure_table dct ON d.department_id = dct.descendant JOIN ( SELECT descendant, COUNT(*) - 1 depth FROM department_closure_table GROUP BY descendant ) dct2 ON d.department_id = dct2.descendant WHERE dct.ancestor = 10;

Slide 58

Slide 58 text

No content

Slide 59

Slide 59 text

特定のノードの⼦ノードの取得 SELECT d.* FROM department d JOIN department_closure_table dct ON d.department_id = dct.descendant WHERE dct.ancestor = 10 AND dct.path_length = 1;

Slide 60

Slide 60 text

No content

Slide 61

Slide 61 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_closure_table dct ON d.department_id = dct.ancestor WHERE dct.descendant = 10 AND dct.path_length = 1;

Slide 62

Slide 62 text

No content

Slide 63

Slide 63 text

PROS ⼦孫/ 先祖ノードに対するアクセスが容易 更新系操作が容易 参照整合性が保証できる

Slide 64

Slide 64 text

CONS 他のモデルよりも保持すべきデータが多くなる

Slide 65

Slide 65 text

まとめ 「閉包テーブル」は総合的に優れている 「隣接リスト」は最も素朴( ナイーブ) な設計だが デメリットもいくつかある 「経路列挙」「⼊れ⼦集合」は参照整合性が保証 できない(RDB の強みが犠牲になる)

Slide 66

Slide 66 text

Further Reading 2 章 ナイーブツリー（素朴な⽊） 第10 章 グラフに⽴ち向かう 10.4 ツリー（⽊） 第10 章 階層的なデータ構造 『SQL アンチパターン』 『理論から学ぶデータベース実践⼊⾨』 『E ective SQL 』

Slide 67

Slide 67 text

第9 章 ⼀歩進んだ論理設計 〜SQL で⽊構造 を扱う 『達⼈に学ぶDB 設計 徹底指南書』 『プログラマのためのSQL グラフ原論』 What are the options for storing hierarchical data in a relational database? - Stack Over ow