如何在QuestDB中识别数据中超过5秒的时间间隙？

我正在使用QuestDB存储时间序列数据，现有一份按timestamp排序的数据集。希望计算连续记录间的时间差（delta）以识别时间间隙，例如判断上一条价格tick记录后是否出现了5秒及以上的延迟。

数据示例结构：

在该示例中，12:00:02到12:00:07之间的间隙超过5秒阈值，希望标记出此类间隙。请问QuestDB中有推荐的实现方法或特定函数吗？

QuestDB支持标准SQL窗口函数，LAG() 是实现该需求的核心工具，它可以获取当前行的前序指定行数据，结合时间差计算即可快速识别时间间隙。

基础实现SQL #

SELECT
  timestamp,
  price,
  symbol,
  -- 获取同品种上一条记录的时间戳
  LAG(timestamp) OVER (PARTITION BY symbol ORDER BY timestamp) AS prev_timestamp,
  -- 计算当前记录与上一条的时间差（单位：秒）
  EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY symbol ORDER BY timestamp))) AS delta_seconds,
  -- 标记是否存在超过5秒的间隙
  CASE
    WHEN timestamp - LAG(timestamp) OVER (PARTITION BY symbol ORDER BY timestamp) >= INTERVAL '5s' THEN 'Y'
    ELSE 'N'
  END AS is_gap_over_5s
FROM your_table_name;

关键细节说明 #

1. PARTITION BY symbol：确保仅计算同一交易品种内部的连续记录时间差，避免跨品种的无效对比。
2. ORDER BY timestamp：显式指定按时间戳排序，保证LAG()获取的是时间维度上的前一条记录（即使数据集已排序，显式声明也能避免潜在逻辑错误）。
3. 时间差计算方式：QuestDB中时间戳直接相减返回INTERVAL类型，既可以用EXTRACT(EPOCH FROM ...)转换为秒数，也可以直接用INTERVAL '5s'做阈值对比，后者更直观。

示例数据输出结果 #

执行上述SQL后，示例数据的输出如下：

筛选间隙记录的优化写法 #

如果只需要提取存在超时间隙的记录，可以用CTE简化逻辑：

WITH gap_calculations AS (
  SELECT
    timestamp,
    price,
    symbol,
    LAG(timestamp) OVER (PARTITION BY symbol ORDER BY timestamp) AS prev_timestamp,
    timestamp - LAG(timestamp) OVER (PARTITION BY symbol ORDER BY timestamp) AS gap_interval
  FROM your_table_name
)
SELECT *
FROM gap_calculations
WHERE gap_interval >= INTERVAL '5s';

内容的提问来源于stack exchange，提问作者Nick The Greek