SQL实现分组统计时扣除重叠区间的人员总耗时计算

前置说明 #

默认业务表名为task_records，核心字段如下：

• user_id：人员ID
• task_type：任务类型
• start_time：任务开始时间，timestamp 类型
• end_time：任务结束时间，timestamp 类型

实现思路 #

• 按user_id、task_type分组后，将所有任务区间按开始时间排序
• 标记每个区间是否为新合并块的起点：当前区间开始时间大于上一个区间的结束时间时，判定为新块起点
• 累加标记生成合并块唯一ID，同一个合并块内的所有区间存在重叠/包含关系
• 先聚合每个合并块的有效时长，再按分组维度汇总得到去重叠后的总耗时

通用SQL实现（支持所有带窗口函数的SQL引擎：MySQL 8.0+、PostgreSQL、Spark SQL、Hive等） #

WITH ranked_tasks AS (
    SELECT
        user_id,
        task_type,
        start_time,
        end_time,
        -- 取同分组内上一个任务的结束时间
        LAG(end_time) OVER (
            PARTITION BY user_id, task_type 
            ORDER BY start_time 
        ) AS prev_end
    FROM task_records
    -- 过滤异常数据：开始时间晚于结束时间的无效记录
    WHERE start_time <= end_time
),
block_flags AS (
    SELECT
        user_id,
        task_type,
        start_time,
        end_time,
        -- 标记是否为新合并块的起点
        CASE WHEN start_time > COALESCE(prev_end, '1970-01-01'::timestamp) THEN 1 ELSE 0 END AS is_new_block
    FROM ranked_tasks
),
merged_blocks AS (
    SELECT
        user_id,
        task_type,
        start_time,
        end_time,
        -- 累加生成合并块ID，同一个合并块ID相同
        SUM(is_new_block) OVER (
            PARTITION BY user_id, task_type 
            ORDER BY start_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS block_id
    FROM block_flags
),
block_durations AS (
    SELECT
        user_id,
        task_type,
        block_id,
        MIN(start_time) AS block_start,
        MAX(end_time) AS block_end,
        -- 计算单个合并块的有效时长，可根据所用引擎调整时间差计算逻辑
        MAX(end_time) - MIN(start_time) AS block_duration
    FROM merged_blocks
    GROUP BY user_id, task_type, block_id
)
-- 最终汇总得到去重叠后的总有效耗时
SELECT
    user_id,
    task_type,
    SUM(block_duration) AS total_effective_duration
FROM block_durations
GROUP BY user_id, task_type
ORDER BY user_id, task_type;

不同引擎时间差计算适配 #

如果需要统计指定单位（秒/分钟/小时），可以替换block_duration的计算逻辑：

• MySQL：TIMESTAMPDIFF(SECOND, MIN(start_time), MAX(end_time)) 返回秒数
• PostgreSQL：EXTRACT(EPOCH FROM (MAX(end_time) - MIN(start_time))) 返回秒数
• Hive/Spark SQL：unix_timestamp(MAX(end_time)) - unix_timestamp(MIN(start_time)) 返回秒数

低版本引擎适配（无窗口函数支持） #

如果使用不支持窗口函数的低版本MySQL 5.x等，可采用自连接方案实现，性能较差仅适合小数据量场景：

SELECT
    user_id,
    task_type,
    SUM(TIMESTAMPDIFF(SECOND, block_start, block_end)) AS total_seconds
FROM (
    SELECT
        t1.user_id,
        t1.task_type,
        MIN(t1.start_time) AS block_start,
        MAX(t2.end_time) AS block_end
    FROM task_records t1
    JOIN task_records t2 
        ON t1.user_id = t2.user_id 
        AND t1.task_type = t2.task_type
        AND t1.start_time <= t2.end_time
        AND t2.start_time <= t1.end_time
    GROUP BY t1.user_id, t1.task_type, t1.start_time
) t
GROUP BY user_id, task_type;

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