2014/07/09
PostgreSQL >  架空の都道府県名を作る（非復元抽出）
昨日の非復元抽出バージョン。実行環境は 2014/04/17 を参照。昨日と同じテーブル ↓ を使い、全国都道府県の構成文字をランダムに入れ替える。


「都・道・府・県」と各県の字数はそのまま残し、例えば北海道→○○道、神奈川県→○○○県のようにする。昨日は「復元抽出」で同じ文字を重複して使ったが、今日は一字一回しか使わない。ただし「福」「山」など複数の県で使われている字は、複数回登場する。

↓ 作成したクエリ。ブロック b で都道府県名の構成字を一字一行のテーブルにし、ランダムに並び替える。それを後のブロックで各県の字数に合わせて当てはめる。
WITH a AS (
	-- 使用テーブル
	SELECT * FROM "201407"."05_tb_sample_1"
), b AS (
	-- 都道府県名の構成字を一行ずつに
	SELECT (regexp_matches(left(pname, -1), '.', 'g'))[1] chr
	FROM a
	ORDER BY random()
), c AS (
	-- 非復元抽出の準備 1
	SELECT pcode, pname, length(pname) - 1 len
	FROM a
), d AS (
	-- 非復元抽出の準備 2
	SELECT *, coalesce(
		sum(len) over(ORDER BY pcode
		ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) ofs
	FROM c
)
SELECT pcode, pname
	, (SELECT string_agg(chr, '')
		FROM (SELECT * FROM b LIMIT len OFFSET ofs) x
	) || right(pname, 1) pname_new
FROM d ;


↑ 実行例。結果の4行目（宮城県→長崎県）のように実在の県名が偶然できることもある。途中経過のブロック b と d の様子は ↓ のとおり。



すべてが2字＋都・道・府・県ならクエリがもっと簡単だが、3字の県があるのでウィンドウ関数を使った。↓ は、本当に非復元抽出になっているか、つまり元の構成字が過不足なく使われているか確認したところ。先ほどのクエリ結果を一時テーブルに保存し、各構成字が使われている回数をカウントして、もし違う行があれば先頭に来るようにした。
WITH a AS (
	-- 先のクエリ結果を一時テーブルに保存し、読み出し
	SELECT * FROM "201407"."09_pname_new2"
), b AS (
	SELECT (regexp_matches(left(pname, -1), '.', 'g'))[1] chr
		, count(*) cnt1
	FROM a GROUP BY 1
), c AS (
	SELECT (regexp_matches(left(pname_new, -1), '.', 'g'))[1] chr
		, count(*) cnt2
	FROM a GROUP BY 1
)
SELECT *, CASE WHEN cnt1 <> cnt2 THEN text 'ERROR' END chk
FROM b FULL JOIN c USING (chr)
ORDER BY 4, 1 ;


↑ 結果を見ると大丈夫そう。もし問題があれば別記事で修正する。
<< ダミー仮名文字列を作る（復元抽出）
架空の都道府県名を作る（文字の復… >>