PostgresSQLでカンマ区切りのデータを個別にカウントする方法

PostgreSQLでカンマ区切りのデータを個別にカウントしたい時があると思います。
そんな時にちょうどいい関数がありましたので紹介します。
※この記事はPostgreSQL 13.5 で動作確認してます。

その関数が
「regexp_split_to_table」

テーブル

id	要件	reg_date
1	A,B,C	2023/08/01 12:34:56
2	C,E,F	2023/08/02 09:00:00
3	C,E	2023/08/02 15:00:00

SELECT id,regexp_split_to_table(要件,',') AS 要件 FROM テーブル

この場合、要件のカラムの「,」で分割されて下記の様に出力されます。

id	要件	reg_date
1	A	2023/08/01 12:34:56
1	B	2023/08/01 12:34:56
1	C	2023/08/01 12:34:56
2	C	2023/08/02 09:00:00
2	E	2023/08/02 09:00:00
2	F	2023/08/02 09:00:00
3	C	2023/08/02 15:00:00
3	E	2023/08/02 15:00:00

このデータを集計すると下記の様になります。

SELECT 
   SUM(CASE 要件 WHEN 'A' THEN 1 ELSE 0 END) AS A
  ,SUM(CASE 要件 WHEN 'B' THEN 1 ELSE 0 END) AS B
  ,SUM(CASE 要件 WHEN 'C' THEN 1 ELSE 0 END) AS C
  ,SUM(CASE 要件 WHEN 'D' THEN 1 ELSE 0 END) AS D
  ,SUM(CASE 要件 WHEN 'E' THEN 1 ELSE 0 END) AS E
  ,SUM(CASE 要件 WHEN 'F' THEN 1 ELSE 0 END) AS F
FROM (
  SELECT id,regexp_split_to_table(要件,',') AS 要件
  FROM テーブル
) tbl

出力結果がこちら

A	B	C	D	E	F
1	1	3	0	2	1

行列変換がちょっと面倒ですが、これで集計ができました！

ついでに日付毎に集計を取る場合は下記のようにすると出力されます。

SELECT 
   date_trunc('day', reg_date)::date AS 日付,
  ,SUM(CASE 要件 WHEN 'A' THEN 1 ELSE 0 END) AS A
  ,SUM(CASE 要件 WHEN 'B' THEN 1 ELSE 0 END) AS B
  ,SUM(CASE 要件 WHEN 'C' THEN 1 ELSE 0 END) AS C
  ,SUM(CASE 要件 WHEN 'D' THEN 1 ELSE 0 END) AS D
  ,SUM(CASE 要件 WHEN 'E' THEN 1 ELSE 0 END) AS E
  ,SUM(CASE 要件 WHEN 'F' THEN 1 ELSE 0 END) AS F
FROM (
  SELECT id,regexp_split_to_table(要件,',') AS 要件
  FROM テーブル
) tbl
GROUP BY date_trunc('day', reg_date)::date
ORDER BY 日付 ASC;

出力結果がこちら