sql - 计数按开始和结束时间分组的条目

Question

我在 PostgreSQL 中有下表存储事件的开始和结束时间:

CREATE TABLE foo
AS
  SELECT id, name, startTime::timestamp, endTime::timestamp
  FROM ( VALUES
    ( 1, 'A', '2017-05-19T12:21:18+00:00', '2017-05-19T15:31:18+00:00' ),
    ( 2, 'B', '2017-05-19T12:35:18+00:00', '2017-05-19T12:48:18+00:00' ),
    ( 3, 'C', '2017-05-19T13:00:18+00:00', '2017-05-19T13:31:18+00:00' ),
    ( 4, 'D', '2017-05-19T13:11:18+00:00', '2017-05-19T13:27:18+00:00' ),
    ( 5, 'E', '2017-05-19T13:45:18+00:00', '2017-05-19T14:55:18+00:00' )
  ) AS (id, name, startTime, endTime);

假设我想从给定的开始和结束时间开始按 15 分钟的时间段对这些条目进行分组。因此，例如从 2017-05-19 12:00 到 2017-05-19 14:00 的时间，我想收到这样的信息:

date                      | count
---------------------------------
2017-05-19T12:00:00+00:00 | 0         (A expected)
2017-05-19T12:15:00+00:00 | 1         (A, B expected)
2017-05-19T12:30:00+00:00 | 2         (A, B expected)
2017-05-19T12:45:00+00:00 | 2         (A, C, D expected)
2017-05-19T13:00:00+00:00 | 3         (A, C, D expected)
2017-05-19T13:15:00+00:00 | 3         (A, C expected)
2017-05-19T13:30:00+00:00 | 2         (A, E expected)
2017-05-19T13:45:00+00:00 | 2         (A, E expected)

如何在PostrgreSQL中以最简单的方式实现？

Answer 1

with my_table(id, name, startTime, endTime) as (
values
    (1, 'A', '2017-05-19T12:21:18+00:00'::timestamp, '2017-05-19T15:31:18+00:00'::timestamp),
    (2, 'B', '2017-05-19T12:35:18+00:00', '2017-05-19T12:48:18+00:00'),
    (3, 'C', '2017-05-19T13:00:18+00:00', '2017-05-19T13:31:18+00:00'),
    (4, 'D', '2017-05-19T13:11:18+00:00', '2017-05-19T13:27:18+00:00'),
    (5, 'E', '2017-05-19T13:45:18+00:00', '2017-05-19T14:55:18+00:00')
)

select date, count(id), string_agg(name, ', ') as names
from generate_series('2017-05-19 12:00:00'::timestamp, '2017-05-19 14:00:00', '15m'::interval) as date
left join my_table t on tstzrange(date, date+ '15m') && tstzrange(t.starttime, t.endtime)
group by 1
order by 1;

        date         | count |  names  
---------------------+-------+---------
 2017-05-19 12:00:00 |     0 | 
 2017-05-19 12:15:00 |     1 | A
 2017-05-19 12:30:00 |     2 | A, B
 2017-05-19 12:45:00 |     2 | A, B
 2017-05-19 13:00:00 |     3 | A, C, D
 2017-05-19 13:15:00 |     3 | A, C, D
 2017-05-19 13:30:00 |     2 | A, C
 2017-05-19 13:45:00 |     2 | A, E
 2017-05-19 14:00:00 |     2 | A, E
(9 rows)