こんにちは、knsk765 です。
今回は階層構造をデータベースで表現する方法について説明します。身近な例としてこんな組織図で考えましょう。
階層構造を扱うときに困るのが何階層まであるか制限がないときですよね。2階層までのものもあれば5階層までいくものもある、みたいな。どこが終わりかわからないデータを SQL でどうやってとればいいの？ってなります。
そこで使うのが CTE（共通テーブル式）での再帰クエリーです。
CTE と再帰クエリーについてはこちらの記事で書いているのであわせて見てみていただければ。
目次
準備
実行環境
PostgreSQL 14.5
テーブル
まずは部署テーブルを作成。使うテーブルはこれ1つです。
CREATE TABLE public.departments ( id int4 NOT NULL, "name" varchar NOT NULL, parent_id int4 NULL, order_no int2 NOT NULL, CONSTRAINT departments_pk PRIMARY KEY (id), CONSTRAINT departments_fk FOREIGN KEY (parent_id) REFERENCES public.departments(id) ON DELETE CASCADE ON UPDATE CASCADE );
親の部署ID parent_id を持たせて階層構造の関連を実現します。
一番ルートになる部署のデータはこう。parent_id は NULL です。
| id | name | parent_id | order_no |
|---|---|---|---|
| 1 | 管理本部 | 1 | |
| 2 | 営業本部 | 2 | |
| 3 | 開発本部 | 3 |
管理本部（id: 1）に属する部署はこうなりますね。
| id | name | parent_id | order_no |
|---|---|---|---|
| 101 | 総務人事部 | 1 | 1 |
| 102 | 財務部 | 1 | 2 |
| 103 | 情報システム室 | 1 | 3 |
というわけで以下の SQL を実行してサンプルデータを登録しましょう。
INSERT INTO departments (id, name, parent_id, order_no) VALUES (1, '管理本部', null, 1), (101, '総務人事部', 1, 1), (10101, '総務課', 101, 1), (10102, '人事課', 101, 2), (102, '財務部', 1, 2), (10201, '経理課', 102, 1), (103, '情報システム室', 1, 3), (2, '営業本部', null, 2), (201, '営業企画部', 2, 1), (20101, '営業企画課', 201, 1), (202, '営業部', 2, 2), (20201, '大阪営業所', 202, 1), (20202, '東京営業所', 202, 2), (20203, '名古屋営業所', 202, 3), (3, '開発本部', null, 3), (301, '商品開発部', 3, 1), (30101, '1課', 301, 1), (30102, '2課', 301, 2), (3010201, '第一小隊', 30102, 1), (3010202, '第二小隊', 30102, 2), (302, '空手部', 3, 2);
ゴール
1回の SELECT SQL で全階層の部署を構造化して取得するのがこの記事のゴールです。
基本編
では、少しずつ SQL を組み立てていってゴールを目指します。
Step 1. 最上位部署の取得
一番上の部署を取得するのは簡単ですね。親部署ID が NULL の部署が対象です。
SELECT id, name, parent_id, order_no FROM departments WHERE parent_id IS NULL;
結果
| id | name | parent_id | order_no |
|---|---|---|---|
| 1 | 管理本部 | 1 | |
| 2 | 営業本部 | 2 | |
| 3 | 開発本部 | 3 |
Step 2. 最上位部署を親に持つ部署の取得
次の階層の部署を取得するとなるとこうですね。方法はいろいろありますが、次の説明のために INNER JOIN で実現しています。
SELECT c.id, c.name, c.parent_id, c.order_no FROM departments c INNER JOIN ( -- ルート部署 SELECT * FROM departments WHERE parent_id IS NULL ) p ON (p.id = c.parent_id);
結果
| id | name | parent_id | order_no |
|---|---|---|---|
| 101 | 総務人事部 | 1 | 1 |
| 102 | 財務部 | 1 | 2 |
| 103 | 情報システム室 | 1 | 3 |
| 201 | 営業企画部 | 2 | 1 |
| 202 | 営業部 | 2 | 2 |
| 301 | 商品開発部 | 3 | 1 |
| 302 | 空手部 | 3 | 2 |
このまままだと以下の問題でつまずきますね。
- 階層が増えるたびに INNER JOIN を増やすのか？
- 何階層まであるかわからないときはどうする？
Step 3. 再帰クエリーで全階層の部署を取得
というわけで CTE（共通テーブル式）による再帰クエリーの出番です。
WITH RECURSIVE cte AS ( -- 最上位階層 ... Step 1. の SQL SELECT id, name, parent_id, order_no FROM departments WHERE parent_id IS NULL UNION ALL -- 子階層 ... Step 2. の SQL を改良 SELECT c.id, c.name, c.parent_id, c.order_no FROM departments c INNER JOIN cte p ON (p.id = c.parent_id) ) SELECT id, name, parent_id, order_no FROM cte;
最上位階層と次の階層を UNION した cte をもとに次の階層、その次の階層、と再帰的に結果を UNION していくと...
結果
| id | name | parent_id | order_no |
|---|---|---|---|
| 1 | 管理本部 | 1 | |
| 2 | 営業本部 | 2 | |
| 3 | 開発本部 | 3 | |
| 103 | 情報システム室 | 1 | 3 |
| 101 | 総務人事部 | 1 | 1 |
| 102 | 財務部 | 1 | 2 |
| 202 | 営業部 | 2 | 2 |
| 201 | 営業企画部 | 2 | 1 |
| 302 | 空手部 | 3 | 2 |
| 301 | 商品開発部 | 3 | 1 |
| 10101 | 総務課 | 101 | 1 |
| 10102 | 人事課 | 101 | 2 |
| 10201 | 経理課 | 102 | 1 |
| 20101 | 営業企画課 | 201 | 1 |
| 20203 | 名古屋営業所 | 202 | 3 |
| 20201 | 大阪営業所 | 202 | 1 |
| 20202 | 東京営業所 | 202 | 2 |
| 30102 | 2課 | 301 | 2 |
| 30101 | 1課 | 301 | 1 |
| 3010201 | 第一小隊 | 30102 | 1 |
| 3010202 | 第二小隊 | 30102 | 2 |
一番末端の階層までいっきに取得できました！
Step 4. 親階層の名前を連結する
全階層のデータが取得できるようになりましたが、今のままだと親子関係がわからないですよね。次は親部署の名前も連結して表示しましょう。ついでに階層の深さも追加します。
WITH RECURSIVE cte AS ( -- 最上位階層 SELECT id, name, parent_id, order_no, name AS fullname, -- この列を追加（最上位は自分の名前のみ） 1 AS depth -- 階層の深さ FROM departments WHERE parent_id IS NULL UNION ALL -- 子階層 SELECT c.id, c.name, c.parent_id, c.order_no, p.fullname || ' > ' || c.name, -- 親の名前と連結 p.depth + 1 -- 親階層 + 1 FROM departments c INNER JOIN cte p ON (p.id = c.parent_id) ) SELECT id, fullname, parent_id, order_no, depth FROM cte;
結果
| id | fullname | parent_id | order_no | depth |
|---|---|---|---|---|
| 1 | 管理本部 | 1 | 1 | |
| 2 | 営業本部 | 2 | 1 | |
| 3 | 開発本部 | 3 | 1 | |
| 101 | 管理本部 > 総務人事部 | 1 | 1 | 2 |
| 102 | 管理本部 > 財務部 | 1 | 2 | 2 |
| 103 | 管理本部 > 情報システム室 | 1 | 3 | 2 |
| 202 | 営業本部 > 営業部 | 2 | 2 | 2 |
| 201 | 営業本部 > 営業企画部 | 2 | 1 | 2 |
| 302 | 開発本部 > 空手部 | 3 | 2 | 2 |
| 301 | 開発本部 > 商品開発部 | 3 | 1 | 2 |
| 10102 | 管理本部 > 総務人事部 > 人事課 | 101 | 2 | 3 |
| 10101 | 管理本部 > 総務人事部 > 総務課 | 101 | 1 | 3 |
| 10201 | 管理本部 > 財務部 > 経理課 | 102 | 1 | 3 |
| 20101 | 営業本部 > 営業企画部 > 営業企画課 | 201 | 1 | 3 |
| 20201 | 営業本部 > 営業部 > 大阪営業所 | 202 | 1 | 3 |
| 20202 | 営業本部 > 営業部 > 東京営業所 | 202 | 2 | 3 |
| 20203 | 営業本部 > 営業部 > 名古屋営業所 | 202 | 3 | 3 |
| 30102 | 開発本部 > 商品開発部 > 2課 | 301 | 2 | 3 |
| 30101 | 開発本部 > 商品開発部 > 1課 | 301 | 1 | 3 |
| 3010201 | 開発本部 > 商品開発部 > 2課 > 第一小隊 | 30102 | 1 | 4 |
| 3010202 | 開発本部 > 商品開発部 > 2課 > 第二小隊 | 30102 | 2 | 4 |
Step 5. 順番に並べる
だいぶいい感じになってきましたが、ORDER BY をしていないのでちゃんと順番に並んでいないですね。同じ階層でかたまってしまっているのですが、最初にあげた組織図のようにツリー状に並べたいです。
order_no という列に順番を持たせていますが、同じ親をもつ部署間で 1から振っています。これをそのまま使っても思ったように並びませんよね...
WITH RECURSIVE cte AS ( -- 最上位階層 SELECT id, name, parent_id, order_no, name AS fullname, -- ソート順 00 書式文字列化 lpad(cast(order_no as varchar), 2, '0') AS full_order_no FROM departments WHERE parent_id IS NULL UNION ALL -- 子階層 SELECT c.id, c.name, c.parent_id, c.order_no, p.fullname || ' > ' || c.name, -- 親のソート順と連結 p.full_order_no || '/' || lpad(cast(c.order_no as varchar), 2, '0') FROM departments c INNER JOIN cte p ON (p.id = c.parent_id) ) SELECT id, fullname, parent_id, order_no, full_order_no FROM cte ORDER BY full_order_no;
結果
| id | fullname | parent_id | order_no | full_order_no |
|---|---|---|---|---|
| 1 | 管理本部 | 1 | 01 | |
| 101 | 管理本部 > 総務人事部 | 1 | 1 | 01/01 |
| 10101 | 管理本部 > 総務人事部 > 総務課 | 101 | 1 | 01/01/01 |
| 10102 | 管理本部 > 総務人事部 > 人事課 | 101 | 2 | 01/01/02 |
| 102 | 管理本部 > 財務部 | 1 | 2 | 01/02 |
| 10201 | 管理本部 > 財務部 > 経理課 | 102 | 1 | 01/02/01 |
| 103 | 管理本部 > 情報システム室 | 1 | 3 | 01/03 |
| 2 | 営業本部 | 2 | 02 | |
| 201 | 営業本部 > 営業企画部 | 2 | 1 | 02/01 |
| 20101 | 営業本部 > 営業企画部 > 営業企画課 | 201 | 1 | 02/01/01 |
| 202 | 営業本部 > 営業部 | 2 | 2 | 02/02 |
| 20201 | 営業本部 > 営業部 > 大阪営業所 | 202 | 1 | 02/02/01 |
| 20202 | 営業本部 > 営業部 > 東京営業所 | 202 | 2 | 02/02/02 |
| 20203 | 営業本部 > 営業部 > 名古屋営業所 | 202 | 3 | 02/02/03 |
| 3 | 開発本部 | 3 | 03 | |
| 301 | 開発本部 > 商品開発部 | 3 | 1 | 03/01 |
| 30101 | 開発本部 > 商品開発部 > 1課 | 301 | 1 | 03/01/01 |
| 30102 | 開発本部 > 商品開発部 > 2課 | 301 | 2 | 03/01/02 |
| 3010201 | 開発本部 > 商品開発部 > 2課 > 第一小隊 | 30102 | 1 | 03/01/02/01 |
| 3010202 | 開発本部 > 商品開発部 > 2課 > 第二小隊 | 30102 | 2 | 03/01/02/02 |
| 302 | 開発本部 > 空手部 | 3 | 2 | 03/02 |
というわけで親の階層からすべて連結した並び順文字列 full_order_no をつくってソートしました。
- 階層を
/で区切る - それぞれの階層の並び順は 2桁 0埋めフォーマットする
- 結果、数値ではなく文字列となる
サンプルなので今回は 2桁固定にしましたが、ここは部署数の要件によります。
1/12/5 のようにそのままの桁数で連結しなかった理由は文字列のソートだからです。
たとえば
| 順番文字列 |
|---|
| 1 |
| 2 |
| 9 |
| 11 |
| 12 |
| 20 |
| 100 |
こんなデータがあったときに数値だと昇順ソートしても上の表と同じ並び方ですよね。でも数字の文字列をソートすると
| 順番文字列 |
|---|
| 1 |
| 11 |
| 12 |
| 100 |
| 2 |
| 20 |
| 9 |
こうなってしまいます。これを回避するために 0埋めフォーマットしたわけです。
めでたく全階層データの表示はできたのでゴールは達成しましたが、もうちょっと試してみたいことがあります。
応用編
Step 6. 抽出もしてみたい
せっかくなのでデータの抽出もしたいですよね。営業部以下の部署を抽出したいのですが WHERE 句にどんな条件を指定すればいい？ 親・子ぐらいまでは指定できそうな気もしますが、孫、それよりも下となると...
今回は PostgreSQL なので配列型データを使ってみましょう（データ型としての配列や JSON、XML などはデータベースによって実装が異なります）。
WITH RECURSIVE cte AS ( -- 最上位階層 SELECT id, name, parent_id, order_no, name AS fullname, lpad(cast(order_no as varchar), 2, '0') AS full_order_no, -- 配列に id を入れる ARRAY[id] AS ids FROM departments WHERE parent_id IS NULL UNION ALL -- 子階層 SELECT c.id, c.name, c.parent_id, c.order_no, p.fullname || ' > ' || c.name, p.full_order_no || '/' || lpad(cast(c.order_no as varchar), 2, '0'), -- id 配列に追加 p.ids || ARRAY[c.id] FROM departments c INNER JOIN cte p ON (p.id = c.parent_id) ) SELECT id, fullname, parent_id, order_no, full_order_no, ids FROM cte WHERE ids @> ARRAY[202] -- id: 202 の部署以下を抽出 ORDER BY full_order_no;
結果
| id | fullname | parent_id | order_no | full_order_no | ids（配列） |
|---|---|---|---|---|---|
| 202 | 営業本部 > 営業部 | 2 | 2 | 02/02 | {2,202} |
| 20201 | 営業本部 > 営業部 > 大阪営業所 | 202 | 1 | 02/02/01 | {2,202,20201} |
| 20202 | 営業本部 > 営業部 > 東京営業所 | 202 | 2 | 02/02/02 | {2,202,20202} |
| 20203 | 営業本部 > 営業部 > 名古屋営業所 | 202 | 3 | 02/02/03 | {2,202,20203} |
ARRAY を使うと配列が作れます。まずは最上位階層の id を配列に追加。
ARRAY[id] AS ids
そして再帰的に子の id を追加していきます。|| 演算子で配列の連結ができます。
p.ids || ARRAY[c.id]
そうするとこんな感じで親のidから順番に追加された配列ができあがります。
{2,202,20201}
最後に WHERE 句で指定。
WHERE ids @> ARRAY[202] -- id: 202 の部署以下を抽出
@> が「包含する」なので 202 の id が含まれる配列データを対象に抽出できたということになります。便利ですね。
PostgreSQL の配列についてはこちらを
9.17. 配列関数と演算子
Step 7. ツリー表示もしてみたい
システムを作るときには表示はフロントエンドでいい感じにすると思います。
でもデータベースを直接見て分析するときとかに、ツリー表示されているとわかりやすいですよね。
こんな感じに
| 部署 |
|---|
| └管理本部 |
| └総務人事部 |
| └総務課 |
| └人事課 |
| └財務部 |
| └経理課 |
これは簡単に実現できます。
WITH RECURSIVE cte AS ( -- 最上位階層 SELECT id, name, parent_id, order_no, lpad(cast(order_no as varchar), 2, '0') AS full_order_no, 1 depth FROM departments WHERE parent_id IS NULL UNION ALL -- 子階層 SELECT c.id, c.name, c.parent_id, c.order_no, p.full_order_no || '/' || lpad(cast(c.order_no as varchar), 2, '0'), p.depth + 1 FROM departments c INNER JOIN cte p ON (p.id = c.parent_id) ) SELECT id, repeat(' ', depth - 1) || '└' || name tree_name, -- ここ parent_id, depth, full_order_no FROM cte ORDER BY full_order_no;
結果
| id | tree_name | parent_id | depth | full_order_no |
|---|---|---|---|---|
| 1 | └管理本部 | 1 | 01 | |
| 101 | └総務人事部 | 1 | 2 | 01/01 |
| 10101 | └総務課 | 101 | 3 | 01/01/01 |
| 10102 | └人事課 | 101 | 3 | 01/01/02 |
| 102 | └財務部 | 1 | 2 | 01/02 |
| 10201 | └経理課 | 102 | 3 | 01/02/01 |
| 103 | └情報システム室 | 1 | 2 | 01/03 |
| 2 | └営業本部 | 1 | 02 | |
| 201 | └営業企画部 | 2 | 2 | 02/01 |
| 20101 | └営業企画課 | 201 | 3 | 02/01/01 |
| 202 | └営業部 | 2 | 2 | 02/02 |
| 20201 | └大阪営業所 | 202 | 3 | 02/02/01 |
| 20202 | └東京営業所 | 202 | 3 | 02/02/02 |
| 20203 | └名古屋営業所 | 202 | 3 | 02/02/03 |
| 3 | └開発本部 | 1 | 03 | |
| 301 | └商品開発部 | 3 | 2 | 03/01 |
| 30101 | └1課 | 301 | 3 | 03/01/01 |
| 30102 | └2課 | 301 | 3 | 03/01/02 |
| 3010201 | └第一小隊 | 30102 | 4 | 03/01/02/01 |
| 3010202 | └第二小隊 | 30102 | 4 | 03/01/02/02 |
| 302 | └空手部 | 3 | 2 | 03/02 |
Step 4. のときに階層の深さ（depth）を取得していました。その数値に応じて前に空白を足すだけですね。
repeat(' ', depth - 1) || '└' || name tree_name,
Step 8. まとめ
ここまでのをまとめるとこんな感じです。たった 4列の departments テーブルひとつでこんな結果が作れるんだから SQL って面白いですよね。
WITH RECURSIVE cte AS ( -- 最上位階層 SELECT id, name, parent_id, order_no, name AS fullname, 1 depth, lpad(cast(order_no as varchar), 2, '0') full_order_no, ARRAY[id] ids FROM departments WHERE parent_id IS NULL UNION ALL -- 子階層 SELECT c.id, c.name, c.parent_id, c.order_no, p.fullname || ' > ' || c.name, p.depth + 1, p.full_order_no || '/' || lpad(cast(c.order_no as varchar), 2, '0'), p.ids || ARRAY[c.id] FROM departments c INNER JOIN cte p ON (p.id = c.parent_id) ) SELECT id, name, repeat(' ', depth - 1) || '└' || name tree_name, fullname, parent_id, order_no, depth, full_order_no, ids FROM cte --WHERE ids @> ARRAY[202] ORDER BY full_order_no;
結果
| id | name | tree_name | fullname | parent_id | order_no | depth | full_order_no | ids |
|---|---|---|---|---|---|---|---|---|
| 1 | 管理本部 | └管理本部 | 管理本部 | 1 | 1 | 01 | {1} | |
| 101 | 総務人事部 | └総務人事部 | 管理本部 > 総務人事部 | 1 | 1 | 2 | 01/01 | {1,101} |
| 10101 | 総務課 | └総務課 | 管理本部 > 総務人事部 > 総務課 | 101 | 1 | 3 | 01/01/01 | {1,101,10101} |
| 10102 | 人事課 | └人事課 | 管理本部 > 総務人事部 > 人事課 | 101 | 2 | 3 | 01/01/02 | {1,101,10102} |
| 102 | 財務部 | └財務部 | 管理本部 > 財務部 | 1 | 2 | 2 | 01/02 | {1,102} |
| 10201 | 経理課 | └経理課 | 管理本部 > 財務部 > 経理課 | 102 | 1 | 3 | 01/02/01 | {1,102,10201} |
| 103 | 情報システム室 | └情報システム室 | 管理本部 > 情報システム室 | 1 | 3 | 2 | 01/03 | {1,103} |
| 2 | 営業本部 | └営業本部 | 営業本部 | 2 | 1 | 02 | {2} | |
| 201 | 営業企画部 | └営業企画部 | 営業本部 > 営業企画部 | 2 | 1 | 2 | 02/01 | {2,201} |
| 20101 | 営業企画課 | └営業企画課 | 営業本部 > 営業企画部 > 営業企画課 | 201 | 1 | 3 | 02/01/01 | {2,201,20101} |
| 202 | 営業部 | └営業部 | 営業本部 > 営業部 | 2 | 2 | 2 | 02/02 | {2,202} |
| 20201 | 大阪営業所 | └大阪営業所 | 営業本部 > 営業部 > 大阪営業所 | 202 | 1 | 3 | 02/02/01 | {2,202,20201} |
| 20202 | 東京営業所 | └東京営業所 | 営業本部 > 営業部 > 東京営業所 | 202 | 2 | 3 | 02/02/02 | {2,202,20202} |
| 20203 | 名古屋営業所 | └名古屋営業所 | 営業本部 > 営業部 > 名古屋営業所 | 202 | 3 | 3 | 02/02/03 | {2,202,20203} |
| 3 | 開発本部 | └開発本部 | 開発本部 | 3 | 1 | 03 | {3} | |
| 301 | 商品開発部 | └商品開発部 | 開発本部 > 商品開発部 | 3 | 1 | 2 | 03/01 | {3,301} |
| 30101 | 1課 | └1課 | 開発本部 > 商品開発部 > 1課 | 301 | 1 | 3 | 03/01/01 | {3,301,30101} |
| 30102 | 2課 | └2課 | 開発本部 > 商品開発部 > 2課 | 301 | 2 | 3 | 03/01/02 | {3,301,30102} |
| 3010201 | 第一小隊 | └第一小隊 | 開発本部 > 商品開発部 > 2課 > 第一小隊 | 30102 | 1 | 4 | 03/01/02/01 | {3,301,30102,3010201} |
| 3010202 | 第二小隊 | └第二小隊 | 開発本部 > 商品開発部 > 2課 > 第二小隊 | 30102 | 2 | 4 | 03/01/02/02 | {3,301,30102,3010202} |
| 302 | 空手部 | └空手部 | 開発本部 > 空手部 | 3 | 2 | 2 | 03/02 | {3,302} |
おまけ
ちなみにこんなのも SQL で作れます。
下に兄弟がないときはツリーの縦線 | をなくしたり +- を `- にしてます。
どうやって実現するか？気になる答えはまた次の機会に！
| id | tree_nmae |
|---|---|
| 1 | +- 管理本部 |
| 101 | | +- 総務人事部 |
| 10101 | | | +- 総務課 |
| 10102 | | | `- 人事課 |
| 102 | | +- 財務部 |
| 10201 | | | `- 経理課 |
| 103 | | `- 情報システム室 |
| 2 | +- 営業本部 |
| 201 | | +- 営業企画部 |
| 20101 | | | `- 営業企画課 |
| 202 | | `- 営業部 |
| 20201 | | +- 大阪営業所 |
| 20202 | | +- 東京営業所 |
| 20203 | | `- 名古屋営業所 |
| 3 | `- 開発本部 |
| 301 | +- 商品開発部 |
| 30101 | | +- 1課 |
| 30102 | | `- 2課 |
| 3010201 | | +- 第一小隊 |
| 3010202 | | `- 第二小隊 |
| 302 | `- 空手部 |
