」という本を買って少しずつ勉強しているのですが、この本の翻訳をされているミックさんという方が「リレーショナル・データベースの世界」というページを開設されています。「SQLパズル 第2版 プログラミングが変わる書き方/考え方
いろいろ興味深いトピックがたくさんあります。
その中で「SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル」という記事を、実際に例をDB2/400 で実行しながら読んでみました。実行する上でちょっとした修正が必要になったので、それをここに書いておきたいと思います。内容はもちろんリンク先の記事を見てくださいね。
「2-2．ノードの深さを計算する」にある
SELECT Children.emp , COUNT(Parents.emp) AS level FROM OrgChart Parents, OrgChart Children WHERE Children.lft BETWEEN Parents.lft AND Parents.rgt GROUP BY Children.emp;
は画面例どおりに出すには
SELECT Children.emp , COUNT(Parents.emp) AS level FROM OrgChart Parents, OrgChart Children WHERE Children.lft BETWEEN Parents.lft AND Parents.rgt GROUP BY Children.emp ORDER BY 2;
でした。
「2-4．木をインデント付きで表示する」にある次の文は
SELECT LPAD(Mgrs.emp, LENGTH(Mgrs.emp) + CAST(COUNT(*) AS INTEGER) + 1, ' ') AS emp FROM OrgChart Mgrs, OrgChart MidMgrs, OrgChart Workers WHERE Mgrs.lft BETWEEN MidMgrs.lft AND MidMgrs.rgt AND MidMgrs.lft BETWEEN Workers.lft AND Workers.rgt GROUP BY Mgrs.emp, Mgrs.lft ORDER BY MAX(Mgrs.lft);
「LPAD 関数は実装依存の関数のため、Oracle、PostgreSQL、MySQL でのみ使用可能」であり、「DB2 では REPEAT」で代用できるということなので↓のように書き替えてみました。
SELECT REPEAT(' ', LENGTH(Mgrs.emp) + CAST(COUNT(*) AS INTEGER) + 1) || Mgrs.emp AS emp
FROM OrgChart Mgrs, OrgChart MidMgrs, OrgChart Workers
WHERE Mgrs.lft BETWEEN MidMgrs.lft AND MidMgrs.rgt
AND MidMgrs.lft BETWEEN Workers.lft AND Workers.rgt
GROUP BY Mgrs.emp, Mgrs.lft
ORDER BY MAX(Mgrs.lft);
表示結果は↓のようになります。
「2-7．パスを列挙する（列持ちバージョン）」の「SQL のロジックを本当に理解していないと書くことのできない見事なもの」と言われている次の SQL が、「ある意味で盲点を突いた技なので、動かない DB も少なからずあります。PostgreSQL では問題なく動作しますが、Oracle ではこの点がネックで動きません」と書いてあり、どきどきしながら実行したのですが↓のようになりました。
実行でき、個々の行の結果、集合としては正しいので「実行できた」ということになるのだとは思いますが、並び順が載っている例と違うのでちょっと悩んでいるところです。
ちなみに、「ほとんどの実装で動作する」ように書き替えた例として載っている SQL の実行例は↓のようになります。
「3-1．部分木の削除」で関連した部分木の削除の例が載っています。
欠番になった添え字の削除用のSQL も載っていますが、対象となる部分が変数になっているものです。「添え字の
5 〜 8 が欠番になっています」ということなので、:drop_lft に 5、:drop_rgt
に 8をそれぞれ代入して実行してみれば、今回の結果の確認をすることができます。
「3-2．単一ノードの削除」で紹介されているSQL には、以下のような注意があります。
>>
実は DB によっては動かない可能性もあります。標準 SQL には沿っていますし、Oracle では正しく動作するのですが、問題となる点は以下の二つです。
- この更新が実行される最中、一時的に emp 列に重複が生じるため、主キー違反となる。
- UPDATE での更新時に同一テーブルの参照をサポートしていない。
PostgreSQL は第一の理由によってエラーとなります。確かに、このクエリのロジックを順に追っていくと、削除する親の名前を第一子の名前で上書きするために、一時的に次のように emp 列が重複する期間があるのです。
>>
実際、DB2/400 で実行してみると、おそらく PostgreSQL と同様の理由でエラーになりました。
PL/SQL のプロシージャを、「Oracle PL/SQL 入門」と DB2 for i5/OS の Redbook を参考にしながら、かなり機械的に変換してみました。
counter INTEGER :=2;
DECLARE counter INTEGER DEFAULT 2;
これは単なる構文の違いですね。ちなみに PL/SQL のただの LOOP は EXIT で抜けますが、DB2 の SQL PSM では LEAVE で抜けます。
WHILE counter <= max_counter- 1
LOOP
END LOOP;
WHILE counter <= max_counter- 1
DO
END WHILE;
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), counter, NULL
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
INSERT するカラムを、全選択ではなく個別に指定して、デフォルトが NULL になるようにするか、
INSERT INTO Stack (stack_top, node, lft)
SELECT (current_top + 1), MIN(T1.node), counter
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
内容が NULL である変数(定数) を定義して、それを該当カラムのセット内容とするか、というやり方を考えてみました。
DECLARE int_null INTEGER DEFAULT NULL;
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), counter, int_null
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
と、やってみたところで、もっとそれらしいやり方を発見しました。
これが元々に一番近そうです。
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), counter, CAST(NULL as Integer)
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
CREATE PROCEDURE TreeTraversal
LANGUAGE SQL
BEGIN
DECLARE counter INTEGER DEFAULT 2;
DECLARE max_counter INTEGER DEFAULT 0;
DECLARE current_top INTEGER DEFAULT 1;
DECLARE current_cnt INTEGER DEFAULT 0;
--カウンタの最大値を初期化（一つのノードについて二度巡回するので2倍している）
SELECT COUNT(*) * 2 INTO max_counter FROM Tree;
--スタックの初期化
DELETE FROM Stack;
--ルートをプッシュ
INSERT INTO Stack
SELECT 1, node, 1, max_counter
FROM Tree
WHERE parent IS NULL;
--プッシュしたノードを元の木から削除
DELETE FROM Tree WHERE parent IS NULL;
WHILE counter <= max_counter- 1
DO
--プッシュしたノードが子を持つか調べる
SELECT COUNT(*) INTO current_cnt
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
IF current_cnt > 0 THEN
-- ノードが子を持っていれば、子を一つプッシュする
INSERT INTO Stack (stack_top, node, lft)
SELECT (current_top + 1), MIN(T1.node), counter
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
-- プッシュしたノードを元の木から削除
DELETE FROM Tree
WHERE node = (SELECT node
FROM Stack
WHERE stack_top = current_top + 1);
-- カウンタとスタック・ポインタのインクリメント
SET counter = counter + 1;
SET current_top = current_top + 1;
ELSE
-- スタックをポップして、rgtの値を決める
UPDATE Stack
SET rgt = counter,
stack_top = -stack_top -- スタックのポップ
WHERE stack_top = current_top;
-- カウンタはインクリメントするが、スタック・ポインタはデクリメント
SET counter = counter + 1;
SET current_top = current_top - 1;
END IF;
END WHILE;
END;
|
こちらが NULL にセットした定数を使用したバージョンです。
CREATE PROCEDURE TreeTraversal
LANGUAGE SQL
BEGIN
DECLARE counter INTEGER DEFAULT 2;
DECLARE max_counter INTEGER DEFAULT 0;
DECLARE current_top INTEGER DEFAULT 1;
DECLARE current_cnt INTEGER DEFAULT 0;
DECLARE int_null INTEGER DEFAULT NULL;
--カウンタの最大値を初期化（一つのノードについて二度巡回するので2倍している）
SELECT COUNT(*) * 2 INTO max_counter FROM Tree;
--スタックの初期化
DELETE FROM Stack;
--ルートをプッシュ
INSERT INTO Stack
SELECT 1, node, 1, max_counter
FROM Tree
WHERE parent IS NULL;
--プッシュしたノードを元の木から削除
DELETE FROM Tree WHERE parent IS NULL;
WHILE counter <= max_counter- 1
DO
--プッシュしたノードが子を持つか調べる
SELECT COUNT(*) INTO current_cnt
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
IF current_cnt > 0 THEN
-- ノードが子を持っていれば、子を一つプッシュする
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), counter, int_null
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
-- プッシュしたノードを元の木から削除
DELETE FROM Tree
WHERE node = (SELECT node
FROM Stack
WHERE stack_top = current_top + 1);
-- カウンタとスタック・ポインタのインクリメント
SET counter = counter + 1;
SET current_top = current_top + 1;
ELSE
-- スタックをポップして、rgtの値を決める
UPDATE Stack
SET rgt = counter,
stack_top = -stack_top -- スタックのポップ
WHERE stack_top = current_top;
-- カウンタはインクリメントするが、スタック・ポインタはデクリメント
SET counter = counter + 1;
SET current_top = current_top - 1;
END IF;
END WHILE;
END;
|
NULL を直接 CAST して使うやり方です。
CREATE PROCEDURE TreeTraversal
LANGUAGE SQL
BEGIN
DECLARE counter INTEGER DEFAULT 2;
DECLARE max_counter INTEGER DEFAULT 0;
DECLARE current_top INTEGER DEFAULT 1;
DECLARE current_cnt INTEGER DEFAULT 0;
--カウンタの最大値を初期化（一つのノードについて二度巡回するので2倍している）
SELECT COUNT(*) * 2 INTO max_counter FROM Tree;
--スタックの初期化
DELETE FROM Stack;
--ルートをプッシュ
INSERT INTO Stack
SELECT 1, node, 1, max_counter
FROM Tree
WHERE parent IS NULL;
--プッシュしたノードを元の木から削除
DELETE FROM Tree WHERE parent IS NULL;
WHILE counter <= max_counter- 1
DO
--プッシュしたノードが子を持つか調べる
SELECT COUNT(*) INTO current_cnt
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
IF current_cnt > 0 THEN
-- ノードが子を持っていれば、子を一つプッシュする
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), counter, CAST(NULL as Integer)
FROM Stack S1, Tree T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
-- プッシュしたノードを元の木から削除
DELETE FROM Tree
WHERE node = (SELECT node
FROM Stack
WHERE stack_top = current_top + 1);
-- カウンタとスタック・ポインタのインクリメント
SET counter = counter + 1;
SET current_top = current_top + 1;
ELSE
-- スタックをポップして、rgtの値を決める
UPDATE Stack
SET rgt = counter,
stack_top = -stack_top -- スタックのポップ
WHERE stack_top = current_top;
-- カウンタはインクリメントするが、スタック・ポインタはデクリメント
SET counter = counter + 1;
SET current_top = current_top - 1;
END IF;
END WHILE;
END;
|
OrgChart_AL というテーブルが必要になるのですが、↓のようなかんじで作成してみました。
OrgChart_PE というテーブルが必要になるので、↓のように作成してみました。
OrgChart_XML というテーブルが必要になるので、↓のように作成してみました。
「4．木の構造を比較する」はすべて何の問題もなく実行できました。
|
|