SQLアタマアカデミー
第5回 SQLで木構造を扱う~入れ子集合モデル (3)入れ子集合モデルにおける更新
入れ子集合モデルにおける更新
次に,
リーフを追加する
木にノードを追加するときは,
今,
リスト6 リーフの追加プロセス
--第一段階:追加するノードの席を空ける
UPDATE OrgChart
SET lft = CASE WHEN lft > :parent_rgt
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= :parent_rgt
THEN rgt + 2
ELSE rgt END
WHERE rgt >= :parent_rgt;
--第二段階:ノードを追加する
INSERT INTO OrgChart VALUES (:child_name, :parent_rgt, (:parent_rgt + 1));
そうして猪狩氏の円の幅を広げたら,
これを今回のサンプルを使って表現すると,
リスト7 今回のサンプルを使った表現
--第一段階:追加するノードの席を空ける
UPDATE OrgChart
SET lft = CASE WHEN lft > 3
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= 3
THEN rgt + 2
ELSE rgt END
WHERE rgt >= 3;
--第二段階:国見氏を追加する
INSERT INTO OrgChart VALUES ('国見', 3, (3 + 1));
図10 リスト7 第一段階で,
追加後の入れ子集合を図示すると,
部分木の削除
今度は逆に,
リスト8 部分木の削除
DELETE FROM OrgChart
WHERE lft BETWEEN (SELECT lft FROM OrgChart WHERE emp = :fired_emp)
AND (SELECT rgt FROM OrgChart WHERE emp = :fired_emp);
リスト9 江崎氏を解雇
DELETE FROM OrgChart
WHERE lft BETWEEN (SELECT lft FROM OrgChart WHERE emp = '江崎')
AND (SELECT rgt FROM OrgChart WHERE emp = '江崎');
図12 解雇後の組織図
emp | lft | rgt |
---|---|---|
足立 | 1 | 14 |
猪狩 | 2 | 3 |
上田 | 4 | 13 |
大神 | 9 | 10 |
加藤 | 11 | 12 |
手続き型言語ならば再帰的な処理を使わねばならない木の操作も,
おわりに
本稿で見たように,
これを防ぐため,
そこで次回では,
本連載のテーマは,
参考資料
- Joe Celko著
『Joe Celko's Trees and Hierarchies in SQL for Smarties』 (Morgan Kaufmann Pub, 2004) - モデルの考案者自らが懇切丁寧に解説を行うのだから,
入れ子集合モデルについて知りたいならば, 本書1冊を読むだけで十分です。役に立つ技術書は世に多くあれど, 感動する技術書はそう多くありません。しかし, 残念ながら邦訳はありません。 - Stephane Faroult, Peter Robson著
『アート・ オブ・ SQL』 (オライリー・ ジャパン,2007) - 第7章で,
階層データを扱う方法を主にパフォーマンスの観点から比較検討しています。入れ子集合モデルについては 「巧妙な手法」 としつつも, パフォーマンスについての欠点を指摘しています。しかし, 本書はもっと本質的なことも言っていて, それは入れ子集合モデルが座標でデータを管理する以上, 結局 「ポインタベースの解決策」 であり, RDBとSQLの理念に沿うというのは誤解だ, という批判です。これは, セルコにとってかなり痛いところを突いています。 - ミック著
『SQLで木と階層構造のデータを扱う (1) ―入れ子集合モデル』 - 私が前掲の
『Trees and Hierarchies』 をもとに解説を行ったサイトです。検索, 更新からほかの構造 (隣接リストモデルやXML) から入れ子集合テーブルへの変換方法など, かなり網羅的に解説したつもりです。本稿を読んで興味を持った方は, ご覧いただければ幸いです。
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合