PostgreSQLで階層マスタを扱う

2019/03/03Database

とある案件で階層マスタを扱うことがありました。

DBがOracleの場合だと、「START WITH 〜 CONNECT BY PRIOR 〜」という、階層問い合わせの構文があるのですが、PostgreSQLだと階層問い合わせが出来るのか知らなかったので調べてみました。
(なお、MySQLでは使えません)

1. データを準備する

以下の様な階層構造を考えます。

レベル0 レベル1 レベル2
勘定科目
資産科目
現金
預金
負債科目
借入金
預り金
資本科目
資本金
利益剰余金


テーブル定義を以下の通りとします。

列名 内容
id int 主キー
code varchar(20) 科目コード
name varchar(100) 科目名
parent_id int 親科目の主キー (外部キー)


DDLは以下の通りです。

CREATE TABLE kamoku (
  id int primary key,
  code varchar(20),
  name varchar(100),
  parent_id int references kamoku(id)
);

INSERT文は以下になります。

INSERT INTO kamoku (id, code, name, parent_id) VALUES (1, 'A', '勘定科目', NULL);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (2, 'A01', '資産科目', 1);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (3, 'A02', '負債科目', 1);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (4, 'A03', '資本科目', 1);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (5, 'A0101', '現金', 2);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (6, 'A0102', '預金', 2);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (7, 'A0201', '借入金', 3);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (8, 'A0202', '預り金', 3);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (9, 'A0301', '資本金', 4);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (10, 'A0302', '利益剰余金', 4);

こうして作ったデータをSELECTすると、以下の結果になります。

hoge=# select * from kamoku;

id code name parent_id
1 A 勘定科目
2 A01 資産科目 1
3 A02 負債科目 1
4 A03 資本科目 1
5 A0101 現金 2
6 A0102 預金 2
7 A0201 借入金 3
8 A0202 預り金 3
9 A0301 資本金 4
10 A0302 利益剰余金 4

(10 rows)

2. WITH RECURSIVE句による階層問い合わせ

PostgreSQLにはWITH句というサブクエリを定義する句が使えます。
ここに、RECURSIVEを付けると、再帰的なサブクエリを書くことが出来ます。

具体的には、以下の様に書きます。

WITH RECURSIVE child (level, id, code, name, parent_id) AS (
  SELECT 0, kamoku.id, kamoku.code, kamoku.name, kamoku.parent_id FROM kamoku WHERE kamoku.id = 1
UNION ALL
  SELECT
    child.level + 1,
    kamoku.id,
    kamoku.code,
    kamoku.name,
    kamoku.parent_id
  FROM
    kamoku, child
  WHERE
    kamoku.parent_id = child.id)
SELECT level, id, code, name, parent_id FROM child ORDER BY code;

結果は以下の通りです。

level id code name parent_id
0 1 A 勘定科目
1 2 A01 資産科目 1
2 5 A0101 現金 2
2 6 A0102 預金 2
1 3 A02    負債科目 1
2 7 A0201 借入金 3
2 8 A0202 預り金 3
1 4 A03    資本科目 1
2 9 A0301 資本金 4
2 10 A0302 利益剰余金 4

(10 rows)

2行目のSELECT文のWHERE句の条件が、開始のidになります。
開始のidを指定すれば、途中から取得することも出来ます。

WITH RECURSIVE child (level, id, code, name, parent_id) AS (
  SELECT 0, kamoku.id, kamoku.code, kamoku.name, kamoku.parent_id FROM kamoku WHERE kamoku.id = 2
UNION ALL
  SELECT
    child.level + 1,
    kamoku.id,
    kamoku.code,
    kamoku.name,
    kamoku.parent_id
  FROM
    kamoku, child
  WHERE
    kamoku.parent_id = child.id)
SELECT level, id, code, name, parent_id FROM child ORDER BY code;

level id code name parent_id
0 2 A01 資産科目 1
1 5 A0101 現金 2
1 6 A0102 預金 2

(3 rows)

逆順(子→親)も出来ます。

WITH RECURSIVE child (level, id, code, name, parent_id) AS (
  SELECT 0, kamoku.id, kamoku.code, kamoku.name, kamoku.parent_id FROM kamoku WHERE kamoku.id = 10
UNION ALL
  SELECT
    child.level + 1,
    kamoku.id,
    kamoku.code,
    kamoku.name,
    kamoku.parent_id
  FROM
    kamoku, child
  WHERE
    child.parent_id = kamoku.id)
SELECT level, id, code, name, parent_id FROM child ORDER BY code DESC;

level id code name parent_id
0 10 A0302 利益剰余金 4
1 4 A03 資本科目 1
2 1 A 勘定科目

(3 rows)

3. まとめと落とし穴

今回の階層問い合わせを再帰的に行うクエリだと、1クエリで出来て速度も速いので良い方法だと思います。

ですが、各階層(level)毎に取得している為、最後に並び替えをしないと、ツリー状に並ぶ結果が返ってきません。
今回は簡単にするために、科目コード(code)にルールを作って並ぶようにしています。

実際に使う時は、もう一工夫したいところです。