sql - 如何进行优化的 SQL 查询以检索每个时间片组中的多个记录

Question

我正在为这个项目使用 Postgress 数据库，但这个问题可以应用于任何数据库。

我需要更有效地从数据库中选择记录。我有一个事件表，其中包含在系统上注册的每个事件的记录。

Activity 表由以下列组成

id             integer 
time_stamp     time stamp without timezone
computer_id    varchar(255)
user_id        varchar(255)
...

一般来说我需要一个简单的结果。我需要能够指定 5 分钟或任何其他时间段的时间片。并且需要找出每个时间片中是否有记录。所以最后我需要每个时间片的 True 或 False 结果。

如果有 True，我需要用颜色给表格上色，如果有 False，我需要将其留空。

我做了一个程序解决方案，通过do while循环并为每个时间片选择一些记录。

例如:

select *
from Activity
where time_stamp between prev_Time and curr_Time 

这有效，但速度很慢。如果时间片是 5 分钟，则有 24 小时 x 60 分钟/5 分钟 = 288 个选择查询。

我需要找到一种方法来进行快速选择查询。我不会做存储过程。甚至更好的是不知道如何用 SQL 编写以 5 分钟为一组给我从开始时间到结束时间的所有时间段。

即使在代码中的任何解决方案都是可以接受的，但必须是快速的。响应时间不得超过 10 秒。

Answer 1

您可以使用 generate_series()创建间隔的函数，像这样:

select g, count(a.id)
from generate_series(
    '20131017 07:00'::timestamp,
    '20131017 08:00'::timestamp,
    '5 minutes'::interval
) as g
   left outer join Activity as a on
      a.time_stamp >= g and a.time_stamp < g + '5 minutes'::interval
group by g
order by g

如果不需要 count = 0 的记录，可以使用此查询:

with cte as (
    select
        (extract(epoch from time_stamp - '20131017 07:00'::timestamp) / 60)::int / 5 as p
    from Activity
    where time_stamp <= '20131017 08:00'::timestamp  
)
select
    '20131017 07:00'::timestamp + ((p * 5)::text || ' minutes')::interval, count(*)
from cte
group by p

sql fiddle demo