抽出用SQLの構築方法～SQL入門書からの第一歩～

初めに

前回はSQLの整理術という面を強調した記事を書いたので、
今回は我流にはなるのですが、抽出用SQLの立て方を書こうと思います。

少なくとも、私はこれがわかるようになったことで
SQL自体を書くのがだいぶ楽になりました。

統計学とか、分析観点とかの学問的な手法については門外漢なので説明できないです。
あくまでも、抽出用に使うためのSQLの作成のための記事です。

サンプル要件

住所を例に出していますが、実際の住所のデータクリーニングは難しいので注意しましょう。今回の例では、少なくともパフォーマンスは出ません。

テーブルは施設を管理している施設マスタ、住所コードの2つのテーブルとして、
施設の住所を紐づけて出力したいといったものです。

施設マスタ

shisetsu_name	address_name
ファミリーマート　エル本町店	本町4丁目7-4
殖蓮小	大阪市天王寺区上本町6-1-55

住所コード

address_name
本町
上本町

普通に入門書通りに考えると、likeによる部分一致が正しそうですね。
それではlikeでくっつけてみましょう。

select * 
  from 施設マスタ m
  left join 住所コード as c
                 on m.address_name like '%'|| c.address_name ||'%' 

shisetsu_name	address_name	address_name_1
ファミリーマート　エル本町店	本町4丁目7-4	本町
殖蓮小	大阪市天王寺区上本町6-1-55	本町
殖蓮小	大阪市天王寺区上本町6-1-55	上本町

さて、上記の抽出結果を確認すると、
殖蓮小のレコードがおかしいですね。
本来、上本町の方で出力されてほしいのに、本町でも出力されてしまいます。

では、この場合をもとに抽出するSQLを組み立てていきます。

構築方法

原因究明

さて、こういう場合はまず第一に原因だけを考えましょう。
今回の場合はシンプルに　本町　も上本町に一致しているためです。
※この原因究明の確認方法は、いつか別記事を書きます。

データ確認

ここでSQLに　戻ってはいけません！！！
戻るべきは住所コードのデータの方です。
（私はこの際に、Excelに出力することが多いです。）

住所コード

address_name
本町
上本町

さて、あなたに質問です。
上記のデータを見たときに、どちらが抽出されるべきか分かりますか？

今回はわかりやすいですね。
上本町の方です。

では、もう一回質問します。
あなたはそれを何で判断しましたか？（もしくは、本町がどうして違うとわかりましたか？）

勘？なんとなく？
いえ、今回あなたは施設マスタとの一致率で判断しているはずです。
では、一致率が高い、低いをどうやって判断しているか？
それが分かってようやくSQLを修正することができるようになります。

SQL修正

ここから先は小手先の話になります。
一致率は置換して一致した文字数が多ければ多いほど高くなります。
（今回は一致した文字数でも問題ないと思いますが、理解のために一致率として追加します。）

select
  *
  ------------------------------------------------------------------- 
  , (length(m.address_name) 
     		- length(replace (m.address_name, c.address_name, '')))::numeric 
	/ length(m.address_name) as "一致率"
  -------------------------------------------------------------------
  -- 1から採番
  , row_number() over ( 
        partition by
              m.shisetsu_name 
            order by
               -- 一致率が降順（高い順）に
              ( 
                length(m.address_name) 
                - length(replace (m.address_name, c.address_name, ''))
              ) desc
  )  as 順位
from
  施設マスタ m 
  left join 住所コード as c 
                 on m.address_name like '%' || c.address_name || '%'

shisetsu_name	address_name	address_name_1	一致率	順位
ファミリーマート　エル本町店	本町4丁目7-4	本町	0.25	1
殖蓮小	大阪市天王寺区上本町6-1-55	上本町	0.1875	1
殖蓮小	大阪市天王寺区上本町6-1-55	本町	0.125	2

さて、抽出結果を見る限りでは、
想定通りに一致率での順位が1のものを抽出してしまえば問題なさそうです。

では、with句に変えて、
1のものだけ抽出しましょう。

with max_hit as ( 
	select
	  *
	  -------------------------------------------------------------------
	  , ( length(m.address_name) 
				- length(replace (m.address_name, c.address_name, ''))) ::numeric 
		/ length(m.address_name) as 一致率
	  -------------------------------------------------------------------
	  -- 1から採番
	  , row_number() over ( 
	    partition by
	      m.shisetsu_name 
	    order by
	      -- 一致率が降順（高い順）に
	      ( 
	        length(m.address_name) - length(replace (m.address_name, c.address_name, ''))
	      ) desc
	  ) as 順位 
	from
	  施設マスタ m 
	  left join 住所コード as c 
	    on m.address_name like '%' || c.address_name || '%'

)

select * 
   from max_hit
   where 順位 = 1

shisetsu_name	address_name	address_name_1	一致率	順位
ファミリーマート　エル本町店	本町4丁目7-4	本町	0.25	1
殖蓮小	大阪市天王寺区上本町6-1-55	上本町	0.1875	1

抽出できました～♪

まとめ

1. 原因究明
2. データ確認
3. SQLの修正

のうち、データ確認が如何に大切かわかっていただけたかと思います。
私が基本的にSQLを書く際は、Excelと抽出結果ぐらいしか見てない気がします。
（今回使ったROW_NUMBERとかは毎回ググりながら書いてますね。）

SQL作成で重要なのは、以下の感覚を身に着けていくことだと考えています。

1. 何を出力したいか？
2. 何で判断しているか？
3. 判断するための列はあるか？追加できるか？

あとがき

この場合どのように考えてSQLを作るか？とか質問あればコメントください。

抽出に使ったSQL

一応、実際に流したSQLを追記しておきます。
説明のためにwith句の一部削ったりしていたので……

with 施設マスタ as (
	select * 
	  from (
	  values ('ファミリーマート　エル本町店','本町4丁目7-4')
	            , ('殖蓮小','大阪市天王寺区上本町6-1-55')
	  ) as t (shisetsu_name ,  address_name)
) , 住所コード as (
	select * 
	  from (
	  values ('本町')
	            , ('上本町')
	  ) as t (address_name )
) , max_hit as ( 
	select
	  *
	  -------------------------------------------------------------------
	  , ( length(m.address_name) 
				- length(replace (m.address_name, c.address_name, ''))) ::numeric 
		/ length(m.address_name) as 一致率
	  -------------------------------------------------------------------
	  -- 1から採番
	  , row_number() over ( 
	    partition by
	      m.shisetsu_name 
	    order by
	      -- 一致率が降順（高い順）に
	      ( 
	        length(m.address_name) - length(replace (m.address_name, c.address_name, ''))
	      ) desc
	  ) as 順位 
	from
	  施設マスタ m 
	  left join 住所コード as c 
	    on m.address_name like '%' || c.address_name || '%'

)

select * 
   from max_hit
   where 順位 = 1