Oracle按相邻行时间间隔小于1分钟规则分组查询起止时间

需求描述 #

现有一张带时间戳（TIMESTAMP类型）字段的Oracle数据表，表内数据已按时间升序排列，需实现如下分组逻辑：

• 逐行计算当前行时间戳与前一行时间戳的差值
• 差值小于1分钟则两行归为同一分组
• 差值大于等于1分钟则创建新分组
• 最终输出每个分组的组号、分组起始时间、分组结束时间

原始样例数据 #

期望输出结果 #

实现思路 #

用Oracle窗口函数分三步处理：

• 用LAG()函数获取按时间排序的上一行时间戳
• 逐行判断当前行与上一行的时间差，差值≥1分钟则标记为分组断点，对断点值逐行累加生成分组号
• 按生成的组号分组，聚合取每组最小时间为起始时间、最大时间为结束时间

实现代码 #

WITH base_with_prev AS (
    SELECT
        time_col, -- 替换为实际时间戳字段名
        LAG(time_col) OVER (ORDER BY time_col) AS prev_time
    FROM your_table -- 替换为实际表名
),
group_marked AS (
    SELECT
        time_col,
        SUM(
            CASE
                WHEN prev_time IS NULL OR (time_col - prev_time) >= INTERVAL '1' MINUTE
                THEN 1
                ELSE 0
            END
        ) OVER (ORDER BY time_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_num
    FROM base_with_prev
)
SELECT
    group_num AS "Group number",
    MIN(time_col) AS "start time",
    MAX(time_col) AS "end time"
FROM group_marked
GROUP BY group_num
ORDER BY group_num;

注意：代码中your_table、time_col需要替换为实际业务中的表名、时间戳字段名。

内容的提问来源于stack exchange，提问作者Omri Toister