2014年12月16日

PostgreSQLパズル?

これは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
posted by hahasoha at 18:00| Comment(2) | PostgreSQL

2014年12月06日

テストデータに対するテストについて

これはPostgreSQL Advent Calendar 2014の12月6日分の記事です。


河原と申します。

僭越ながらPostgreSQL関連本をAmazon PODで出版させて頂いております。







この記事以外に全く記事がないことからもお分かり頂けるように、記事の投稿の為にわざわざブログを解説したわけですが、大した記事は書けませんのであしからず…


早速、本題に入ります。

テストデータはなるべく本番環境のデータに近い内容であるべきなのですが、大量のリアルなデータを作成するには、それなりにテクニックが必要となります。


で、PostgreSQLでのテストデータ作成については既に去年のAdvent Calendarで笠原さんが書かれていますので未読の方はまずはそちらの記事をご覧ください。


リアルなデータを作成するには色々と問題があるわけですが、その一つとしてランダム性が挙げられます。

generate_series関数を使えば大量の連番データは容易に作成できますが、そこにランダム性を加えるとなると一工夫が必要となるわけです。


そして、ただランダムなだけであればrandom関数でも噛ませれば済む話なのですが、「ランダム性もあって整合性も保たれてなければならない」となると更に工夫が必要となります。


例えば先日、「シーケンス列とレコード作成日時列」の2列を持つテーブルのサンプルデータを作成する機会がありました。

簡略化しますが、以下のようなイメージです。



seq | testtime
-----+---------------------
1 | 2014-12-04 00:00:01
2 | 2014-12-04 00:00:02
3 | 2014-12-04 00:00:03
4 | 2014-12-04 00:00:04
5 | 2014-12-04 00:00:05
  :

で、testtimeにランダム性を持たせたいわけですが、現実のデータを模するのであれば、testtimeは、seqが小さいレコードより大きな値でなければなりません。

つまり、ランダムの間隔でレコードが作成されていくのを模したいわけです。


とりあえず、本当にランダム性が高いといえるか疑問はありますが、以下のようにサンプルデータを作成したとします。



=> CREATE TEMP TABLE tempdata AS
SELECT generate_series(1,1000000) seq,
current_timestamp + (generate_series(1,1000000)||'minutes')::interval
- (floor(random() * 60)||'seconds')::interval testtime ;

seq | testtime
-----+-------------------------------
1 | 2014-12-04 00:25:42.593496+09
2 | 2014-12-04 00:26:27.593496+09
3 | 2014-12-04 00:27:22.593496+09
4 | 2014-12-04 00:28:13.593496+09
5 | 2014-12-04 00:29:09.593496+09
  :

レコード単位に1分ずつ加算させつつ、60秒未満のランダムな秒数を減算します。

仮にseqが1のレコードが最小の減算値で、逆にseqが2のレコードが最大の減算値であったとしても、1のtesttimeが2を上回ることはない……筈なのですが、確認したくなるのが人情でしょう。


余談ですが、100点満点のテストの点数のサンプルデータを作成する場合、ceil(random()*100)とやりたくなりますが、これだと0点が出ないことになるので、ceil(random()*101)-1かfloor(random()*101)としないと求めたい結果が出ません。

ですが、公式ドキュメントでは、random関数の範囲は8.3まででは「0.0〜1.0の範囲の乱数値」と、最新のドキュメントでは「0.0 <= x < 1.0の範囲の乱数値」と説明されています。
この説明が正しければ、random関数でジャスト0が出力される可能性があるということになります。
が、とりあえず以下のSQLで10億回ほど施行してみましたが0は出ませんでした。

=> WITH r AS
(SELECT ceil(random()) col1 FROM generate_series(1,1000000000))
SELECT * FROM r WHERE col1 = 0 ;

col1
------
(0 rows)

random関数はdouble precisionを返すようですが、公式ドキュメントの浮動小数点データ型の説明を簡単に読んだ限りでは、相当な桁数まで行くようですね…
本当に0が出るのか確認するためにはソースを読んだ方が早そうです。

本題に戻ります。

testtimeがseqと同様に昇順となっているか確認する方法としてまず思いつくのは、以下のように自己結合を用いる方法でしょうか。


=> SELECT * FROM tempdata t1,tempdata t2
WHERE (t1.seq + 1 = t2.seq) AND (t1.testtime > t2.testtime) ;

seq | testtime | seq | testtime
-----+----------+-----+----------
(0 rows)

ただ、この方法ですと抜け番のない列がないと実施できません。


方法は色々とあると思いますが、個人的にはWindow関数をよく使っています。


row_numberを取得するために使われたことがある人も多いかと思いますが、普通の集約関数も大変便利に使えます。

例えば以下のように使えば、seqでソート後のテーブルに対して、上から順に各行時点での最大値を取得できます。


=> SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata ;

seq | testtime | max
-----+-------------------------------+-------------------------------
1 | 2014-12-04 23:19:49.001853+09 | 2014-12-04 23:19:49.001853+09
2 | 2014-12-04 23:20:48.001853+09 | 2014-12-04 23:20:48.001853+09
3 | 2014-12-04 23:21:49.001853+09 | 2014-12-04 23:21:49.001853+09
4 | 2014-12-04 23:22:32.001853+09 | 2014-12-04 23:22:32.001853+09
5 | 2014-12-04 23:23:55.001853+09 | 2014-12-04 23:23:55.001853+09
  :

後は、testtimeとmaxの値が異なる行が存在しないかを確認するだけです。


=> WITH r AS (SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata)
SELECT * FROM r WHERE testtime <> max ;

seq | testtime | max
-----+----------+-----
(0 rows)

因みに、今回のケースでは自己結合よりWindow関数を利用した方が約1.5倍ほど高速でした。



さて、ここで記事を終えても良いのですが、もう一つありがちな罠を紹介しておきます。


1回のSQLだけで整合性のあるデータを作らなければならないわけでは必ずしもありません。

データを作成後に、後から問題のあるデータを修正するというやり方もあるかと思います。


が、深く考えずにやってしまいますと、その修正により整合性が失われてしまうことがあります。


=> CREATE TEMP TABLE tempdata2 AS
SELECT generate_series(1,1000) seq,
current_timestamp + (generate_series(1,1000)||'days')::interval
- (floor(random() * 24)||'hours')::interval testtime ;

=> WITH r AS (SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata2)
SELECT * FROM r WHERE testtime <> max ;

seq | testtime | max
-----+----------+-----
(0 rows)

この時点では整合性が保たれているのですが、このクエリでは日付が相当未来になってしまいますので、interval型を利用して2年ほど日付を巻き戻しますと……


=> UPDATE tempdata2 SET testtime = testtime - interval '2 years';

=> WITH r AS (SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata2)
SELECT * FROM r WHERE testtime <> max ;

seq | testtime | max
-----+------------------------------+------------------------------
451 | 2014-02-28 07:27:44.77576+09 | 2014-02-28 18:27:44.77576+09
(1 row)

ご覧のように、整合性に問題のあるデータが発生してしまいました。

日付を見れば原因はすぐにお分かり頂けるかと思いますが、うるう年の影響です。


interval型は当然うるう年に対応していますが、対応した結果、

2016年2月29日 00:00:00の2年前は2014年2月28日 00:00:00となり、

2016年2月28日 12:00:00の2年前は2014年2月28日 12:00:00となります。


=> SELECT '2016-2-29 00:00:00'::timestamp - interval '2 years' col1 ,
'2016-2-28 12:00:00'::timestamp - interval '2 years' col2 ;

-[ RECORD 1 ]-------------
col1 | 2014-02-28 00:00:00
col2 | 2014-02-28 12:00:00

このように、前後関係が逆転してしまう可能性があるわけです。


「自分はこんなドジは踏まない」という方も多いかと思いますが、大量のテストデータを作成する場合は、僅かな意識漏れが結果として大きな違いを生む可能性があります。


テストデータを使ってテストをする前に、そのテストデータ自身をテストすることを強くお勧め致します。


12月7日は、nuko_yokohamaさんです。

(カンファレンスお疲れ様でした。私も行きたかったです……)

タグ:PostgreSQL
posted by hahasoha at 00:00| Comment(0) | TrackBack(0) | PostgreSQL