これは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
