2024
September
13
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;
出力結果がこちら
日付 | A | B | C | D | E | F |
2023/08/01 | 1 | 1 | 1 | 0 | 0 | 0 |
2023/08/02 | 0 | 0 | 2 | 0 | 2 | 1 |
このカテゴリの最新記事
2023.07.03
間違いやすいSQLのコマンド5選
2023.11.28
サクラエディタで便利な機能について紹介
2024.07.16
【HTML】formaction属性でactionをbuttonやinputによって切り替える方法
2024.04.24
一開発者からみたRPAのメリットとデメリット