これはPostgreSQL Advent Calendar 2014の12月16日分の記事です。
昨日はmoomindaniさんの記事でした。
数あるデータベースの中からPostgreSQL……というより、RDBMSを採用する理由の一つとして、複雑なクエリが実行できることが挙げられます。
スケールアウト等を考えると、重たい処理をDBで実行させるのはあまり褒められた話ではないとも思いますが、趣味でやっているWebサービスレベルであれば、そもそもマシンが1台しかないなら、なるべくDB内で処理を完結させた方が効率的なこともあります。
で、先日、とある処理をDB内で完結させようとした結果、思いのほかドツボに嵌まり、個人的には相当カオスなSQLが生まれてしまいましたので、ここで紹介したいと思います。
（と申しますか、もっと美しいSQLがある筈……）
現在、Microsoft Office Wordの自動索引登録機能を補助することに特化したサイトを趣味で作っているのですが、wordファイル中から索引候補を抽出する処理でtextsearch_jaのja_analyzeを利用させて頂いています。
例えば、PostgreSQLの公式ドキュメントの一部をja_analyzeにかけると以下のような結果が取得できます。
=>SELECT word,type,ruby FROM
ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に
関する情報を収集し、報告するサブシステムです。');
word | type | ruby
--------------+--------+--------------
PostgreSQL | 名詞 | PostgreSQL
の | 助詞 | ノ
統計 | 名詞 | トウケイ
情報 | 名詞 | ジョウホウ
コレクタ | 名詞 | コレクタ
は | 助詞 | ハ
サーバ | 名詞 | サーバ
の | 助詞 | ノ
活動 | 名詞 | カツドウ
状況 | 名詞 | ジョウキョウ
に | 助詞 | ニ
関する | 動詞 | カンスル
情報 | 名詞 | ジョウホウ
を | 助詞 | ヲ
収集 | 名詞 | シュウシュウ
し | 動詞 | シ
、 | 記号 | 、
報告 | 名詞 | ホウコク
する | 動詞 | スル
サブシステム | 名詞 | サブシステム
です | 助動詞 | デス
。 | 記号 | 。
(22 rows)
一見すると問題なさそうですが、索引候補とするには、名詞が細かく分割され過ぎています。
索引候補としては「統計情報コレクタ」で一つの語句として抽出したいわけです。
どういった条件で一つの語句とするかは色々な考え方があるのでしょうが、今回は「名詞が連続している部分を抽出する」ことを条件としたいと思います。
つまり、以下のような結果を取得するためには、どのようなSQLを書いたら良いか、ということです。
word | ruby
------------------+----------------------------
統計情報コレクタ | トウケイジョウホウコレクタ
活動状況 | カツドウジョウキョウ
(2 rows)
まず、「連続している名詞」を抽出しなければならないわけですが、これは先日の記事でも紹介したWindow関数で抽出できます。
後の処理のために、row_numberも取得しておきます。
=>SELECT row_number () OVER (),
lag (type) OVER (),
lead (type) OVER (),
word,type,ruby FROM
ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に
関する情報を収集し、報告するサブシステムです。');
row_number | lag | lead | word | type | ruby
------------+--------+--------+--------------+--------+--------------
1 | | 助詞 | PostgreSQL | 名詞 | PostgreSQL
2 | 名詞 | 名詞 | の | 助詞 | ノ
3 | 助詞 | 名詞 | 統計 | 名詞 | トウケイ
4 | 名詞 | 名詞 | 情報 | 名詞 | ジョウホウ
5 | 名詞 | 助詞 | コレクタ | 名詞 | コレクタ
6 | 名詞 | 名詞 | は | 助詞 | ハ
7 | 助詞 | 助詞 | サーバ | 名詞 | サーバ
8 | 名詞 | 名詞 | の | 助詞 | ノ
9 | 助詞 | 名詞 | 活動 | 名詞 | カツドウ
10 | 名詞 | 助詞 | 状況 | 名詞 | ジョウキョウ
11 | 名詞 | 動詞 | に | 助詞 | ニ
12 | 助詞 | 名詞 | 関する | 動詞 | カンスル
13 | 動詞 | 助詞 | 情報 | 名詞 | ジョウホウ
14 | 名詞 | 名詞 | を | 助詞 | ヲ
15 | 助詞 | 動詞 | 収集 | 名詞 | シュウシュウ
16 | 名詞 | 記号 | し | 動詞 | シ
17 | 動詞 | 名詞 | 、 | 記号 | 、
18 | 記号 | 動詞 | 報告 | 名詞 | ホウコク
19 | 名詞 | 名詞 | する | 動詞 | スル
20 | 動詞 | 助動詞 | サブシステム | 名詞 | サブシステム
21 | 名詞 | 記号 | です | 助動詞 | デス
22 | 助動詞 | | 。 | 記号 | 。
(22 rows)
lagに1行前の、leadに1行後のtypeが表示されます。
ウィンドウ演算が行われた結果に対してフィルタ処理を掛ける場合は副問い合わせを利用します。
また、この後の処理のために、row_numberに対してCASE文を打ちます。
=>SELECT CASE
WHEN lag = '名詞' THEN 1
ELSE row_number
END num,
row_number,word,ruby
FROM
(
SELECT row_number () OVER (),
lag (type) OVER (),
lead (type) OVER (),
word,type,basic,ruby FROM
ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に
関する情報を収集し、報告するサブシステムです。')
) foo1
WHERE (lead = '名詞' AND type = '名詞')
OR ( lag = '名詞' AND type = '名詞');
num | row_number | word | ruby
-----+------------+----------+--------------
3 | 3 | 統計 | トウケイ
1 | 4 | 情報 | ジョウホウ
1 | 5 | コレクタ | コレクタ
9 | 9 | 活動 | カツドウ
1 | 10 | 状況 | ジョウキョウ
(5 rows)
lagを利用し、1行前が名詞だったら1、そうでなければrow_numberを表示させます。
これにより、連続している名詞の切れ目でnum列に最大値が現れることになります
次は、この結果に対して更にWindow関数を用いて副問い合わせします。
=>SELECT max(num) OVER (ORDER BY row_number),word,ruby FROM
(
SELECT CASE
WHEN lag = '名詞' THEN 1
ELSE row_number
END num,
row_number,word,ruby
FROM
(
SELECT row_number () OVER (),
lag (type) OVER (),
lead (type) OVER (),
word,type,basic,ruby FROM
ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に
関する情報を収集し、報告するサブシステムです。')
) foo1
WHERE (lead = '名詞' AND type = '名詞')
OR ( lag = '名詞' AND type = '名詞')
) foo2;
max | word | ruby
-----+----------+--------------
3 | 統計 | トウケイ
3 | 情報 | ジョウホウ
3 | コレクタ | コレクタ
9 | 活動 | カツドウ
9 | 状況 | ジョウキョウ
(5 rows)
上から順に各行時点でのnumの最大値を表示するmax列を生成することで、連続している名詞がmax列の値でグループ化されます。（個人的にはここの処理で一番悩みました）
ここまで持ってくればあと一息です。
異なる行の値を連結するために、array_agg関数とarray_to_string関数を使います。
array_agg関数は引数を配列に変換する集約関数で、array_to_string関数は引数の配列を文字列に結合する関数です。
よって、上記の結果を更に副問い合わせし、max列で集約すれば完成です。
=>SELECT
array_to_string (array_agg (word), '') word,
array_to_string (array_agg (ruby), '') ruby FROM
(
SELECT max(num) OVER (ORDER BY row_number),word,ruby FROM
(
SELECT CASE
WHEN lag = '名詞' THEN 1
ELSE row_number
END num,
row_number,word,ruby
FROM
(
SELECT row_number () OVER (),
lag (type) OVER (),
lead (type) OVER (),
word,type,basic,ruby FROM
ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に
関する情報を収集し、報告するサブシステムです。')
) foo1
WHERE (lead = '名詞' AND type = '名詞')
OR ( lag = '名詞' AND type = '名詞')
) foo2
) foo3 GROUP BY max;
word | ruby
------------------+----------------------------
統計情報コレクタ | トウケイジョウホウコレクタ
活動状況 | カツドウジョウキョウ
(2 rows)
如何でしたでしょうか。
……SQLでやらなければforループとかで遥かにシンプルに実装できそうなことはわかってはおりますが、そこはPostgreSQL Advent Calendarの記事だからということで目をつむって頂ければと思います。
SQLに限った話ではないですが、結局、各種機能を「知っているか知らないか」だけで生産性が大幅に変わることになります。
SQLによる他の方法で同結果を得る方法を思いつく方は、是非ご教示ください。
タグ：PostgreSQL
