Vertica中如何按4小时间隔分组事件记录？

问题 #

需要在Vertica数据库中按4小时间隔分组事件记录，现有包含event_id、start_date、end_date的事件时长数据，要求每组的最长时长不超过4小时。

示例原数据 #

期望分组结果 #

已尝试用窗口函数计算累计时长，但不知道如何关联到分组：

select sum(end_date - start_date) over (partition by event_id order by start_date)

解决方案 #

可以通过判断事件与分组起始时间的间隔来划分分组，结合窗口函数和分组聚合实现需求。以下是适配Vertica的SQL实现：

WITH event_with_group AS (
    SELECT 
        event_id,
        start_date,
        end_date,
        -- 计算当前事件与分组起始时间的间隔，超过4小时则触发新分组
        SUM(
            CASE 
                WHEN start_date >= FIRST_VALUE(start_date) OVER (PARTITION BY event_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + INTERVAL '4 hours'
                THEN 1
                ELSE 0
            END
        ) OVER (PARTITION BY event_id ORDER BY start_date) AS group_id
    FROM your_table_name
),
adjusted_groups AS (
    SELECT 
        event_id,
        start_date,
        end_date,
        -- 把分组ID归一化，确保每个event_id的分组从0开始连续
        group_id - MIN(group_id) OVER (PARTITION BY event_id) AS adjusted_group_id
    FROM event_with_group
)
-- 按分组聚合，得到合并后的时间段
SELECT 
    event_id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM adjusted_groups
GROUP BY event_id, adjusted_group_id
ORDER BY event_id, start_date;

逻辑说明 #

1. 标记分组ID：用FIRST_VALUE()获取当前分组的起始时间，判断当前事件的起始时间是否和该起始时间间隔超过4小时，是的话就给一个标记，通过SUM()窗口函数累计这些标记，得到每个事件的临时分组ID
2. 归一化分组ID：把每个event_id的分组ID调整为从0开始的连续编号，避免分组ID跳号
3. 聚合结果：按event_id和调整后的分组ID分组，取每组最早的start_date和最晚的end_date，就是合并后的分组结果

如果想要更简洁的写法，可以直接用LAG()窗口函数跟踪上一个分组的起始时间：

WITH event_groups AS (
    SELECT 
        event_id,
        start_date,
        end_date,
        -- 当当前事件起始时间和上一个分组起始时间间隔≥4小时，分组ID+1
        SUM(
            CASE 
                WHEN start_date >= LAG(MIN(start_date) OVER (PARTITION BY event_id ORDER BY start_date), 1, start_date) OVER (PARTITION BY event_id ORDER BY start_date) + INTERVAL '4 hours'
                THEN 1
                ELSE 0
            END
        ) OVER (PARTITION BY event_id ORDER BY start_date) AS group_id
    FROM your_table_name
)
SELECT 
    event_id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM event_groups
GROUP BY event_id, group_id
ORDER BY event_id, start_date;

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