Postgres：计算连续值之间的时间间隔（以秒为单位）

Question

我想知道我们如何计算连续日期时间的差异（例如以天或秒为单位），考虑下面的示例表：

time                       value
2020-03-30 00:25:10       10112
2020-04-02 08:04:03       45665
2020-04-10 09:55:56       112
2020-04-10 13:12:00       858
2020-04-28 10:15:59       89965
2020-05-30 22:31:02       12

这是由 pandas' diff()：

0                NaT
1    3 days 07:38:53
2    8 days 01:51:53
3    0 days 03:16:04
4   17 days 21:03:59
5   32 days 12:15:03

换句话说，Postgres相当于pandas' diff()？

Answer 1

您可以简单地减去滞后时间戳，然后使用 to_char。例如：

SELECT "time" - LAG("time") OVER (ORDER BY "time") AS diff,
       TO_CHAR("time" - LAG("time") OVER (ORDER BY "time"), 'DD "days" HH24:MI:SS') AS cdiff
FROM data

输出：

diff                                            cdiff
null                                            null
{"days":3,"hours":7,"minutes":38,"seconds":53}  03 days 07:38:53
{"days":8,"hours":1,"minutes":51,"seconds":53}  08 days 01:51:53
{"hours":3,"minutes":16,"seconds":4}            00 days 03:16:04
{"days":17,"hours":21,"minutes":3,"seconds":59} 17 days 21:03:59
{"days":32,"hours":12,"minutes":15,"seconds":3} 32 days 12:15:03

db-fiddle 上的演示

注意 如果您还想要行号，则可以添加一个，因为

ROW_NUMBER() OVER (ORDER BY "time") - 1 AS rownum

您还可以使用 EXTRACT：

EXTRACT(EPOCH FROM "time" - LAG("time") OVER (ORDER BY "time")) AS seconds

输出

rownum  diff                                            cdiff               seconds
0       null                                            null                null
1       {"days":3,"hours":7,"minutes":38,"seconds":53}  03 days 07:38:53    286733
2       {"days":8,"hours":1,"minutes":51,"seconds":53}  08 days 01:51:53    697913
3       {"hours":3,"minutes":16,"seconds":4}            00 days 03:16:04    11764
4       {"days":17,"hours":21,"minutes":3,"seconds":59} 17 days 21:03:59    1544639
5       {"days":32,"hours":12,"minutes":15,"seconds":3} 32 days 12:15:03    2808903

db-fiddle 演示

Answer 2

SELECT row_number() OVER w - 1,
       time - lag(time) OVER w
FROM tab
WINDOW w AS (ORDER BY time)
ORDER BY time;

row_number 对结果行进行计数，而 lag 则按定义的顺序获取上一行的值。