主页 / dba / 问题 / 61097
Accepted
Phill Pafford
Asked: 2014-03-18 10:39:57 +0800 CST

比较结果中的日期

  • 772

我正在寻找比较结果集中的日期

例子：

SELECT * FROM tbl WHERE foo=bar

输出

id, datetime, name
1, 2014-01-01 18:13:45, test 1
2, 2014-01-01 19:01:32, test 2
3, 2014-01-01 20:34:44, test 3
4, 2014-01-03 04:45:22, test 4

如果时间差异小于或大于前一个记录的 24 小时，我希望能够在每条记录之间进行区分。

例子：

1, 2014-01-01 18:13:45, test 1 <-- first record since the previous record
2, 2014-01-01 19:01:32, test 2 <-- second record was less than 24 hours since the previous record
3, 2014-01-01 20:34:44, test 3 <-- third record was less than 24 hours since the previous record
4, 2014-01-03 04:45:22, test 4 <-- fourth record was more than 24 hours since the previous record

想法？

postgresql aggregate

2 个回答

  1. Best Answer

    正如@ypercube 所说，lag()会有所帮助。

    select id,
       (d - lag) as lag, 
       case when (d - lag) < interval '24 hours' then false else true end as p
from (
      select id, 
             (datetime) as d, 
             lag(datetime) over (order by id) as lag 
      from lagtest
     ) as t;

    对于第一行，不会显示滞后。

    • 5

  2. @Fabrizio基本上是正确的，但我会简化：

    SELECT id, datetime
       lag(datetime) OVER (ORDER BY id) + interval '1d' < datetime AS more_than_a_day
FROM   tbl;

    不需要子查询甚至CASE语句。

    • 2

相关问题