Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Introduction to Tree Representations in RDB 2024

Introduction to Tree Representations in RDB 2024

RDBでのツリー表現入門2024
RDBでツリー構造を表現するための典型的なモデルについて学ぼう!
cf. 2020年版(in Opt Technologies): https://speakerdeck.com/lagenorhynque/introduction-to-tree-representations-in-rdb

Kent OHASHI

July 26, 2024
Tweet

More Decks by Kent OHASHI

Other Decks in Programming

Transcript

  1. のプロダクトエンジニア 主要技術スタック: / + PostgreSQL 関数型言語/関数型プログラミングが好き 仕事や趣味で , , などに長く

    触れてきた RDB/SQLが好き プログラミングを始めて最初に好きになった言語 がSQLだった lagénorhynque 🐬カマイルカ 株式会社スマートラウンド Kotlin Ktor Clojure Scala Haskell 2
  2. ツリーの本体データ用テーブル department CREATE TABLE `department` ( `department_id` int(10) unsigned NOT

    NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`department_id`) ); 6
  3. ツリー管理用のテーブル 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 ); 11
  4. すべてのノードとその階層情報の取得 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; MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions) 13
  5. 14

  6. 特定のノードからの子孫ノードの取得 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; 15
  7. 16

  8. 18

  9. 20

  10. CONS 再帰CTE (recursive common table expressions)が 使えないと任意階層に対するクエリが困難 削除操作が煩雑 parent_id で

    NULL が現れてしまう ルートノード管理用のテーブルを用意することで 回避可能 22
  11. ツリー管理用のテーブル 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 ); 25
  12. 28

  13. 特定のノードからの子孫ノードの取得 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 ), '%'); 29
  14. 30

  15. 特定のノードの子ノードの取得 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+/$'); 31
  16. 32

  17. 特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_path_enumeration dpe USING

    (department_id) WHERE dpe.path = ( SELECT CASE WHEN path = CONCAT(department_id, '/') THEN NULL ELSE REGEXP_REPLACE(path, '^(.+/)\\d+/$', '$1') END FROM department_path_enumeration WHERE department_id = 10 ); 33
  18. 34

  19. ツリー管理用のテーブル 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 ); 39
  20. 42

  21. 特定のノードからの子孫ノードの取得 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; 43
  22. 44

  23. 特定のノードの子ノードの取得 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; 45
  24. 46

  25. 特定のノードの親ノードの取得 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 ); 47
  26. 48

  27. ツリー管理用のテーブル 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 ); 53
  28. すべてのノードとその階層情報の取得 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; 55
  29. 56

  30. 特定のノードからの子孫ノードの取得 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; 57
  31. 58

  32. 特定のノードの子ノードの取得 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; 59
  33. 60

  34. 特定のノードの親ノードの取得 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; 61
  35. 62