如何在现代SQL中实现时间戳的时序聚类？

可以实现，这类需求属于时间序列会话化分组，核心是把间隔在设定阈值内的时间戳归为同一组，最终提取每组的起止时间。

假设你的时间戳存储在表timestamps的ts字段中，我们以「时间间隔不超过2分钟则归为同一组」为规则（匹配你给出的示例结果），用窗口函数就能完成：

WITH grouped_ts AS (
  SELECT
    ts,
    -- 当当前时间与上一条的间隔超过2分钟（120秒）时，标记为新组起点，累积生成分组ID
    SUM(CASE WHEN EXTRACT(EPOCH FROM ts - LAG(ts) OVER (ORDER BY ts)) > 120 THEN 1 ELSE 0 END) 
    OVER (ORDER BY ts) AS group_id
  FROM timestamps
)
-- 按分组聚合，提取每组的起止时间
SELECT
  MIN(ts) AS start,
  MAX(ts) AS end
FROM grouped_ts
GROUP BY group_id
ORDER BY start;

代码逻辑说明： #

• LAG(ts) OVER (ORDER BY ts)：先按时间戳排序，获取当前行的上一行时间戳。
• EXTRACT(EPOCH FROM ts - LAG(ts))：计算当前时间与上一行的时间差（单位：秒），这里2分钟对应120秒。
• SUM(...) OVER (ORDER BY ts)：累积求和，每次遇到超过阈值的时间差就加1，这样相同group_id的时间戳就属于同一聚类组。
• 最后按group_id分组，取每组的最小时间作为start、最大时间作为end，就得到你要的聚类结果。

如果需要调整聚类的时间阈值，比如改成1分钟，只需要把120换成60即可。不同SQL方言的时间差计算语法略有不同（比如MySQL用TIMESTAMPDIFF(SECOND, LAG(ts) OVER (ORDER BY ts), ts)），但核心逻辑完全一致。

内容的提问来源于stack exchange，提问作者Carl Patenaude Poulin