crosstabviewでメリークリスマス!

  • 4
    いいね
  • 0
    コメント

この記事はPostgreSQL Advent Calendar 2016の25日目の記事です。

はじめに

直前まで何書くか全然決めていなかったのですが、PGConf.Asia 2016(12/1~12/3)に参加し、nukoさんのLTでcrosstabviewの存在を知り、今回調べてみることにしました。

crosstabview

PostgreSQL9.6のpsqlで利用できるようになった機能です。
マニュアルに記載してある通り、問い合わせの結果をクロス表形式で出力します。

Example
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の碁盤を見ていたら、グラフを描きたくなったのです。エクセル方眼紙ストであれば当然の結果です。
ということで、やってみよう!

Example2
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の第4パラメータで水平方向の並び順を制御できます。ただし、このパラメータは整数値のみ与えられるとのことなので↑のようにx_idxとかy_idxで順位づけしています。

おわりに

2016年最後を飾るのにふさわしくない誰得内容でしたが、これで皆さんもpsql方眼紙ストです!

Example3
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}

です。フルタチさんも大喜びですね。

それでは、、、
メリークリスマス!&ハッピーニューイヤー!!

この投稿は PostgreSQL Advent Calendar 201625日目の記事です。