こんにちは、knsk765 です。

以前、階層構造をデータベースで表現する方法について記事を書きました。今回はその番外編です。

こんな階層構造の部署データがあります。これを 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	`- 空手部

準備
実践

準備

実行環境

PostgreSQL 14.5

テーブル

以前の記事からの再掲になりますが、部署テーブルを作成してデータ投入。

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
);

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);

前提知識

CTE（共通テーブル式）
再帰クエリー
ウィンドウ関数

CTE、再帰クエリー、ウィンドウ関数についてはこちらの記事でも書いているのであわせて見てみていただければ。

blog.ingage.jp

実践

さくっと回答から。

WITH RECURSIVE ordered AS (
  -- 兄弟の番号付け
  SELECT
    id, name, parent_id, order_no,
    lpad(cast(order_no as varchar), 2, '0') AS order_no_text,
    row_number() OVER (
      PARTITION BY parent_id ORDER BY order_no DESC) AS row_no_desc
  FROM departments
),
hierarchy AS (
  -- 1階層目
  SELECT
    row_no_desc,
    '' AS parent_line,
    (CASE row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    id, name, parent_id, order_no,
    order_no_text AS full_order_no
  FROM ordered WHERE parent_id IS NULL
  UNION ALL
  -- 2階層目以降
  SELECT
    c.row_no_desc,
    (
      p.parent_line ||
      (CASE p.row_no_desc WHEN 1 THEN '   ' ELSE '|  ' END)
    ) AS parent_line,
    (CASE c.row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    c.id, c.name, c.parent_id, c.order_no,
    (p.full_order_no || '/' || c.order_no_text) AS full_order_no
  FROM
    ordered c
    INNER JOIN hierarchy p ON (p.id = c.parent_id)
)
SELECT
  id,
  (parent_line || tree_line || name) AS tree_name
FROM hierarchy ORDER BY full_order_no;

Step 1. 兄弟の番号付け

一番最初の CTE（ordered）で何をしているかから分解して解説します。

WITH RECURSIVE ordered AS (
  -- 兄弟の番号付け
  SELECT
    id, name, parent_id, order_no,
    lpad(cast(order_no as varchar), 2, '0') AS order_no_text,
    row_number() OVER (
      PARTITION BY parent_id ORDER BY order_no ASC) AS row_no_asc,
    row_number() OVER (
      PARTITION BY parent_id ORDER BY order_no DESC) AS row_no_desc
  FROM departments
)
SELECT * FROM ordered
ORDER BY parent_id NULLS FIRST, order_no;

結果

id	name	parent_id	order_no	order_no_text	row_no_asc	row_no_desc
1	管理本部		1	01	1	3
2	営業本部		2	02	2	2
3	開発本部		3	03	3	1
101	総務人事部	1	1	01	1	3
102	財務部	1	2	02	2	2
103	情報システム室	1	3	03	3	1
201	営業企画部	2	1	01	1	2
202	営業部	2	2	02	2	1
301	商品開発部	3	1	01	1	2
302	空手部	3	2	02	2	1
10101	総務課	101	1	01	1	2
10102	人事課	101	2	02	2	1
10201	経理課	102	1	01	1	1
20101	営業企画課	201	1	01	1	1
20201	大阪営業所	202	1	01	1	3
20202	東京営業所	202	2	02	2	2
20203	名古屋営業所	202	3	03	3	1
30101	1課	301	1	01	1	2
30102	2課	301	2	02	2	1
3010201	第一小隊	30102	1	01	1	2
3010202	第二小隊	30102	2	02	2	1

order_no_text はあとで全体の並び替えのために使います。
row_no_asc, row_no_desc は同じ親部署内での昇順・降順の番号（兄弟の番号）です。
row_no_desc が 1 なら末っ子ということで、この判定があとで必要になります。
（row_no_asc はここでの参考のためだけなので利用しません）

Step 2. 最上位部署の取得

hierarchy の CTE を解説します。ここは再帰クエリーになっていますが、まずは一番上の階層部分。

WITH RECURSIVE ordered AS (
  -- 兄弟の番号付け
  SELECT
    id, name, parent_id, order_no,
    lpad(cast(order_no as varchar), 2, '0') AS order_no_text,
    row_number() OVER (
      PARTITION BY parent_id ORDER BY order_no DESC) AS row_no_desc
  FROM departments
),
hierarchy AS (
  -- 1階層目
  SELECT
    row_no_desc,
    '' AS parent_line,
    (CASE row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    id, name, parent_id, order_no,
    order_no_text AS full_order_no
  FROM ordered WHERE parent_id IS NULL
)
SELECT
  id, name, parent_id, order_no, row_no_desc,
  parent_line, tree_line, full_order_no
FROM hierarchy ORDER BY full_order_no;

結果

id	tree_line	name	order_no	row_no_desc	full_order_no
1	+-	管理本部	1	3	01
2	+-	営業本部	2	2	02
3	`-	開発本部	3	1	03

tree_line のところを見ると少しイメージがわきますね。
末っ子（row_no_desc が 1）のときはツリーの末端（`-）。

Step 3. 2階層目以降の部署も取得

2階層目以降の部署も取得しつつ、どんな結果が返ってきているか詳細に分解して出してみましょう。

WITH RECURSIVE ordered AS (
  -- 兄弟の番号付け
  SELECT
    id, name, parent_id, order_no,
    lpad(cast(order_no as varchar), 2, '0') AS order_no_text,
    row_number() OVER (
      PARTITION BY parent_id ORDER BY order_no DESC) AS row_no_desc
  FROM departments
),
hierarchy AS (
  -- 1階層目
  SELECT
    row_no_desc,
    cast(1 as bigint) AS parent_row_no_desc,
    '' AS ancestor_line,
    '' AS parent_line,
    (CASE row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    id, name, parent_id, order_no,
    order_no_text AS full_order_no
  FROM ordered WHERE parent_id IS NULL
  UNION ALL
  -- 2階層目以降
  SELECT
    c.row_no_desc,
    p.row_no_desc AS parent_row_no_desc,
    p.parent_line AS ancestor_line,
    (CASE p.row_no_desc WHEN 1 THEN '   ' ELSE '|  ' END) AS parent_line,
    (CASE c.row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    c.id, c.name, c.parent_id, c.order_no,
    (p.full_order_no || '/' || c.order_no_text) AS full_order_no
  FROM
    ordered c
    INNER JOIN hierarchy p ON (p.id = c.parent_id)
)
SELECT
  id,
  ancestor_line,
  parent_row_no_desc,
  parent_line, 
  row_no_desc, tree_line,
  name,
  full_order_no
FROM hierarchy ORDER BY full_order_no;

結果

id	ancestor_line	parent_row_no_desc	parent_line	row_no_desc	tree_line	name	full_order_no
101		3	`\|`	3	`+-`	総務人事部	01/01
1		1		3	`+-`	管理本部	01
10101	`\|`	3	`\|`	2	`+-`	総務課	01/01/01
10102	`\|`	3	`\|`	1	`-	人事課	01/01/02
102		3	`\|`	2	`+-`	財務部	01/02
10201	`\|`	2	`\|`	1	`-	経理課	01/02/01
103		3	`\|`	1	`-	情報システム室	01/03
2		1		2	`+-`	営業本部	02
201		2	`\|`	2	`+-`	営業企画部	02/01
20101	`\|`	2	`\|`	1	`-	営業企画課	02/01/01
202		2	`\|`	1	`-	営業部	02/02
20201	`\|`	1		3	`+-`	大阪営業所	02/02/01
20202	`\|`	1		2	`+-`	東京営業所	02/02/02
20203	`\|`	1		1	`-	名古屋営業所	02/02/03
3		1		1	`-	開発本部	03
301		1		2	`+-`	商品開発部	03/01
30101		2	`\|`	2	`+-`	1課	03/01/01
30102		2	`\|`	1	`-	2課	03/01/02
3010201	`\|`	1		2	`+-`	第一小隊	03/01/02/01
3010202	`\|`	1		1	`-	第二小隊	03/01/02/02
302		1		1	`-	空手部	03/02

ancestor_line, parent_line, tree_line をくっつけるとツリーの線になります。

Step 4. 最終形

というわけで一番最初に書いた回答の SQL をもういちど記載。

WITH RECURSIVE ordered AS (
  -- 兄弟の番号付け
  SELECT
    id, name, parent_id, order_no,
    lpad(cast(order_no as varchar), 2, '0') AS order_no_text,
    row_number() OVER (
      PARTITION BY parent_id ORDER BY order_no DESC) AS row_no_desc
  FROM departments
),
hierarchy AS (
  -- 1階層目
  SELECT
    row_no_desc,
    '' AS parent_line,
    (CASE row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    id, name, parent_id, order_no,
    order_no_text AS full_order_no
  FROM ordered WHERE parent_id IS NULL
  UNION ALL
  -- 2階層目以降
  SELECT
    c.row_no_desc,
    (
      p.parent_line ||
      (CASE p.row_no_desc WHEN 1 THEN '   ' ELSE '|  ' END)
    ) AS parent_line,
    (CASE c.row_no_desc WHEN 1 THEN '`- ' ELSE '+- ' END) AS tree_line,
    c.id, c.name, c.parent_id, c.order_no,
    (p.full_order_no || '/' || c.order_no_text) AS full_order_no
  FROM
    ordered c
    INNER JOIN hierarchy p ON (p.id = c.parent_id)
)
SELECT
  id,
  (parent_line || tree_line || name) AS tree_name
FROM hierarchy ORDER BY full_order_no;

結果

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	`- 空手部

まあ実際の開発業務でこんなことしない（SQL じゃなくてプログラムで画面表示する）でしょう。
けど、こんな面倒なことでも SQL だけでできてしまうと知っていれば、データ分析するときにわりかし便利な整形方法を思いついたりします。
パズルっぽくて面白いので、みなさまもぜひ。

インゲージ開発者ブログ

【SQL】階層図の出力結果をツリー構造に整形する

準備