如何获取PostgresSQL中下一行之间间隔大于xx秒的时间戳计数

Question

>我有 3 列的表(postgres 9.6(：串行、时间戳、clock_name 通常每行之间有 1 秒的差异，但有时间隔更大。

我正在尝试获取 2 行之间的时间戳间隔大于 10 秒的次数(假设我将其限制为 1000 行(

我想在一个查询中执行此操作(可能从选择中选择(，但我不知道如何编写这样的查询，我的sql知识非常基本。

任何帮助将不胜感激

Answer 1

您可以使用窗口函数在给定当前记录的情况下检索下一条记录。

在函数上使用 ORDER BY 来确保事情按时间戳顺序排列，并使用 PARTITION 保持时钟分开，您可以为每一行找到它后面的行。

WITH links AS
(
SELECT
id, ts, clock, LEAD(ts) OVER (PARTITION BY clock ORDER BY ts) AS next_ts
FROM myTable
)
SELECT * FROM links
WHERE
EXTRACT(EPOCH FROM (next_ts - ts)) > 10

然后，您可以只比较时间戳。

窗口函数 https://www.postgresql.org/docs/current/static/functions-window.html

或者，如果您更喜欢使用派生表而不是 WITH 子句。

SELECT * FROM (
SELECT
id, ts, clock, LEAD(ts) OVER (PARTITION BY clock ORDER BY ts) AS next_ts
FROM myTable
) links
WHERE
EXTRACT(EPOCH FROM (next_ts - ts)) > 10