PostgreSQLでもOracleのPIVOTみたいなことをやりたいなぁ・・・と思って調べた。
crosstabらしい。
拡張機能らしい。
早速PgAdminで以下のSQLを実行して取り込む。
CREATE EXTENSION IF NOT EXISTS tablefunc;
いくつか関数が追加された中に、crosstabがあればOK。
で、続けてSQLを組み立てる。
今回は以下のように縦並びである店舗日別データ(qty)を全店SUM(qty)にして横並びにしてみる。
tab_shop_qty カラム構成(y |m |d |no |shop | qty)
CREATE TABLE tab_shop_qty (
y integer,
m integer,
d integer,
no integer,
shop character varying(10),
qty numeric(5,0)
);
INSERT INTO public.tab_shop_qty values(2019,1,1,1, 'abc', 10);
INSERT INTO public.tab_shop_qty values(2019,1,1,2, 'abc', 8);
INSERT INTO public.tab_shop_qty values(2019,1,2,1, 'abc', 25);
INSERT INTO public.tab_shop_qty values(2019,1,2,2, 'abc', 15);
INSERT INTO public.tab_shop_qty values(2019,1,1,1, 'efg', 12);
INSERT INTO public.tab_shop_qty values(2019,1,1,2, 'efg', 7);
INSERT INTO public.tab_shop_qty values(2019,1,1,2, 'efg', -2);
INSERT INTO public.tab_shop_qty values(2019,1,2,1, 'hij', 20);
店舗(shop)別に、日毎のqtyを横に並べる
SELECT c.shop,c."1",c."2",...,c."31"
FROM crosstab(
'SELECT shop,d,SUM(qty) qty FROM tab_shop_qty WHERE y=2019 AND m=1 GROUP BY shop,d ORDER BY 1'
,'SELECT d FROM generate_series(1,31) d'
)
AS c(shop varchar,"1" numeric,"2" numeric,...,"31" numeric)
ORDER BY c.shop
上の例だと、shopというカラム１つだけがキーになっているのでこのようなSQLで済むのだが、キーが複数カラムある場合は、第1引数のSQL文にORDER BY句を追加するという工夫が必要。
店舗,NO(shop,no)別に、日毎のqtyを横に並べる
SELECT c.shop,c.no,c."1",c."2",...,c."31"
FROM crosstab(
'SELECT shop,no,d,SUM(qty) qty FROM tab_shop_qty WHERE y=2019 AND m=1 GROUP BY shop,no,d ORDER BY 2'
,'SELECT d FROM generate_series(1,31) d'
)
AS c(shop varchar,"1" numeric,"2" numeric,...,"31" numeric)
ORDER BY c.shop,c.no
ドキュメントurl: https://www.postgresql.jp/document/9.6/html/tablefunc.html では、
第1引数のORDER BY句は
「実際は、同じrow_name(キー項目のこと)を持つ値をまとめられるように、source_sql(第1引数のSQL文のこと)問い合わせでは常にORDER BY 1を指定すべきです。」
との記載があるが、「ORDER BY 1」なのであれば指定ありとなしでは変わりはなかった。
結局、ORDER BYの指定の仕方がイマイチわからなかったが、ORDER BYの1つめで指定したカラム数まではキー、次が第2引数のデータとの関連付け用、その次がPIVOTとして出力するデータ、かな・・・などと思った。
————————
＜2019/08/08 追記＞
いろいろ試していた時に発覚したことがあったので追記。
crosstabの第１引数のSQL文だが、グルーピングするカラムのうち、１つでもデータが１種類しかないカラムが存在した場合、うまく動作しないことが分かった。
上の説明文だと自分で後から見直しても、なんのこっちゃ！って思うと推測するので、追記しておくと・・・・
最後の例だとshop,noの2カラムがグループ,dが横並びにするカラム,qtyが横並びにするデータとなるのだが、
テーブルのレコードにshop＝’abc’のデータ１種類しかない場合、crosstabがうまく動作しないということがある、ということだ。
ORDER BY 2までならうまくいくかもしれないが、少なくとも私が試してみたORDER BY 3（つまりグルーピングするカラムが3列）の場合、うまくマージされず、本来は何全行と出るはずの結果レコードが1行しか出てこない始末となった。
う～ん、なんとかなんないのだろうか。他にうまくできる方法を探さなければ、「関数作成してカーソル利用」しか思いつく方法がない・・・