和暦対応＆地名展開
この記事はPostgreSQL Advent Calendar 2013 - Qiitaの22日目の記事にゃ。
今回は新ネタはありません(新ネタの実装が間に合わなかった＆来月のPostgreSQL Unconferenceに出すことにした)。
なので今回はブログの過去エントリからお気に入りのテーマを再構成した感じでまとめます。ご了承くださいませ。
テーマは2つ。和暦対応と地名展開です。
その1：和暦対応
今日は冬至日、二十四節気でいうところ「冬至」の始まりですね。
さて、PostgreSQLには色んなデータ型があって勿論日時型もありますが、残念ながら(？)二十四節気や七十二候には対応していません(四半期を取り出すquarterとかはありますが)。
日本に住んでいるのだから、やっぱり二十四節気は使いたい。ということで対応してみましたｗ
実行例
早速、実行例を見てみましょう。例えばこんな感じ。
wareki=# SELECT CURRENT_DATE, ts2w24(CURRENT_DATE);
date | ts2w24
------------+--------
2013-12-22 | 冬至
(1 row)せっかくなので月の表記もそれっぽくしてみます。
wareki=# SELECT CURRENT_DATE, ts2w12(CURRENT_DATE);
date | ts2w12
------------+--------
2013-12-22 | 師走これだけじゃあまり変態っぽくないので、今度はこれを条件として使ってみます。
サンプルとして、lifelog というテーブルを検索してみることにします。テーブルには200件ほどのラー食のログが格納されています。
wareki=# \d lifelog
Table "public.lifelog"
Column | Type | Modifiers
-------------+---------+------------------------------------------------------
id | integer | not null default nextval('lifelog_id_seq'::regclass)
data | text |
review_date | date |
Indexes:
"lifelog_data_idx" gin (data gin_bigm_ops)
wareki=# TABLE lifelog LIMIT 10;
id | data | review_date
----+---------------------------------------------+-------------
1 | 港南区で鶏白湯らーめんを食べた。 | 2012-09-01
2 | 伊勢佐木町で油そばを食べた。 | 2012-09-02
3 | 大さん橋で冷やしラーメンを食べた。 | 2012-09-02
4 | 大和で豚骨醤油ラーメンを食べた。 | 2012-09-08
5 | 穴守稲荷で豚骨醤油ラーメンを食べた。 | 2012-09-15
6 | 羽咋市で海鮮塩ラーメンを食べた。 | 2012-09-17
7 | 環状2号・上永谷で豚骨醤油ラーメンを食べた。 | 2012-09-19
8 | 鶴屋町で醤油ラーメンを食べた。 | 2012-09-21
9 | 西区で醤油ラーメンを食べた。 | 2012-09-22
10 | 寿町で醤油ラーメンを食べた。 | 2012-09-23
・・・例えば・・・今と真逆の季節、「夏至」(6/21-7/6)の頃に何を食べていたか検索してみましょう。
このときに夏至の期間を示す"<=>"という演算子を使います。
wareki=# SELECT * FROM lifelog WHERE review_date <=> '夏至'; id | data | review_date -----+--------------------------------+------------- 171 | 品川で味噌つけ麺を食べた。 | 2013-06-22 172 | 横浜で油そばを食べた。 | 2013-07-02 173 | 新丸子で醤油ラーメンを食べた。 | 2013-07-06 (3 rows)
さらに「文月」(7月)のレビューに絞り込んでみます。
wareki=# SELECT * FROM lifelog WHERE review_date <=> '夏至' AND review_date <=> '文月'; id | data | review_date -----+--------------------------------+------------- 172 | 横浜で油そばを食べた。 | 2013-07-02 173 | 新丸子で醤油ラーメンを食べた。 | 2013-07-06
二十四節気をさらに3つの期間に分けた七十二候にも無駄に対応してみました。
wareki=# SELECT * FROM lifelog WHERE review_date <=> '禾乃登'; id | data | review_date -----+--------------------------------------+------------- 1 | 港南区で鶏白湯らーめんを食べた。 | 2012-09-01 2 | 伊勢佐木町で油そばを食べた。 | 2012-09-02 3 | 大さん橋で冷やしラーメンを食べた。 | 2012-09-02 211 | 西区で坦々刀削麺を食べた。 | 2013-09-02 212 | 神奈川区で直系家系ラーメンを食べた。 | 2013-09-04 (5 rows)
ちなみに「禾乃登」は「こくものすなわちみのる」と読むらしいです。期間は9/1-9/7らしいです。尤も、この機能の実装のために調査するまで、そんなことは全然知りませんでしたがｗ
逆に出力側を二十四節気として対応もできます。
どの季節に家系を食べたのか調べてみます。
wareki=# SELECT id, data, ts2w24(review_date) FROM lifelog WHERE data LIKE likequery('家系');
id | data | ts2w24
-----+--------------------------------------+--------
20 | 大口で家系ラーメンを食べた。 | 寒露
22 | 大和で家系ラーメンを食べた。 | 寒露
26 | 瀬谷区で家系ラーメンを食べた。 | 霜降
31 | 大井町(東京)で家系ラーメンを食べた。 | 霜降
37 | 瀬谷区で家系ラーメンを食べた。 | 霜降
58 | 綱島で家系ラーメンを食べた。 | 小雪
73 | 大井町(東京)で家系ラーメンを食べた。 | 冬至
74 | 戸塚で家系ラーメンを食べた。 | 冬至
77 | 職場近くで家系ラーメンを食べた。 | 小寒
81 | 大井町(東京)で家系ラーメンを食べた。 | 小寒
93 | 目黒で家系ラーメンを食べた。 | 雨水
96 | 新丸子で家系ラーメンを食べた。 | 雨水
112 | 職場近くで家系ラーメンを食べた。 | 春分
147 | 中区で家系ラーメンを食べた。 | 立夏
151 | 磯子区で家系ラーメンを食べた。 | 立夏
159 | 伊勢佐木で家系ラーメンを食べた。 | 芒種
161 | 杉田で家系ラーメンを食べた。 | 芒種
184 | 川崎で家系ラーメンを食べた。 | 大暑
190 | 鶴見で家系ラーメンを食べた。 | 立秋
197 | 藤沢で家系ラーメンを食べた。 | 立秋
202 | 杉田で朝から家系ラーメンを食べた。 | 処暑
204 | 鴨居で家系ラーメンを食べた。 | 処暑
210 | 野毛で家系ラーメンを食べた。 | 処暑
212 | 神奈川区で直系家系ラーメンを食べた。 | 処暑
(24 rows)なんか、年中食ってみるみたいですね(汗
実装
和暦月の表示、二十四節気、七十二候の実装は、PostgreSQLのDOMAIN機能を使っています。普通はCREATE TYPEで新しく型を作るのが定番だとは思いますが、今回はあえてTEXT型のDOMAINと範囲型をちょっと応用して、pl/pgSQLで簡易実装してみました。
まず、TEXT型をベースとしてCREATE DOMAINで制約チェックを加えたドメインとします。
CREATE DOMAIN wareki AS text CONSTRAINT wareki_check NOT NULL CHECK ( VALUE ~ '(立春|雨水|啓蟄|春分|清明|穀雨|立夏|小満|芒種|夏至|小暑|大暑|立秋|処暑|白露 |秋分|寒露|霜降|立冬|小雪|大雪|冬至|小寒|大寒|睦月|如月|衣更着|弥生|卯月|皐月|早月|>水無月|文月|葉月|長月|神無月|神有月|霜月|師走|東風解凍|黄鶯睍薭|魚上氷|土脉潤起|霞始 靆|草木萠動|蟄虫啓戸|桃始笑|菜虫化蝶|雀始巣|桜始開|雷乃発声|玄鳥至|鴻雁北|虹始見|葭>始生|霜止出苗|牡丹華|蛙始鳴|蚯蚓出|竹笋生|蚕起食桑|紅花栄|麦秋至|螳螂生|腐草為蛍|梅>子黄|乃東枯|菖蒲華|半夏生|温風至|蓮始開|鷹乃学習|桐始結花|土潤溽暑|大雨時行|涼風至|>寒蝉鳴|蒙霧升降|綿柎開|天地始粛|禾乃登|草露白|鶺鴒鳴|玄鳥去|雷乃収声|蟄虫坏戸|水始涸 |鴻雁来|菊花開|蟋蟀在戸|霜始降|霎時施|楓蔦黄|山茶始開|地始凍|金盞香|虹蔵不見|朔風払>葉|橘始黄|閉塞成冬|熊蟄穴|鱖魚群|乃東生|麋角解|雪下出麦|芹乃栄|水泉動|雉始雊|款冬華|水沢腹堅|鶏始乳)' );
warekiというドメインでは、和暦月、二十四節気、七十二候の表記のみを許容するようにします。
あとはTIMESTAMP/DATE型からwarekiドメインに変換する関数を記述します。
まず、TIMESTAMP/DATE型から
EXTRACT(DOY FROM ts)
で、その年の年始からの日数(整数値)で取り出し、うるう年補正をします。
次に、取り出した整数値とINT4RANGE（int4の範囲型）とちまちまチェックします。
実装コードの一部を示すとこんな感じになります。
CREATE OR REPLACE FUNCTION ts2w24(ts timestamp without time zone) RETURNS wareki AS $$
DECLARE
doy integer := EXTRACT(DOY FROM ts);
BEGIN
IF isLeapYear(ts) AND doy > 60 THEN
doy := doy - 1; -- 閏日対応
END IF;
IF int4range('[35, 50)') @> doy THEN
return '立春';
ELSIF int4range('[50, 65)') @> doy THEN
return '雨水';あとは、二十四節気と七十二候のそれぞれの範囲をint4range()で生成したものと、doy（年始からの日数）を延々比較していくというだけです。
そしてTIMESTAMP/DATE型が和暦表記に含まれるかどうか、という判定関数 include_wareki も作成しておきます。
CREATE OR REPLACE FUNCTION include_wareki(ts timestamp, w wareki) RETURNS boolean AS $$
DECLARE
tsr INT4RANGE;
doy integer := EXTRACT(DOY FROM ts);
BEGIN
tsr = wareki_to_doys(w);
IF w = '冬至' THEN
-- 冬至のみの例外処理
return doy <@ '[1,6)'::int4range OR doy <@ '[356,366]'::int4range ;
ELSE
return tsr @> doy ;
END IF;
END;
$$ LANGUAGE plpgsql;上記関数内のwareki_to_doy()という関数は、和暦月/二十四節気/七十二候の文字列から、それぞれの範囲に街頭するINT4RANGE型を返却する関数です。これも単純に文字列比較をして一致したら、それに対応するINT4RANGEの値を返却するだけです。面倒ですがやってることは単純です。
CREATE OR REPLACE FUNCTION wareki_to_doys(w wareki) RETURNS int4range AS $$
DECLARE
BEGIN
-- warekiの値によってdoy(Day of Year)のint4rangeをセットする
CASE w
-- 二十四節気表記
-- 本当は年毎に微妙に日付が微妙に違うので
-- 年から算出するのが正しいけど今回は簡易的に固定値セット。
WHEN '立春' THEN
return int4range('[35, 50)');
WHEN '雨水' THEN
return int4range('[50, 65)');
・・・最後に、この関数を使う、オペレータ <=> も生成します。これで、TIMESTAMP/DATE型が二十四節気/七十二候に含まれるかどうかという判定が書けるようになります(まあinclude_warekiを直接呼び出してもいいですが)。
CREATE OPERATOR <=> ( LEFTARG = timestamp, RIGHTARG = wareki, PROCEDURE = include_wareki, COMMUTATOR = <=> );
ということで、PostgreSQLで二十四節気や七十二候を扱えるようにしてみました。
誰得ですけどｗ
地名展開
次は地名展開と全文検索を組み合わせる例の紹介です。
また、さっきの lifelog テーブルを見てみましょう。
wareki=# TABLE lifelog LIMIT 10; id | data | review_date ----+---------------------------------------------+------------- 1 | 港南区で鶏白湯らーめんを食べた。 | 2012-09-01 2 | 伊勢佐木町で油そばを食べた。 | 2012-09-02 3 | 大さん橋で冷やしラーメンを食べた。 | 2012-09-02 4 | 大和で豚骨醤油ラーメンを食べた。 | 2012-09-08 5 | 穴守稲荷で豚骨醤油ラーメンを食べた。 | 2012-09-15 6 | 羽咋市で海鮮塩ラーメンを食べた。 | 2012-09-17 7 | 環状2号・上永谷で豚骨醤油ラーメンを食べた。 | 2012-09-19 8 | 鶴屋町で醤油ラーメンを食べた。 | 2012-09-21 9 | 西区で醤油ラーメンを食べた。 | 2012-09-22 10 | 寿町で醤油ラーメンを食べた。 | 2012-09-23 ・・・
私は地元が横浜ということもあって、当然ながら横浜でラーメンを食べることが多いのですが、そういうときには横浜市内のもっと限定された地域「港南区」「(中区の)伊勢佐木町」で食べた、みたいな記述をすることが多いです。
さて、このlifelogテーブルのdataカラムにN-gram全文検索モジュールpg_bigmを使って全文検索インデックスを設定しておきます。
で、そのテーブルに対して「横浜」を条件値として与えて検索してみます。
wareki=# SELECT id, data FROM lifelog WHERE data LIKE likequery('横浜');
id | data
-----+------------------------------------
68 | 横浜駅近くで醤油ラーメンを食べた。
82 | 横浜駅で醤油ラーメンを食べた。
172 | 横浜で油そばを食べた。
193 | 横浜で火鍋風ラーメンを食べた。
194 | 横浜で冷やしラーメンを食べた。
201 | 横浜駅近くでタンメンを食べた。
206 | 横浜駅近くで冷やし刀削麺を食べた。
(7 rows)当たり前ですが「横浜」を含むレコードしかヒットしません。(´・ω・｀)
本当は、横浜市内の「港南区」や「伊勢佐木町」で食べたレコードもヒットさせたいわけです。
そこで、likequery()の代わりに、地名を展開するexpand_area(text)を記述します。なお、この関数はTEXTの配列を返却するので、LIKE ANY (expand_area(text))のように記述します。
実行例を見てみます。
wareki=# SELECT id, data FROM lifelog WHERE data LIKE ANY (expand_area('横浜'));
id | data
-----+-----------------------------------------------
1 | 港南区で鶏白湯らーめんを食べた。
2 | 伊勢佐木町で油そばを食べた。
8 | 鶴屋町で醤油ラーメンを食べた。
9 | 西区で醤油ラーメンを食べた。
・・・
206 | 横浜駅近くで冷やし刀削麺を食べた。
207 | 西区で醤油ラーメンを食べた。
210 | 野毛で家系ラーメンを食べた。
211 | 西区で坦々刀削麺を食べた。
212 | 神奈川区で直系家系ラーメンを食べた。
(65 rows)と、こんな結果になります。id=206 のように「横浜」を含むレコードだけでなく、id=1 のように「港南区」やid=2 のように「伊勢佐木町」を含むレコードもヒットするようになりました。
そして「中区」で検索すると
wareki=# SELECT id, data FROM lifelog WHERE data LIKE ANY (expand_area('中区'));
id | data -----+----------------------------------------------- 2 | 伊勢佐木町で油そばを食べた。 12 | 麺恋亭 中華街店でなめこそばを食べた。 13 | 伊勢佐木町で油そばを食べた。 52 | 石川町で味噌ラーメンを食べた。
中区内の地名を含むレコードが検索されます。
ふしぎ！
実装
ふしぎ！でもなんでもなく、これはexpand_area()が検索結果として「港南区」や「伊勢佐木町」を含むTEXT配列を返却しているからです。
例えば、expand_area('横浜')の場合は、
wareki=# SELECT expand_area('横浜');
expand_area
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
------------------------------------------
{%みなとみらい%,%桜木町%,%蒔田%,%神奈川区%,%横浜%,%中区%,%弘明寺%,%鶴見区%,%生麦%,%南
太田%,%尻手%,%高島%,%中華街%,%吉野町%,%西区%,%緑区%,%浅間町%,%矢向%,%南区%,%関内%,%反
%,%鴨居%,%白楽%,%石川町%,%野毛%,%伊勢佐木町%,%中山%,%大黒町%,%長津田%,%山下町%,%六角橋
%,%東神奈川%,%鶴屋町%,%十日市場%,%横浜%}のようなTEXT配列を生成します。
さて、このTEXT配列はどうやって生成しているのかというと、関数内で地名展開用の辞書テーブルを検索して生成しています。
地名展開用の辞書テーブル(area)は、以下のような構造のテーブルです。
wareki=# \d area
Table "public.area"
Column | Type | Modifiers
--------+-----------+-----------
id | integer |
name | text |
childs | integer[] | nameには地名を、childsにはその地名に含まれる地名群のid配列を設定します。
例えばこんな感じで。
wareki=# TABLE area;
id | name | childs
------+--------------+--------------------------------------
1000 | 東京都 | {1100,1200,1300,1400}
・・・
1400 | 町田市 | {1401,1402,1403,1404}
・・・
2000 | 神奈川県 | {1400,2100,2200,2300,2400,2500,2600}
2001 | 湘南 | {2500,2600,2700}
2100 | 横浜市 | {2110,2120,2130,2140,2150,2160}
2110 | 中区 | {2111,2112,2113,2114,2115,2116,2117}
2111 | 山下町 | {}
2112 | 中華街 | {}
2113 | 伊勢佐木町 | {}
2114 | 石川町 | {}
2115 | 関内 | {}
2116 | 桜木町 | {}
2117 | 野毛 | {}
2120 | 西区 | {2116,2121,2122,2123}
・・・図で示すと、こういう擬似ツリー(或いは有方向の非循環グラフ)になります。
町田市のように、東京都にも神奈川県にも属するような市も表現可能ですｗ
こういうツリー状になった情報を検索するときに役立つのが、PostgreSQL 8.4から導入されたWITH句を使った再帰問い合わせです。
関数expand_area()は、この再帰問い合わせを応用して作成しています。
関数の定義を見てみましょう。
CREATE OR REPLACE FUNCTION expand_area(area_name TEXT) RETURNS TEXT[] AS $$ SELECT array_append(array_agg(likequery(regexp_replace(res.name, '(市|県|都)$', ''))), likequery(area_name)) FROM (WITH RECURSIVE ar AS (SELECT * FROM area AS ar1 WHERE ar1.name LIKE likequery(area_name) UNION ALL SELECT ar2.* FROM ar, area as ar2 WHERE ar2.id = ANY (ar.childs)) SELECT distinct name FROM ar) as res; $$ LANGUAGE sql;
引数として与えられた area_text は再帰問い合わせの基点となる
SELECT * FROM area AS ar1 WHERE ar1.name LIKE likequery(area_name)
のlikequery()に与えられます。
この基点クエリとUNIONをとる
SELECT ar2.* FROM ar, area as ar2 WHERE ar2.id = ANY (ar.childs))
の問い合わせが再帰的に実行されます。
ここでarのchilds、つまり自分の地名の配下の地名idと一致するレコードを検索しています。配下要素にヒットするレコードが存在しなくなるまで、これを再帰的に繰り返します。
最後に、この再帰クエリの結果となったレコードをarray_aggで配列に変換して返却します。
シンプルなSQLですが、これで任意の段数のツリー内のヒットしたノードと配下ノードの結果を返却することができます。
再帰問い合わせバンザイですね！
おわりに
以上、PostgreSQLの機能を使って誰得な検索をやってみる例を紹介しました。
今回の例はSQL関数やpl/pgSQL関数だけで簡単に実装しただけですが、C言語関数を組み合わせればもっと面白いことが出来そうです。
来月のUnconferenceまでには、もうちょっと面白そうな拡張を実装して紹介できればと思います。
