Postgres - 按时间间隔查询时间序列组
问题描述:
我有一个表格BusInfo,它存储了乘客上车的人数以及他/她可以占据多少个座位(取决于他/她拥有的行李数)。 我创建了一个捣鼓IT-Postgres - 按时间间隔查询时间序列组
http://sqlfiddle.com/#!15/88226/11
start end bus_name seats start_time ride_time
April, 28 2016 17:00:00 April, 28 2016 18:00:00 CA 2 1461862800 3600
April, 28 2016 17:30:00 April, 28 2016 18:30:00 CA 1 1461864600 3600
April, 28 2016 17:45:00 April, 28 2016 18:45:00 CA 2 1461865500 3600
April, 28 2016 17:00:00 April, 28 2016 19:00:00 CA 1 1461862800 7200
April, 28 2016 17:00:00 April, 28 2016 17:30:00 CA 2 1461862800 1800
我想运行一个查询其在10分钟interval.Something这样得到座位占用是预期的输出
datetime | seats occupied at the time
17:00 5
17:10 5
17:20 5
17:30 4
17:40 4
17:50 6
18:00 4
我试过,但没有接近 -
select to_char(to_timestamp(floor((extract('epoch' from to_timestamp(start_time))/ 600))*600),'yyyy/MM/dd HH24:MI'),
sum(seats) from businfo
where start_time >= 1461862800 and start_time <= 1461866400
and (start_time+ride_time) >= (1461862800)
group by to_char(to_timestamp(floor((extract('epoch' from
to_timestamp(start_time))/ 600))*600),'yyyy/MM/dd HH24:MI')
order by to_char(to_timestamp(floor((extract('epoch' from
to_timestamp(start_time))/ 600))*600),'yyyy/MM/dd HH24:MI') ASC
有什么想法?
答
一种方法是使用generate_series()
:
select gs.ts, sum(seats)
from generate_series('2016-04-28 17:00:00'::timestamp, '2016-04-28 18:00:00', interval '10 minute') gs(ts) join
businfo bi
on gs.ts between bi.start and bi.end
group by gs.ts
order by gs.ts;
感谢您的答复。查询返回了预期的结果,但尽管事实上我没有一个非常大的数据库,但它仍然很慢。我如何优化它?可能从添加索引开始。 – Harpreet
我能否建议你提出另一个问题,明确表示性能? –