axjack's blog

axjack is said to be an abbreviation for An eXistent JApanese Cool Klutz.

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

はじめに

タイトルの詳しい説明

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

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

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

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

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

どうやるか?

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

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

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

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

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

なとき、

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

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

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

なとき、

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

なとき、

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

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

ソース

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

実行環境

DB FiddleMySQL 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 経理部第二課

感想

良い点

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

改善すべき点

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

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