英文:
Determine that no records have been created for 4 days in table on Postgresql
问题
有一个名为"pressure"的表，包括以下字段：
- id - 整数
- value - 整数
- created_at - 时间戳
是否有可能确定是否存在连续四天或更多天的时间窗口，在这段时间内没有创建任何记录。通常情况下，有很多记录，但通常记录的创建中断不会超过1到2天。
英文:
There is a table "pressure" with fields:
id - integer
value - integer
created_at - timestamp
Is it possible to determine if there was a window of more than 4 days in a row when no records were created. That is, if the period is 4 days in a row, when there were no records. There are a lot of records. But usually a break in the creation of records is no more than 1 - 2 days.
答案1
得分: 1
Postgres 允许对日期/时间戳进行减法运算，其结果是一个间隔。然后，您将该结果与特定的兴趣间隔进行比较。lag()函数提供对先前行的访问，因此提供了先前的created_at值。所以：（请参见demo）
select sq.*
from (select p.*
, lag(p.created_at) over(order by p.created_at) previous_date
, p.created_at - lag(p.created_at) over(order by p.created_at) days_between
from pressure p
) sq
where sq.days_between > interval '4 days';
英文:
Postgres permits subtraction of dates/timestamps, the result of which is an interval. You then compare that result to the specific interval of interest. The lag() function provides access to the prior row thus the previous created_at value. So: (see demo)
select sq.*
from (select p.*
, lag(p.created_at) over(order by p.created_at) previous_date
, p.created_at - lag(p.created_at) over(order by p.created_at) days_between
from pressure p
) sq
where sq.days_between > interval '4 days';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论