在Rails + Postgres中按任意时间间隔计数记录的最佳方法是什么？
在Rails + Postgres中按任意时间间隔计数记录的最佳方法是什么？
提问于 2018-04-03 05:17:03
我的应用程序有一个Events带有时间戳事件的表格。
我需要在每个最近的N时间间隔内报告事件的计数。对于不同的报告，间隔可以是“每周”或“每天”或“每小时”或“每15分钟的间隔”。
例如，用户可以显示他们每周，每天，每小时或每小时收到的订单数量。
1）我的首选是动态地执行一个SQL查询（我使用Postgres），它按任意时间间隔进行分组。有没有办法做到这一点？
2）一种简单但丑陋的蛮力方法是在按时间戳排序的开始/结束时间范围内对所有记录执行单个查询，然后以任意间隔手动构建标记。
3）另一种方法是在每个时间间隔的事件表中添加单独的字段，并静态存储一个the_week the_day，，the_hour和the_quarter_hour字段，以便在创建记录时（一次）记录'hit'，而不是每次报告该字段时。
这里最好的做法是，如果需要的话，我可以修改模型并预先存储间隔数据（虽然在表格宽度加倍的情况下花费适中）？
回答 1
Stack Overflow用户
发布于 2018-04-03 15:15:13
幸运的是，你正在使用PostgreSQL。generate_series()（Postgres具体），window 函数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;
- 该查询
timestamp从基表自动收集最小值和最大值，以覆盖表的完整时间范围。您可以改为使用任意时间范围。 - 输入一次您的选择间隔。可以是任何你能想到的时间间隔。
- 结果中的每个时间间隔都有一行。如果没有事件发生，你会得到一个计数
0。 - 确保正确处理上下边框：
- 对于时间范围内的运行计数
“过去一周每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, 1, 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, '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;
SQLfiddle显示所有。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100003253
复制相关文章
相似问题