テーブル結合にLIKE句を用いて結合条件を部分一致としてみた結果と問題点
従業員の住所から全国地方公共団体コードを特定する作業を行った際に思い付きでやった方法が意外とうまくハマったのでメモ。
〇目的:従業員の住所から全国地方公共団体コードを割り当てる
〇テーブル定義
-- employee address
create table emp_addr(
emp_code integer,
addr text);
-- lg code https://www.soumu.go.jp/main_content/000730858.xlsxcreate table lg_code_list(
lg_code integer,
pref_name text, -- 都道府県名
muni_name text, -- 市町村名
pref_kana text,
muni_kana text);
〇実際のコード
select * from emp_addr as a
left outer join lg_code_list as b
on a.addr like b.pref_name || b.muni_name || '%';
たったこれだけで部分一致によるテーブル結合が実現します。
問題を回避する
ただし、住所情報に欠落や誤りがある場合は個別対応が必要です。
元データを訂正できればよいですが、できない場合は以下の通りとすることもできます。
〇特定の条件を結合させないケース
select * from emp_addr as a
left outer join lg_code_list as b
on a.addr like b.pref_name || b.muni_name || '%'
and case when b.pref_name || b.muni_name = '東京都町田市' then false
else true end;
〇特定の条件を他の条件へ置き換えるケース
select * from emp_addr as a
left outer join lg_code_list as b
on a.addr like
case when b.pref_name || b.muni_name = '東京都町田市' then '神奈川県町田市%'
else b.pref_name || b.muni_name || '%' end;