時間ごとに横持ちで集計するSQL

こんな感じで持っているデータを

id	parent_id	created_at
1	101	1234567890
2	102	1234567891
3	101	1234567892
4	101	1234567893

こんな感じで集計するSQL。（想定結果なので集計値は適当）

parent_id	00	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23
101	0	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
102	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

たまーーにやるけど割と忘れるのでメモ。

下記はPostgreSQLだけど日付関数以外はMySQLとかでもだいたい同じはず。

select
  parent_id,
  sum(CASE WHEN hour = '00' THEN 1 ELSE 0 END) as "00",
  sum(CASE WHEN hour = '01' THEN 1 ELSE 0 END) as "01",
  sum(CASE WHEN hour = '02' THEN 1 ELSE 0 END) as "02",
  sum(CASE WHEN hour = '03' THEN 1 ELSE 0 END) as "03",
  sum(CASE WHEN hour = '04' THEN 1 ELSE 0 END) as "04",
  sum(CASE WHEN hour = '05' THEN 1 ELSE 0 END) as "05",
  sum(CASE WHEN hour = '06' THEN 1 ELSE 0 END) as "06",
  sum(CASE WHEN hour = '07' THEN 1 ELSE 0 END) as "07",
  sum(CASE WHEN hour = '08' THEN 1 ELSE 0 END) as "08",
  sum(CASE WHEN hour = '09' THEN 1 ELSE 0 END) as "09",
  sum(CASE WHEN hour = '10' THEN 1 ELSE 0 END) as "10",
  sum(CASE WHEN hour = '11' THEN 1 ELSE 0 END) as "11",
  sum(CASE WHEN hour = '12' THEN 1 ELSE 0 END) as "12",
  sum(CASE WHEN hour = '13' THEN 1 ELSE 0 END) as "13",
  sum(CASE WHEN hour = '14' THEN 1 ELSE 0 END) as "14",
  sum(CASE WHEN hour = '15' THEN 1 ELSE 0 END) as "15",
  sum(CASE WHEN hour = '16' THEN 1 ELSE 0 END) as "16",
  sum(CASE WHEN hour = '17' THEN 1 ELSE 0 END) as "17",
  sum(CASE WHEN hour = '18' THEN 1 ELSE 0 END) as "18",
  sum(CASE WHEN hour = '19' THEN 1 ELSE 0 END) as "19",
  sum(CASE WHEN hour = '20' THEN 1 ELSE 0 END) as "20",
  sum(CASE WHEN hour = '21' THEN 1 ELSE 0 END) as "21",
  sum(CASE WHEN hour = '22' THEN 1 ELSE 0 END) as "22",
  sum(CASE WHEN hour = '23' THEN 1 ELSE 0 END) as "23"
from (
  select *, to_char(to_timestamp(created_at), 'HH24') as hour from moge
) i group by parent_id order by parent_id;

上記みたいな集計をしたい場合はだいたいExcelに貼って集計したい用途だと思うので、PostgreSQLの場合のCSV吐き出す方法も併せて。

psql -U user -h 127.0.0.1 -p 5432 dbname -A -F $',' > out.csv < sum.sql

TSVの場合は -F $',' から -F $'\t' に変える。

ツー

日常の記録

時間ごとに横持ちで集計するSQL