Zenn
👩‍💻

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

に公開
Google Sheets
ツール作成
tech

はじめに

今回、スプレッドシートの関数を使って効率的にテストデータを作成したいと思い記事にしました。
入れ子集合モデルの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の値を計算するのが大変だったため、スプレッドシートで計算できるようにしてみました。
最後までご覧いただきありがとうございました。

Discussion