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

まえがき

前回の宿題の１つが解決できたのでメモを残す記事です。前回の宿題とは、

部署テキストの途中から他の部署名を含むという場合(e.g. システム開発第一部 vs 老朽化更改システム開発第一部 のような部署テキストが存在する場合、システム開発第一部 ⊃ 老朽化更改システム開発第一部)となってしまう)、これはおそらく正しく無いと思うものの、今の所これを回避する方法が分からない。

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

です。*1

今回は、前回と同じデータに「老朽化更改システム開発第一部」を加えた際、「システム開発第一部 ⊃ 老朽化更改システム開発第一部」とならないようなwhere句の作り方を書いてみます。

ソースコード

前回と同じスタイルで作成します。

実行環境

DB Fiddle の MySQL v8.0 です。

テーブル作成

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

データ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','経理部第二課');
    insert into DeptList values ('10301','老朽化更改システム開発第一部');   -- 今回追加したデータ
    insert into DeptList values('10311','外部委託システム運用管理部');  -- 同様に今回追加したデータ

部署の紐付け

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(concat('dummy',a.deptText))  >  length(replace(concat('dummy',a.deptText), concat('dummy',b.deptText), ''))
    ;

結果確認

今回は、「老朽化更改システム開発第一部」と「外部委託システム運用管理部」がそれぞれどのように紐付けられたかを確認します。

select文

select 
    * 
from 
    dept0
where 
    srcNum in ('10301','10311') 
order by 
    srcNum desc
;

実行結果

srcNum	srcText	dstNum	dstText
10311	外部委託システム運用管理部	10311	外部委託システム運用管理部
10301	老朽化更改システム開発第一部	10301	老朽化更改システム開発第一部

前回の抽出ロジックの場合

select文

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

    select 
        * 
    from 
        dept0
    where 
        srcNum in ('10301','10311') 
    order by 
        srcNum desc
    ;

実行結果

srcNum	srcText	dstNum	dstText
10311	外部委託システム運用管理部	1012	運用管理部
10311	外部委託システム運用管理部	10311	外部委託システム運用管理部
10301	老朽化更改システム開発第一部	1000	システム開発第一部
10301	老朽化更改システム開発第一部	10301	老朽化更改システム開発第一部

考察

where句はlength(concat('dummy',a.deptText)) > length(replace(concat('dummy',a.deptText), concat('dummy',b.deptText), ''))です。テキストの頭にdummyなる文字列を連結してみました。これを付ける場合と付けない場合を比較すると、下記のようになります。

a	b	replaceできる？	replaceすると？	replace後の文字数は？	replace前後で文字数を比較すると？	文字数比較の真偽は？
老朽化更改システム開発第一部	システム開発第一部	yes	老朽化更改	5	14 > 5	true
dummy老朽化更改システム開発第一部	dummyシステム開発第一部	no	dummy老朽化更改システム開発第一部	19	19 > 19	false

なんとも見辛い表ですが、、テキストの頭にdummyを追加することによって、中間一致から前方一致することができた、ということです。

*1:「開発」が抜けていたので、追記箇所を太字で補いました。