PostgreSQL 9.5.2
»
create table date_trunc_example (ts timestamp, val numeric(9,2)); copy date_trunc_example from '/date_trunc_example.tsv' (format csv, delimiter E'\t', header true); select * from date_trunc_example; +----------------------------+---------+ | ts | val | +----------------------------+---------+ | 2016-06-03 17:11:50.481619 | 79280.3 | | 2016-06-03 17:26:40.752305 | 73544.0 | | 2016-06-03 17:27:27.756976 | 73767.6 | | 2016-06-03 17:29:07.201976 | 73270.7 | | 2016-06-03 17:29:33.727976 | 70783.2 | | 2016-06-03 17:29:51.441976 | 70286.2 | | 2016-06-03 17:30:08.496976 | 72505.6 | | 2016-06-03 17:30:17.230976 | 70834.9 | | 2016-06-03 17:30:38.500976 | 70561.5 | | 2016-06-03 17:30:51.788976 | 70109.1 | ...
↓ テンプレその
with your_table (ts, val) as ( select ts, val from date_trunc_example ), your_settings (start, intv) as ( values(interval '12hour', interval '3hour') ), bounds (min, max) as ( select min(ts), max(ts) from your_table ), ranges (tsr) as ( select tsrange(ts, ts + intv) from bounds, your_settings, generate_series( date_trunc('day', min) + start, max, intv) as ts ) select tsr, count(val) from ranges left-- comment out to exclude times without data join your_table on tsr @> ts group by 1 order by 1; +-----------------------------------------------+-------+ | tsr | count | +-----------------------------------------------+-------+ | ["2016-06-03 12:00:00","2016-06-03 15:00:00") | 0 | | ["2016-06-03 15:00:00","2016-06-03 18:00:00") | 70 | | ["2016-06-03 18:00:00","2016-06-03 21:00:00") | 0 | | ["2016-06-03 21:00:00","2016-06-04 00:00:00") | 0 | | ["2016-06-04 00:00:00","2016-06-04 03:00:00") | 0 | | ["2016-06-04 03:00:00","2016-06-04 06:00:00") | 0 | | ["2016-06-04 06:00:00","2016-06-04 09:00:00") | 0 | | ["2016-06-04 09:00:00","2016-06-04 12:00:00") | 0 | | ["2016-06-04 12:00:00","2016-06-04 15:00:00") | 0 | | ["2016-06-04 15:00:00","2016-06-04 18:00:00") | 0 | | ["2016-06-04 18:00:00","2016-06-04 21:00:00") | 0 | | ["2016-06-04 21:00:00","2016-06-05 00:00:00") | 0 | | ["2016-06-05 00:00:00","2016-06-05 03:00:00") | 614 | | ["2016-06-05 03:00:00","2016-06-05 06:00:00") | 1820 | | ["2016-06-05 06:00:00","2016-06-05 09:00:00") | 1823 | | ["2016-06-05 09:00:00","2016-06-05 12:00:00") | 1730 | | ["2016-06-05 12:00:00","2016-06-05 15:00:00") | 1686 | | ["2016-06-05 15:00:00","2016-06-05 18:00:00") | 1311 | | ["2016-06-05 18:00:00","2016-06-05 21:00:00") | 416 | | ["2016-06-05 21:00:00","2016-06-06 00:00:00") | 116 | | ["2016-06-06 00:00:00","2016-06-06 03:00:00") | 1575 | | ["2016-06-06 03:00:00","2016-06-06 06:00:00") | 0 | | ["2016-06-06 06:00:00","2016-06-06 09:00:00") | 0 | ...
↓ テンプレその
with your_table (ts, val) as ( select ts, val from date_trunc_example ), your_settings (start, intv) as ( values(interval '12hour', interval '3hour') ), bounds (min, max) as ( select min(ts), max(ts) from your_table ), ranges (tsr) as ( select tsrange(ts, ts + intv) from bounds, your_settings, generate_series( date_trunc('day', min) + start, max, intv) as ts ) select tsr, count(val) from ranges -- left-- comment out to exclude times without data join your_table on tsr @> ts group by 1 order by 1; +-----------------------------------------------+-------+ | tsr | count | +-----------------------------------------------+-------+ | ["2016-06-03 15:00:00","2016-06-03 18:00:00") | 70 | | ["2016-06-05 00:00:00","2016-06-05 03:00:00") | 614 | | ["2016-06-05 03:00:00","2016-06-05 06:00:00") | 1820 | | ["2016-06-05 06:00:00","2016-06-05 09:00:00") | 1823 | | ["2016-06-05 09:00:00","2016-06-05 12:00:00") | 1730 | | ["2016-06-05 12:00:00","2016-06-05 15:00:00") | 1686 | | ["2016-06-05 15:00:00","2016-06-05 18:00:00") | 1311 | | ["2016-06-05 18:00:00","2016-06-05 21:00:00") | 416 | | ["2016-06-05 21:00:00","2016-06-06 00:00:00") | 116 | | ["2016-06-06 00:00:00","2016-06-06 03:00:00") | 1575 | | ["2016-06-07 15:00:00","2016-06-07 18:00:00") | 147 | | ["2016-06-08 09:00:00","2016-06-08 12:00:00") | 39 | | ["2016-06-08 12:00:00","2016-06-08 15:00:00") | 1464 | | ["2016-06-08 18:00:00","2016-06-08 21:00:00") | 1131 | | ["2016-06-08 21:00:00","2016-06-09 00:00:00") | 1289 | | ["2016-06-09 00:00:00","2016-06-09 03:00:00") | 2480 | | ["2016-06-09 03:00:00","2016-06-09 06:00:00") | 362 | +-----------------------------------------------+-------+ (17 rows)
WITH
ストアド化して集計の起点・時間間隔を引数で受け取るようにすればもっと便利かも。そのうち試します。