郵便番号データベースの分割されたレコードをマージする（その２）
KEN_ALLテーブルから独自のメタ文字を解析して分割されたレコードをマージした方がいます．
http://flashcast.jp/blog/2010/03/zipsearch1.html
今回は開き括弧と閉じ括弧の対応から分割されたレコードをマージします．
このクエリでコアになる処理はCTE4とCTE6ですが，開き括弧と閉じ括弧で囲まれた連続レコードを抽出する処理は以下の通りです．
USE ZIPDB;
GO
IF OBJECT_ID('#CTE3') IS NOT NULL DROP TABLE #CTE3;WITH START_ID (startid) AS
(
SELECT ID
FROM dbo.KEN_ALL
WHERE AREA LIKE '%(%'
AND AREA NOT LIKE '%)%'
),
END_ID (endid) AS
(
SELECT ID
FROM dbo.KEN_ALL
WHERE AREA NOT LIKE '%(%'
AND AREA LIKE '%)%'
),
CTE (startid, endid, diff) AS
(
SELECT START_ID.startid, END_ID.endid, END_ID.endid - START_ID.startid
FROM START_ID INNER JOIN END_ID
ON START_ID.startid < END_ID.endid
),
CTE1 (startid, mindiff) AS
(
SELECT startid, MIN(diff)
FROM CTE
GROUP BY startid
),
CTE2 (startid, endid, diff) AS
(
SELECT CTE.startid, CTE.endid, CTE.diff
FROM CTE1 INNER JOIN CTE
ON CTE1.startid = CTE.startid
AND CTE1.mindiff = CTE.diff
)
SELECT CTE2.startid, CTE2.endid, K.*
INTO #CTE3
FROM CTE2 INNER JOIN dbo.KEN_ALL AS K
ON K.ID BETWEEN CTE2.startid AND CTE2.endid;WITH CTE4 (ID, startid, endid, DATALEVEL, AREA, AREA_kana) AS
(
SELECT ID, startid, endid, 0, AREA, AREA_kana
FROM #CTE3
WHERE ID = startid
UNION ALL
SELECT #CTE3.ID, #CTE3.startid, #CTE3.endid, DATALEVEL + 1, CTE4.AREA + #CTE3.AREA, CTE4.AREA_kana + #CTE3.AREA_kana
FROM #CTE3 INNER JOIN CTE4
ON CTE4.startid = #CTE3.startid
AND CTE4.ID < #CTE3.ID
),
CTE5 (startid, MAXLEVEL) AS
(
SELECT startid, MAX(DATALEVEL)
FROM CTE4
GROUP BY startid
),
CTE6 (ID, startid, endid, DATALEVEL, AREA, AREA_kana) AS
(
SELECT CTE4.ID, CTE4.startid, CTE4.endid, CTE4.DATALEVEL, CTE4.AREA, CTE4.AREA_kana
FROM CTE4 INNER JOIN CTE5
ON CTE4.startid = CTE5.startid
AND CTE4.DATALEVEL = CTE5.MAXLEVEL
)
UPDATE K
SET AREA = CTE6.AREA, AREA_kana = CTE6.AREA_kana
FROM dbo.KEN_ALL AS K INNER JOIN CTE6
ON K.ID BETWEEN CTE6.startid AND CTE6.endid;
START_IDでは'('を持ち')'を持たないレコードを抽出し，END_IDでは'('を持たず')'を持つレコードを抽出します．
CTEでstartid, endidの組み合わせを作り，CTE2ではCTE1で作った正しい（IDの差分が最小となる）startidとendidの組み合わせをCTEから抽出します．
#CTE3にKEN_ALLからstartidとendidとで挟まれたレコードを抽出して追加しています．一時テーブルを使用するのは主にパフォーマンス上の理由です．私の環境では一時テーブルを使用しない場合9分余りかかりましたが，一時テーブルを使用すると8秒で処理が完了しました．
重複削除のコードは省略します．
前回と今回で，KEN_ALLテーブルを第1正規形の一歩手前まで加工しました．これは非正規形ですが，いわば配列型でもあります．配列のデリミタとして'、'がメタ文字として使われていますが，どうやら階層構造のある配列のようで，機械的に関数に代入しても第1正規形に住所展開できる訳ではなさそうです．その他列挙子としての'・'や範囲指定子の'〜'，否定の候補を示す'「...を除く」'や'「...以外」'などがメタ文字として挙げられます．
これらを第1正規形にするには正規表現が必要ではないかと思います．
