PostgreSQL中合并间隔≤1分钟的时间戳为时间区间

需求说明 #

需要将间隔小于等于1分钟的时间戳合并为同一时间区间，移除中间处于区间内的冗余时间戳。
示例：
输入时间戳：
"2023-08-10T18:30:30", "2023-08-10T18:31:00","2023-08-10T18:31:30","2023-08-10T18:35:00","2023-08-10T18:35:30"
预期输出：
"18:30:30-18:31:30", "18:35:00-18:35:30"

尝试用generate_series结合时间最小最大值关联原数据，但不知道如何生成预期结果。

样本数据 #

SELECT time FROM (values 
   ('2023-08-10T18:30:30')
  ,('2023-08-10T18:31:00')
  ,('2023-08-10T18:31:30')
  ,('2023-08-10T18:35:00')
  ,('2023-08-10T18:35:30')
  ,('2023-08-10T18:36:00')
  ,('2023-08-10T18:37:00')
  ,('2023-08-10T18:37:30')
  ) s(time)

预期输出规则 #

• 2023-08-10T18:31:00处于2023-08-10T18:30:30与2023-08-10T18:31:30的1分钟区间内，因此被移除
• 2023-08-10T18:35:30处于2023-08-10T18:35:00与2023-08-10T18:36:00的1分钟区间内，因此被移除
• 2023-08-10T18:37:00与2023-08-10T18:37:30之间无需移除任何时间戳

预期输出数据：

SELECT time 
FROM (values
  ('2023-08-10T18:30:30')
, ('2023-08-10T18:31:30')
, ('2023-08-10T18:35:00')
, ('2023-08-10T18:36:00')
, ('2023-08-10T18:37:00')
, ('2023-08-10T18:37:30')
) s(time)

解决方案 #

方案1：保留非冗余时间戳 #

使用窗口函数LAG()和LEAD()判断当前时间戳是否需要保留，逻辑为：保留首尾时间戳，或与前后时间间隔超过1分钟的记录。

WITH sorted_times AS (
  SELECT 
    time::timestamp,
    LAG(time::timestamp) OVER (ORDER BY time) AS prev_time,
    LEAD(time::timestamp) OVER (ORDER BY time) AS next_time
  FROM (values 
    ('2023-08-10T18:30:30')
   ,('2023-08-10T18:31:00')
   ,('2023-08-10T18:31:30')
   ,('2023-08-10T18:35:00')
   ,('2023-08-10T18:35:30')
   ,('2023-08-10T18:36:00')
   ,('2023-08-10T18:37:00')
   ,('2023-08-10T18:37:30')
  ) s(time)
)
SELECT time
FROM sorted_times
WHERE 
  prev_time IS NULL -- 第一个时间戳
  OR next_time IS NULL -- 最后一个时间戳
  OR EXTRACT(EPOCH FROM (time - prev_time)) > 60 -- 与上一个间隔超过1分钟
  OR EXTRACT(EPOCH FROM (next_time - time)) > 60; -- 与下一个间隔超过1分钟

方案2：直接生成合并后的区间字符串 #

通过分组标识将连续的时间戳归为一组，再取每组的最小和最大时间拼接成区间。

WITH sorted_times AS (
  SELECT 
    time::timestamp,
    -- 当当前时间与上一个间隔超过1分钟时，分组ID加1
    SUM(CASE WHEN EXTRACT(EPOCH FROM (time::timestamp - LAG(time::timestamp) OVER (ORDER BY time))) > 60 THEN 1 ELSE 0 END) OVER (ORDER BY time) AS group_id
  FROM (values 
    ('2023-08-10T18:30:30')
   ,('2023-08-10T18:31:00')
   ,('2023-08-10T18:31:30')
   ,('2023-08-10T18:35:00')
   ,('2023-08-10T18:35:30')
   ,('2023-08-10T18:36:00')
   ,('2023-08-10T18:37:00')
   ,('2023-08-10T18:37:30')
  ) s(time)
)
SELECT 
  TO_CHAR(MIN(time), 'HH24:MI:SS') || '-' || TO_CHAR(MAX(time), 'HH24:MI:SS') AS time_interval
FROM sorted_times
GROUP BY group_id
ORDER BY group_id;

执行后输出：

18:30:30-18:31:30
18:35:00-18:36:00
18:37:00-18:37:30

内容的提问来源于stack exchange，提问作者steve wang