最好的办法，以计算任意时间间隔记录的Rails + Postgres的(Best way to co

我的应用程序有一个Events有时间标记的事件表。

我需要每一个最近的过程中报告事件数N的时间间隔。对于不同的报告，间隔可能是“每周”或“每一天”或“每个小时”或“每15分钟间隔”。

例如，用户可以显示他们接到的订单数目每星期，日或小时或一刻钟。

1）我的选择是通过任意的时间间隔动态做一个SQL查询（我使用Postgres的）那组。有没有办法做到这一点？

2）容易，但丑陋的蛮力的方法是做对按时间戳排序的开始/结束时间范围内的所有记录单查询，然后有一个方法手工打造以任何间隔相符。

3）另一种方法是单独的字段添加到事件表中的每个间隔和静态存储一个the_week the_day ， the_hour和the_quarter_hour字段，以便予取在记录被创建（一次时的“打”），而不是每次我在那场报告。

这里有什么最好的做法，鉴于我可以（在表格的宽度加倍的温和费用虽然）修改如果需要的模型和前店间隔数据？

幸运的是，你正在使用PostgreSQL。窗口函数generate_series()是你的朋友。

测试用例

考虑下面的测试表（你应该已经提供）：

CREATE TABLE event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series(timestamp '2018-05-01'
                     , timestamp '2018-05-08'
                     , interval '7 min') + random() * interval '7 min';

为每7分钟一个事件（加上0至7分钟，随机地）。

，基本解决方案

此查询计算的任意时间间隔事件。在实施例17分：

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
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 start_time
ORDER  BY start_time;

查询所检索的最小和最大ts从基表，以覆盖完整的时间范围。您可以使用任意的时间范围来代替。
根据需要提供任何时间间隔 。
生产的每时隙一行。如果这期间没有发生事件，计数为0 。
一定要处理的上下正确绑定：
- 从SQL查询意外的结果与时间戳之间
窗口函数lead()有一个经常被忽视的特点：它能够提供在不导致行存在一个默认。提供了'infinity'的例子。否则最后的间隔将与一个上限被切断NULL 。

最小当量

上述查询使用CTE和lead()和详细的语法。优雅，也许更容易理解，但有点贵。这里是一个短，速度更快，最低版本：

SELECT start_time, count(e.ts) AS events
FROM  (SELECT generate_series(min(ts), max(ts), interval '17 min') FROM event) g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '17 min'
GROUP  BY 1
ORDER  BY 1;

例如，对于“每15在过去一周分钟”`

而与格式化to_char()

SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), count(e.ts) AS events
FROM   generate_series(date_trunc('day', localtimestamp - interval '7 days')
                     , localtimestamp
                     , interval '15 min') g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '15 min'
GROUP  BY start_time
ORDER  BY start_time;

不过ORDER BY和GROUP BY底层时间戳值，而不是在格式化字符串。这是更快，更可靠。

分贝<>小提琴这里

相关答案产生在时间框架内运行计数 ：