Oracle中基于时间间隔参数合并多行数据的实现方案

原表数据 #

需求说明 #

同一C_ID下，若某行的T_STOP与下一行的T_START间隔不超过2小时，则合并这些行：

• O_ID以逗号分隔聚合
• T_START取组内最早时间
• T_STOP取组内最晚时间

期望结果 #

解决方案 #

无需递归查询，用窗口函数即可高效实现，核心是给连续符合条件的记录分配同一组ID，再按组聚合：

WITH ranked_data AS (
    SELECT 
        t.*,
        -- 判断当前行与上一行的时间间隔是否≤2小时，生成分组标记
        CASE 
            WHEN LAG(T_STOP) OVER (PARTITION BY C_ID ORDER BY T_START) >= T_START - INTERVAL '2' HOUR
            THEN 0
            ELSE 1
        END AS group_flag
    FROM your_table t
),
grouped_data AS (
    SELECT 
        *,
        -- 累加分组标记，生成唯一组ID
        SUM(group_flag) OVER (PARTITION BY C_ID ORDER BY T_START) AS group_id
    FROM ranked_data
)
SELECT 
    C_ID,
    LISTAGG(O_ID, ', ') WITHIN GROUP (ORDER BY O_ID) AS O_ID,
    MIN(T_START) AS T_START,
    MAX(T_STOP) AS T_STOP
FROM grouped_data
GROUP BY C_ID, group_id
ORDER BY C_ID, group_id;

步骤解释 #

1. ranked_data CTE：通过LAG函数获取同一C_ID下前一行的T_STOP，对比当前行T_START，判断间隔是否符合要求，生成group_flag（符合为0，不符合为1）。
2. grouped_data CTE：对group_flag做累加，同一连续组的group_id保持一致，不同组的group_id递增，以此区分独立分组。
3. 最终聚合：按C_ID和group_id分组，用LISTAGG拼接O_ID，取组内最早的T_START和最晚的T_STOP。

注意事项 #

• 确保T_START和T_STOP为DATE或TIMESTAMP类型，若为字符串需先转换：TO_DATE(T_START, 'MM/DD/RR HH24:MI')
• LISTAGG支持Oracle 11g及以上版本，低版本可改用WM_CONCAT（返回CLOB类型，需转换为VARCHAR2时可使用TO_CHAR(WM_CONCAT(O_ID))）

内容的提问来源于stack exchange，提问作者Brennan Cushin