我需要在最近的N个时间间隔内报告事件计数.对于不同的报告,间隔可以是“每周”或“每一天”或“每小时”或“每15分钟间隔”.
例如,用户可以显示他们每周,每天或每小时或四分之一小时收到的订单数量.
1)我的偏好是动态地做一个单一的SQL查询(我正在使用Postgres)按任意时间间隔进行分组.有办法吗？
2)一个简单但丑的强力方式是对按时间戳排序的开始/结束时间范围内的所有记录执行单个查询,然后通过任意间隔手动构建一个计数.
3)另一种方法是将每个间隔的事件表添加单独的字段,并静态存储the_week the_day,the_hour和the_quarter_hour字段,以便在创建记录时(一次)而不是每次我关于该领域的报告.
这里最好的做法是,如果需要,我可以修改模型和预先存储间隔数据(尽管以适度的费用将表格宽度加倍)？
解决方法
generate_series()(Postgres具体),window function lead()和LEFT JOIN是你的朋友：
测试用例
给定以下测试表(您应该提供)：
CREATE TEMP TABLE event(event_id serial,ts timestamp);
INSERT INTO event (ts)
SELECT generate_series('2015-04-17 0:0'::timestamp,'2015-04-20 0:0'::timestamp,interval '7 min') + random() * interval '7 min';
每7分钟一次(0到7分钟随机).
基本解决方案
该查询对任意时间间隔的事件进行计数. 17分钟的例子：
WITH grid AS (
SELECT start_time,lead(start_time) OVER (ORDER BY start_time) AS end_time
FROM (
SELECT generate_series(min(ts),max(ts),interval '17 min') AS start_time
FROM event
) x
)
SELECT start_time,count(e.ts) AS events
FROM grid g
LEFT JOIN event e ON e.ts >= g.start_time
AND e.ts < g.end_time
GROUP BY 1
ORDER BY 1;
>查询从基表自动收集最小和最大时间戳以涵盖表的完整时间范围.
您可以改为使用任意时间范围.
>输入您选择的间隔时间.可以任何时间间隔你可以想到.
>结果中每个时间间隔的一行.如果没有事件发生,你会得到0的计数.
>确保正确处理上下边框：
PostgreSQL Error on Heroku with “BETWEEN”
>对于时间范围内的运行计数,请参阅此相关答案：
PostgreSQL: running count of rows for a query ‘by minute’
“过去一周每15分钟”一例
基本格式与to_char().
WITH grid AS (
SELECT start_time,lead(start_time) OVER (ORDER BY start_time) AS end_time
FROM generate_series(now()::date - 7 -- truncates to start of day,now(),interval '15 min') AS start_time
)
SELECT to_char(start_time,'YYYY-MM-DD HH24:MI'),count(e.ts) AS events
FROM grid g
LEFT JOIN event e ON e.ts >= g.start_time
AND e.ts < g.end_time
GROUP BY start_time
ORDER BY start_time;
角箱最后一排
要舍弃它,你可以使用：
lead(start_time,1,now()) OVER (ORDER BY start_time) AS end_time
..其中now()是您的示例的时间范围的上边界.否则最后一个间隔将被截断,因为上边框为NULL.
当没有前导行存在时,lead()经常被忽略,提供默认的功能.
一般解决方案覆盖角箱
WITH vals AS (
SELECT now()::date - 7 AS frame_start -- enter values once,now() AS frame_end,interval '15 min' AS t_interval
),grid AS (
SELECT start_time,lead(start_time,frame_end) OVER (ORDER BY start_time) AS end_time
FROM (
SELECT generate_series(frame_start,frame_end,t_interval) AS start_time,frame_end
FROM vals
) x
)
SELECT to_char(start_time,count(e.ts) AS events
FROM grid g
LEFT JOIN event e ON e.ts >= g.start_time
AND e.ts < g.end_time
GROUP BY start_time
ORDER BY start_time;
SQL Fiddle显示全部.
