非階層的な部署コードと階層的な部署テキストを持つテーブルから、部署テキストで階層構造を導出する。

はじめに

タイトルの詳しい説明

日本語って難しいですね。非階層的な部署コードと階層的な部署テキストを持つテーブルとは、以下のようなテーブルを意味します。

部署コード	部署テキスト
1000	システム開発第一部
10001	システム開発第一部第一課
10002	システム開発第一部第二課
10003	システム開発第一部第三課
10004	システム開発第一部第三課一係
10005	システム開発第一部第三課二係
1006	システム開発第二部
1007	システム開発第二部国内課
1008	システム開発第二部海外課
1009	システム開発第二部海外課アジア係
1010	システム開発第二部海外課ヨーロッパ係

部署コードがシーケンシャルのようなそうでもないような微妙な付け方をしている一方で、部署テキストはなんとなく

┏システム開発第一部  
┃　┣システム開発第一部第一課
┃　┣システム開発第一部第二課
┃　┗システム開発第一部第三課
┃　　　┣システム開発第一部第三課一係
┃　　　┣システム開発第一部第三課二係
┃　　　┗システム開発第一部第三課二係
┃
┗システム開発第二部
　　┣システム開発第二部国内課
　　┗システム開発第二部海外課
　　　　┣システム開発第二部海外課アジア係
　　　　┗システム開発第二部海外課アジア係

のように階層化できそうですよね。ということで、SQLにて部署テキストで階層構造を導出することにします。

どうやるか？

日本語で書くと以下の２ステップを踏みます。

• テーブルを自己結合する
  • 同じテーブルを別名で二つ指定するので、便宜上A・Bと名付ける
• 以下の条件を満たすレコードを抽出する
  • Aの部署名をBの部署名で''(ブランク)に置換し(★1)
  • Aの部署名の長さ が ★1の長さより大きい

なぜこのように抽出すると(一見)うまくいくのか？

置換が成功する例を見てみます。

A.部署名 = システム開発第一部第一課
B.部署名 = システム開発第一部

なとき、

・Aの部署名をBの部署名で''(ブランク)に置換し(★1)
　→　「システム開発第一部第一課」が「第一課」と置換されます。
・ Aの部署名の長さ が ★1の長さより大きい
　→　12文字 vs 3文字 なので条件を満たす

一方、置換が失敗する例はというと、

----------【例１】----------
A.部署名 = システム開発第一部
B.部署名 = システム開発第一部第一課

なとき、

・Aの部署名をBの部署名で''(ブランク)に置換し(★1)
　→　置換ができないので「システム開発第一部」のままとなります
・ Aの部署名の長さ が ★1の長さより大きい
　→　12文字 vs 12文字 なので条件を満たさない

----------【例２】----------
A.部署名 = システム開発第一部第一課
B.部署名 = システム開発第二部

なとき、

・Aの部署名をBの部署名で''(ブランク)に置換し(★1)
　→　置換ができないので「システム開発第一部第一課」のままとなります
・ Aの部署名の長さ が ★1の長さより大きい
　→　12文字 vs 12文字 なので条件を満たさない

となるので、(一見)うまくいきます。

ソース

実際にテーブルを作ってSELECT文を実行してみます。

実行環境

DB Fiddle の MySQL v8.0 にて実行しました。

テーブル作成

    --部署テーブル
    create table  DeptList (
      deptNum varchar(256) -- 部署コード
      ,deptText varchar(256) -- 部署テキスト
    );

データinsert

階層的で無い(i.e. 子を持たない部)データも加えてinsertします。

    --データ投入
    insert into DeptList values('1000','システム開発第一部');
    insert into DeptList values('10001','システム開発第一部第一課');
    insert into DeptList values('10002','システム開発第一部第二課');
    insert into DeptList values('10003','システム開発第一部第三課');
    insert into DeptList values('10004','システム開発第一部第三課一係');
    insert into DeptList values('10005','システム開発第一部第三課二係');
    insert into DeptList values('1006','システム開発第二部');
    insert into DeptList values('1007','システム開発第二部国内課');
    insert into DeptList values('1008','システム開発第二部海外課');
    insert into DeptList values('1009','システム開発第二部海外課アジア係');
    insert into DeptList values('1010','システム開発第二部海外課ヨーロッパ係');
    insert into DeptList values('1011','品質管理部');
    insert into DeptList values('1012','運用管理部');
    insert into DeptList values('1013','運用管理部総務課');
    insert into DeptList values('1014','総務部');
    insert into DeptList values('1015','人事部');
    insert into DeptList values('10151','人事部人事課');
    insert into DeptList values('1016','総務部給与課');
    insert into DeptList values('1017','企画室');
    insert into DeptList values('1018','企画室企画課');
    insert into DeptList values('1019','企画室企画課第一企画係');
    insert into DeptList values('10191','企画室企画課第二企画係');
    insert into DeptList values('10192','企画室企画課第三企画係');
    insert into DeptList values('1022','経理部第一課');
    insert into DeptList values('1029','経理部第二課');

部署の紐付け

    create temporary table dept0
    select
        a.deptNum as srcNum --紐付け元の部署コード
        , a.deptText as srcText --紐付け元の部署テキスト
        , b.deptNum as dstNum --紐付け先の部署コード
        , b.deptText as dstText --紐付け先の部署テキスト
    from 
        DeptList as a
        , DeptList as b
    where 
         length(a.deptText)  >  length(replace(a.deptText, b.deptText, ''))
    ;

結果確認

なお、結果の解釈は「左側(srcNum, srcText)は右側(dstNum, dstText)の配下にある」です。

select * from dept0;

srcNum	srcText	dstNum	dstText
1000	システム開発第一部	1000	システム開発第一部
10001	システム開発第一部第一課	1000	システム開発第一部
10002	システム開発第一部第二課	1000	システム開発第一部
10003	システム開発第一部第三課	1000	システム開発第一部
10004	システム開発第一部第三課一係	1000	システム開発第一部
10005	システム開発第一部第三課二係	1000	システム開発第一部
10001	システム開発第一部第一課	10001	システム開発第一部第一課
10002	システム開発第一部第二課	10002	システム開発第一部第二課
10003	システム開発第一部第三課	10003	システム開発第一部第三課
10004	システム開発第一部第三課一係	10003	システム開発第一部第三課
10005	システム開発第一部第三課二係	10003	システム開発第一部第三課
10004	システム開発第一部第三課一係	10004	システム開発第一部第三課一係
10005	システム開発第一部第三課二係	10005	システム開発第一部第三課二係
1006	システム開発第二部	1006	システム開発第二部
1007	システム開発第二部国内課	1006	システム開発第二部
1008	システム開発第二部海外課	1006	システム開発第二部
1009	システム開発第二部海外課アジア係	1006	システム開発第二部
1010	システム開発第二部海外課ヨーロッパ係	1006	システム開発第二部
1007	システム開発第二部国内課	1007	システム開発第二部国内課
1008	システム開発第二部海外課	1008	システム開発第二部海外課
1009	システム開発第二部海外課アジア係	1008	システム開発第二部海外課
1010	システム開発第二部海外課ヨーロッパ係	1008	システム開発第二部海外課
1009	システム開発第二部海外課アジア係	1009	システム開発第二部海外課アジア係
1010	システム開発第二部海外課ヨーロッパ係	1010	システム開発第二部海外課ヨーロッパ係
1011	品質管理部	1011	品質管理部
1012	運用管理部	1012	運用管理部
1013	運用管理部総務課	1012	運用管理部
1013	運用管理部総務課	1013	運用管理部総務課
1014	総務部	1014	総務部
1016	総務部給与課	1014	総務部
1015	人事部	1015	人事部
10151	人事部人事課	1015	人事部
10151	人事部人事課	10151	人事部人事課
1016	総務部給与課	1016	総務部給与課
1017	企画室	1017	企画室
1018	企画室企画課	1017	企画室
1019	企画室企画課第一企画係	1017	企画室
10191	企画室企画課第二企画係	1017	企画室
10192	企画室企画課第三企画係	1017	企画室
1018	企画室企画課	1018	企画室企画課
1019	企画室企画課第一企画係	1018	企画室企画課
10191	企画室企画課第二企画係	1018	企画室企画課
10192	企画室企画課第三企画係	1018	企画室企画課
1019	企画室企画課第一企画係	1019	企画室企画課第一企画係
10191	企画室企画課第二企画係	10191	企画室企画課第二企画係
10192	企画室企画課第三企画係	10192	企画室企画課第三企画係
1022	経理部第一課	1022	経理部第一課
1029	経理部第二課	1029	経理部第二課

感想

良い点

• ひとまず１階層分( e.g. システム開発第一部 ⊃ システム開発第一部第一課) を紐づけることができた。
• 自分は自分自身に含まれる(e.g 企画室企画課 は 企画室企画課を含む) 結果となっているが、これはwhere句を弄れば抽出対象から外すことができる。

改善すべき点

一見 と付けた理由を書き残します。

• もし部署テキストが２階層・３階層…と続いてしまう場合、階層数が分からない場合、この手法では物足りなさが残る。
• 部署テキストの途中から他の部署名を含むという場合(e.g. システム第一部 vs 老朽化更改システム第一部 のような部署テキストが存在する場合、システム第一部 ⊃ 老朽化更改システム第一部)となってしまう)、これはおそらく正しく無いと思うものの、今の所これを回避する方法が分からない。