postgreSQLのselect文でデータを整理する構文を教えてください。group by , count , to_chart etc...

Question

上記図のようにpostgreSQLでeample_tableの内容をselect文を用いて右のように出力したいと思っております。

実施したことは各項目のデータは出力できるのですが、これを1文でまとめて図のように出力する場合構文はどのようにすれば出力できますでしょうか。

ご教示よろしくお願いいたします。

①
> select
> to_char(date_at,'yyyy-mm')  as month
> from
> example_table 
> group by to_char(date_at,'yyyy-mm');

  month
---------
 2021-01
 2021-02
 2021-03
(3 rows)


②
> select
> COUNT(flag) as flagA
> from
> example_table
> where flag='0';
 flagA
-------------------
                 8
(1 row)

③
> select
> COUNT(flag) as flagB
> from
> user_info
> where flag='1';
 flagB
-------------------
                 2
(1 row)

Answer 1

Postgresql 9.6以上であれば、crosstab(text source_sql, text category_sql)を使えば表の形にできます。

CREATE TABLE example_table(date_at date, flag int);
INSERT INTO example_table values('2020/01/01', 0);
INSERT INTO example_table values('2020/01/02', 0);
INSERT INTO example_table values('2020/01/03', 0);
INSERT INTO example_table values('2020/02/01', 0);
INSERT INTO example_table values('2020/02/02', 0);
INSERT INTO example_table values('2020/03/01', 0);
INSERT INTO example_table values('2020/03/02', 0);
INSERT INTO example_table values('2020/03/03', 0);
INSERT INTO example_table values('2020/03/01', 1);
INSERT INTO example_table values('2020/03/02', 1);

-- 今後結果を得るために利便性の高いview(monthly_result)を作成しておきます。
CREATE VIEW monthly_result AS
select
 to_char(date_at,'yyyy-mm')  as month,
 'flag' || flag::text as name,
 COUNT(*) as num
from
 example_table 
group by
 to_char(date_at,'yyyy-mm'), 'flag' || flag::text;

-- crosstabを有効にするのに必要です。各データベースで1度実行すればOKです。
CREATE EXTENSION tablefunc;

-- 次回以降は以下のクエリを発行すれば表が取得できます。
SELECT * FROM crosstab(
	'SELECT month, name, num FROM monthly_result ORDER BY 1, 2')
AS (
	"month" text, "flag0" bigint, "flag1" bigint
);

結果

month,     flag0, flag1
"2020-01", 3,
"2020-02", 2,
"2020-03", 3,     2

monthly_resultは以下のような表を返します。

month,   name,  num
2021-01, flag0, 3
2021-02, flag0, 2
2021-03, flag0, 3
2021-03, flag1, 2

参考

tablefunc - EXTENSIONの説明ページです。

Answer 2

ちゃんとマニュアルを読んでSQLを実行して覚えます。

group by , count , to_chart etc

というところまでわかっているなら、CASE も取り込めば完成です。

Answer 3

select
to_char(date_at,'yyyy-mm') as month,
COUNT(case when flag='0' then flag else NULL end) as flagA,
COUNT(case when flag='1' then flag else NULL end) as flagB
from
example_table
group by to_char(date_at,'yyyy-mm');

できました。こういうイメージかと思います。ありがとうございます。