この記事はPostgreSQL Advent Calendar 2016の25日目の記事です。
はじめに
直前まで何書くか全然決めていなかったのですが、PGConf.Asia 2016(12/1～12/3)に参加し、nukoさんのLTでcrosstabviewの存在を知り、今回調べてみることにしました。
crosstabview
PostgreSQL9.6のpsqlで利用できるようになった機能です。
マニュアルに記載してある通り、問い合わせの結果をクロス表形式で出力します。
create table uriage(d date, b text, v int);
insert into uriage values ('2016-12-23', 'Tokyo', 100);
insert into uriage values ('2016-12-24', 'Tokyo', 150);
insert into uriage values ('2016-12-25', 'Tokyo', 130);
insert into uriage values ('2016-12-23', 'Osaka', 80);
insert into uriage values ('2016-12-25', 'Osaka', 100);
insert into uriage values ('2016-12-24', 'Nagoya', 90);
insert into uriage values ('2016-12-25', 'Yokohama', 120);
insert into uriage select '9999-12-31'::date, b, sum(v::int) from uriage group by b;
insert into uriage select d, 'Zentai', sum(v::int) from uriage group by d;
select * from uriage ;
¥crosstabview d b v
b | 2016-12-23 | 2016-12-24 | 2016-12-25 | 9999-12-31
----------+------------+------------+------------+------------
Tokyo | 100 | 150 | 130 | 380
Osaka | 80 | | 100 | 180
Nagoya | | 90 | | 90
Yokohama | | | 120 | 120
Zentai | 180 | 240 | 350 | 770
(5 rows)
統計とか集計とかに明るくないのでよくわかりませんが、こういうのが便利なんでしょう。きっと。
より詳しくはPostgreSQLのwikiにも記載がありますので、参照してください（英語です）。
本題
統計とか集計とかに詳しくないのに、この機能に興味を持ったのはある理由があります。
pg_reversiの碁盤を見ていたら、グラフを描きたくなったのです。エクセル方眼紙ストであれば当然の結果です。
ということで、やってみよう！
create table test(x numeric, y numeric, v text);
insert into test select g::numeric(10,5), (sin(g))::numeric(10,5), '*' from generate_series( (-1.0)::numeric, (1.0)::numeric, 0.1) g;
¥t on
¥a
¥f ' '
select dense_rank() over (order by x) x_idx, dense_rank() over (order by y) y_idx, x::numeric(10,1), y::numeric(10,1), v from test order by y desc;
¥crosstabview y x v x_idx
1.0 * * * * * *
0.9 * * * * *
0.8 * * * *
0.7 * * * *
0.6 * * * *
0.5 * * *
0.4 * * * *
0.3 * * *
0.2 * * *
0.1 * * *
0.0 * * *
-0.1 * * *
-0.2 * * *
-0.3 * * *
-0.4 * * * *
-0.5 * * *
-0.6 * * * *
-0.7 * * * *
-0.8 * * * *
-0.9 * * * * *
-1.0 * * * * * *
（注）ディスプレイサイズの都合、目盛幅0.1の精度になっています。
¥tでタプルオンリーにしたり、¥aで無整列出力にしたり、¥fで半角スペース2つをセパレータにしたりしてるところが涙ぐましいですが、無事に正弦波を視覚的に捉えることができました！
ちなみに、¥crosstabviewの第４パラメータで水平方向の並び順を制御できます。ただし、このパラメータは整数値のみ与えられるとのことなので↑のようにx_idxとかy_idxで順位づけしています。
おわりに
２０１６年最後を飾るのにふさわしくない誰得内容でしたが、これで皆さんもpsql方眼紙ストです！
truncate test ;
insert into test select g::numeric(10,5), (|/(abs(g))) + ((|/(6::numeric(10,1))-(g::numeric(10,1)^2)))::numeric(10,5), '*' from generate_series( (-1 * (|/(6::numeric)))::numeric, (|/(6::numeric))::numeric, 0.1) g;
insert into test select g::numeric(10,5), (|/(abs(g))) - ((|/(6::numeric(10,1))-(g::numeric(10,1)^2)))::numeric(10,5), '*' from generate_series( (-1 * (|/(6::numeric)))::numeric, (|/(6::numeric))::numeric, 0.1) g;
select dense_rank() over (order by x) x_idx, dense_rank() over (order by y) y_idx, x::numeric(10,1), y::numeric(10,1), v from test order by y desc;
¥crosstabview y x v x_idx
3.3 * * * *
3.2 * * * * * * * * * * * * * *
3.1 * * * * * * * *
3.0 * * * * *
2.9 * * * *
2.8 * * * *
2.7 * * * *
2.6 *
2.5 *
2.4 *
2.3 *
2.1 *
2.0 *
1.1 *
1.0 *
0.7 * *
0.4 * *
0.2 * *
0.0 * *
-0.1 *
-0.2 *
-0.3 * *
-0.4 *
-0.5 *
-0.6 * *
-0.7 * *
-0.8 * *
-0.9 *
-1.0 * *
-1.1 * *
-1.2 * *
-1.3 * *
-1.4 * * *
-1.5 * *
-1.6 * *
-1.7 * * *
-1.8 * *
-1.9 * *
-2.1 * *
-2.2 * *
ちなみに↑の数式は、
y=\sqrt{|x|}\pm\sqrt{6-x^2} \\
-\sqrt{6} \le x \le \sqrt{6}
です。フルタチさんも大喜びですね。
それでは、、、
メリークリスマス！＆ハッピーニューイヤー！！