sql - 在 Ruby on Rails/Postgresql 中查找多个连续日期(日期时间)

Question

我们怎样才能找到X满足条件的 连续 日期(使用 hour )？

编辑:这里是 SQL fiddle http://sqlfiddle.com/#!17/44928/1

示例:

查找 3 个连续日期 where aa < 2和 bb < 6和 cc < 7

鉴于此表名为 weather :

<表类="s-表"><头>时间戳<日>啊 bb<日>抄送 <正文>01/01/2000 00:0015501/01/2000 01:0055501/01/2000 02:0015501/01/2000 03:0015501/01/2000 04:0015501/01/2000 05:00155

答案应返回 02:00, 03:00, 04:00 中的 3 条记录.

我们如何在 Ruby on Rails 中执行此操作 - 或者如果更好的话直接在 SQL 中执行此操作？

我开始研究基于这个答案的方法: Detect consecutive dates ranges using SQL

def consecutive_dates
  the_query = "WITH t AS (
    SELECT timestamp d,ROW_NUMBER() OVER(ORDER BY timestamp) i
    FROM @d
    GROUP BY timestamp
  )
  SELECT MIN(d),MAX(d)
  FROM t
  GROUP BY DATEDIFF(hour,i,d)"

  ActiveRecord::Base.connection.execute(the_query)
end

但我无法让它工作。

Answer 1

假设您每小时有一行，那么获取发生这种情况的第一小时的简单方法是使用 lead():

select t.*
from (select t.*,
             lead(timestamp, 2) over (order by timestamp) as timestamp_2
      from t
      where aa < 2 and bb < 6 and cc < 7
     ) t
where timestamp_2 = timestamp + interval '2 hour';

这会过滤条件并查看前面两行的行。如果提前两个小时，则连续三行符合条件。 注意:以上将同时返回 2020-01-01 02:00 和 2020-01-01 03:00。

从你的问题来看，你似乎只想要最早的。要处理这个问题，请同时使用 lag():

select t.*
from (select t.*,
             lag(timestamp) over (order by timestamp) as prev_timestamp
             lead(timestamp, 2) over (order by timestamp) as timestamp_2
      from t
      where aa < 2 and bb < 6 and cc < 7
     ) t
where timestamp_2 = timestamp + interval '2 hour' and
      (prev_timestamp is null or prev_timestamp < timestamp - interval '1' hour);

如果您真的需要原始行，您可以使用 generate_series() 生成额外的小时数:

select t.timestamp + n.n * interval '1 hour', aa, bb, cc
from (select t.*,
             lead(timestamp, 2) over (order by timestamp) as timestamp_2
      from t
      where aa < 2 and bb < 6 and cc < 7
     ) t cross join lateral
     generate_series(0, 2) n
where timestamp_2 = timestamp + interval '2 hour';

根据问题，您的数据似乎具有精确的时间戳，因此时间戳等式将起作用。如果真实数据更加模糊，则可以调整查询以将其考虑在内。