#114 スプレッドシートの関数を使って入れ子集合モデルのテストデータを作成してみる

はじめに

今回、スプレッドシートの関数を使って効率的にテストデータを作成したいと思い記事にしました。
入れ子集合モデルのlft、rgtを自動で入力しインサート用のSQL文を作成できるように設定してみます。

入れ子集合モデルについて

入れ子集合モデル（Nested Set Model）は、階層構造を表現するために、各ノードにlft（左位置）とrgt（右位置）を割り当てる方法です。
参照: https://gihyo.jp/dev/serial/01/sql_academy2/000501

Root
  ├─ A
  │   ├─ A1
  │   │   ├─ A1-1
  │   │   ├─ A1-2
  │   ├─ A2
  └─ B
      ├─ B1
      └─ B2

lftとrgtの値は以下のようになります。

Node	lft	rgt
Root	1	18
A	2	7
A1	3	6
A1-1	4	5
A1-2	8	9
A2	10	11
B	12	17
B1	13	14
B2	15	16

スプレッドシートの作成

データを入力する

以下のようにスプレッドシートに入力します。
Depth（列D）は空にしておきます。

ID（列A）	Name（列B）	Parent ID（列C）
1	Root	(空欄)
2	A	1
3	A1	2
4	A1-1	3
5	A1-2	3
6	A2	2
7	B	1
8	B1	7
9	B2	7

lftとrgtを計算する（数式を設定する）

lftとrgtを計算するために必要な、ノードの階層の深さと子のノードの数を求めていきます。

階層の深さの計算

階層を計算するため、 Depth（列D）を追加し数式を入力します。

excel

=IF(C2="", 1, VLOOKUP(C2, A$2:D$100, 4, FALSE) + 1)

処理の内容は以下です。
・Parent ID（列C）が空欄なら Depth（列D）に1を入れる。
・Parent ID（列C）が空欄でなければ、ID（列A）を検索しParent ID（列C）と等しい値の行からDepth（列D）の値を取得する。

lftの計算

lft（列E）を追加し数式を入力します。

excel

=IF(C2="", 1, MAX(E$1:E1) + 2)

・Parent ID（列C）が空欄ならlft（列E）に1を入れる。
・Parent ID（列C）が空欄でなければ、lft の最大値に2を足した値を入れる。

rgtの計算

rgt（列F）を追加し数式を入力します。

excel

=E2 + 2 * (COUNTIF(C$2:C$100, A2))

・親のIDに対して、子がいくつ存在するか（C列に親のID（列A）がいくつ存在するか）を数える。
・1つの子に対し2つの位置（lft と rgt）が割り当てられるので、カウントした子の数に2を掛ける
・各行のrgtの値を足す。

計算結果の例

入力すると以下のようになります。

ID（列A）	Name（列B）	Parent ID（列C）	Depth（列D）	lft（列E）	rgt（列F）
1	Root		1	1	18
2	A	1	2	2	7
3	A1	2	3	3	6
4	A1-1	3	4	4	5
5	A1-2	3	4	8	9
6	A2	2	3	10	11
7	B	1	2	12	17
8	B1	7	3	13	14
9	B2	7	3	15	16

SQL文を生成する

計算したlftとrgtを使って、SQL文を生成します。

列Gに数式を入力します。

excel

=CONCATENATE("INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (", A2, ", '", B2, "', ", IF(C2 = "", "NULL", C2), ", ", E2, ", ", F2, ");")

・CONCATENATE で文字列を結合する。
・Parent ID（列C）が空であれば NULL を設定する。

結果例：

ID	Name	Parent ID	Depth	lft	rgt	SQL (G列)
1	Root		1	1	18	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (1, 'Root', NULL, 1, 18);
2	A	1	2	2	7	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (2, 'A', 1, 2, 7);
3	A1	2	3	3	6	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (3, 'A1', 2, 3, 6);
4	A1-1	3	4	4	5	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (4, 'A1-1', 3, 4, 5);
5	A1-2	3	4	8	9	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (5, 'A1-2', 3, 8, 9);
6	A2	2	3	10	11	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (6, 'A2', 2, 10, 11);
7	B	1	2	12	17	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (7, 'B', 1, 12, 17);
8	B1	7	3	13	14	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (8, 'B1', 7, 13, 14);
9	B2	7	3	15	16	INSERT INTO nested_set (id, name, parent_id, lft, rgt) VALUES (9, 'B2', 7, 15, 16);

最後に

lftとrgtの値を計算するのが大変だったため、スプレッドシートで計算できるようにしてみました。
最後までご覧いただきありがとうございました。