2014/07/08
PostgreSQL > 架空の都道府県名を作る（文字の復元抽出）
PostgreSQL > 架空の都道府県名を作る（文字の復元抽出）
昨日までダミーファイル群を作る中で思いついたが、使わなかったクエリ。架空の都道府県名を、各県の構成文字（○○県の○）の復元抽出で作成する。実行環境は 2014/04/17 を参照。
復元抽出というのは「一つ抽出したら元の集合に戻す」、つまり同じ要素を何度も使えること。例えば埼玉県の「埼」と「玉」はも全国47都道府県の構成文字で一つずつしかないが、復元抽出すると複数回出現する可能性がある。
↓ 使うテーブル。今回はコードは使わず、都道府県名のリストがあればいい。
↓ 作成したクエリ。今回「都・道・府・県」と各県の字数はそのまま残し、それ以外の字をランダムに入れ替える。つまり北海道→○○道に、神奈川県→○○○県にする。
↑ 実行結果。復元抽出なので「取」「潟」など一個しかない字が何度も登場している。文字入れ替えの結果、偶然に実在する県名が復活したりする（富山県→山形県、鳥取県→新潟県）。当然ながら架空の各県名は実行するたびに違う。
復元抽出の実装は、ブロック b の構成文字テーブルを ORDER BY random() で並べ替えてから LIMIT で字数分だけ取得する所。LIMIT 数を式で指定できるので、割と簡単に書けた。もう一つ、非復元抽出のバージョンも作ったので明日書く予定。
復元抽出というのは「一つ抽出したら元の集合に戻す」、つまり同じ要素を何度も使えること。例えば埼玉県の「埼」と「玉」はも全国47都道府県の構成文字で一つずつしかないが、復元抽出すると複数回出現する可能性がある。
↓ 使うテーブル。今回はコードは使わず、都道府県名のリストがあればいい。
↓ 作成したクエリ。今回「都・道・府・県」と各県の字数はそのまま残し、それ以外の字をランダムに入れ替える。つまり北海道→○○道に、神奈川県→○○○県にする。
WITH a AS (
-- 使用テーブル
SELECT * FROM "201407"."05_tb_sample_1"
), b AS (
-- 都道府県名の構成文字
SELECT (regexp_matches(left(pname, -1), '.', 'g'))[1] chr
FROM a
)
SELECT pcode, pname, concat((
SELECT string_agg(chr, '')
FROM (
SELECT * FROM b ORDER BY random() LIMIT length(pname) - 1
) x), right(pname, 1)) pname_new
FROM a ;
↑ 実行結果。復元抽出なので「取」「潟」など一個しかない字が何度も登場している。文字入れ替えの結果、偶然に実在する県名が復活したりする（富山県→山形県、鳥取県→新潟県）。当然ながら架空の各県名は実行するたびに違う。
復元抽出の実装は、ブロック b の構成文字テーブルを ORDER BY random() で並べ替えてから LIMIT で字数分だけ取得する所。LIMIT 数を式で指定できるので、割と簡単に書けた。もう一つ、非復元抽出のバージョンも作ったので明日書く予定。