住所を都道府県と市区町村に分割するSQL

HiveQLにおける処理を想定したメモ。

住所が書かれたカラムがあり、それを都道府県単位、市区町村単位でまとめ上げて集計などしたい場合がある。 都道府県は問題ないが、市区町村の中には、"市"、"町"、"村"が含まれる自治体名があるために単純に分割することができない。 そこで、地道に場合分けすることで対処する（2020年6月現在の市区町村名）。

WITH a AS (
  SELECT '東京都町田市原町田1丁目1−11' address
), b AS (
  SELECT
    address,
    REGEXP_EXTRACT(address, '^([^市区町村]{2}[都道府県]|[^市区町村]{3}県)') prefecture,
    REGEXP_PEPLACE(address, REGEXP_EXTRACT(address, '^([^市区町村]{2}[都道府県]|[^市区町村]{3}県)'), '') city
  FROM
    a
)

SELECT
  address,
  prefecture,
  CASE
    -- 「区」
    WHEN city RLIKE '区' THEN REGEXP_EXTRACT(city, '^(.+区)')
    -- 「市」の入った市
    WHEN city RLIKE '^市川市' THEN '市川市'
    WHEN city RLIKE '^市原市' THEN '市原市'
    WHEN city RLIKE '^野々市市' THEN '野々市市'
    WHEN city RLIKE '^四日市市' THEN '四日市市'
    WHEN city RLIKE '^廿日市市' THEN '廿日市市'
    -- 「町」の入った市
    WHEN city RLIKE '^町田市' THEN '町田市'
    WHEN city RLIKE '^十日町市' THEN '十日町市'
    WHEN city RLIKE '^大町市' THEN '大町市'
    -- 「村」の入った市
    WHEN city RLIKE '^田村市' THEN '田村市'
    WHEN city RLIKE '^東村山市' THEN '東村山市'
    WHEN city RLIKE '^武蔵村山市' THEN '武蔵村山市'
    WHEN city RLIKE '^村山市' THEN '村山市'
    WHEN city RLIKE '^羽村市' THEN '羽村市'
    WHEN city RLIKE '^村上市' THEN '村上市'
    WHEN city RLIKE '^大村市' THEN '大村市'
    -- 「市」を含む町村郡
    WHEN city RLIKE '^余市郡.+[町村]' THEN REGEXP_EXTRACT(city, '^余市郡.+[町村]')
    WHEN city RLIKE '^芳賀郡.+町' THEN REGEXP_EXTRACT(city, '^芳賀郡.+町')
    WHEN city RLIKE '^中新川郡.+[町村]' THEN REGEXP_EXTRACT(city, '^中新川郡.+[町村]')
    WHEN city RLIKE '^西八代郡市川三郷町' THEN '西八代郡市川三郷町' 
    WHEN city RLIKE '^神崎郡.+町' THEN REGEXP_EXTRACT(city, '^神崎郡.+町')
    WHEN city RLIKE '^高市郡.+[町村]' THEN REGEXP_EXTRACT(city, '^高市郡.+[町村]')
    WHEN city RLIKE '^吉野郡.+[町村]' THEN REGEXP_EXTRACT(city, '^吉野郡.+[町村]')
    -- 「町」を含む町村郡
    WHEN city RLIKE '^杵島郡.+町' THEN REGEXP_EXTRACT(city, '^杵島郡.+町')
    -- 「村」を含む町村郡
    WHEN city RLIKE '^柴田郡.+町' THEN REGEXP_EXTRACT(city, '^柴田郡.+町')
    WHEN city RLIKE '^東村山郡.+町' THEN REGEXP_EXTRACT(city, '^東村山郡.+町')
    WHEN city RLIKE '^西村山郡.+町' THEN REGEXP_EXTRACT(city, '^西村山郡.+町')
    WHEN city RLIKE '^北村山郡大石田町' THEN '北村山郡大石田町'
    WHEN city RLIKE '^田村郡.+町' THEN REGEXP_EXTRACT(city, '^田村郡.+町')
    WHEN city RLIKE '^佐波郡玉村町' THEN '佐波郡玉村町'
    -- その他
    WHEN city RLIKE '^([^市区町村]*[市区町村])' THEN REGEXP_EXTRACT(city, '^([^市区町村]*[市区町村])')
    ELSE NULL
  END city
FROM
  b
;

もっと効率的に書けそうではあります。

これを実行した結果は、以下のようになる。

address prefecture  city
東京都町田市原町田1丁目1-11 東京都  町田市

uub.jp

ビッグデータ分析・活用のためのSQLレシピ [ 加嵜長門 ] 価格：4180円（税込、送料無料) (2021/5/29時点) 楽天で購入

SQLデータ分析・活用入門 データサイエンスの扉を開くための技術 [ 西潤史郎 ] 価格：2860円（税込、送料無料) (2021/5/29時点) 楽天で購入