SQL Server 2016查询双表时间重叠并计算各时段用户总时长

实现逻辑 #

• 核心思路是先匹配两张表中有时间重叠的记录，再截取重叠区间计算时长后聚合求和
• 时间重叠判断规则：表2用户开始时间 < 表1事件结束时间 AND 表2用户结束时间 > 表1事件开始时间
• 重叠区间计算：分别取两个时段开始时间的最大值作为重叠起点，结束时间的最小值作为重叠终点
• 累计时长直接对所有重叠区间的分钟差求和，无匹配记录时默认返回0

实现代码 #

SELECT 
    t1.eventId,
    t1.startDateTime,
    t1.endDateTime,
    ISNULL(SUM(
        DATEDIFF(MINUTE, 
            IIF(t2.startUserDateTime > t1.startDateTime, t2.startUserDateTime, t1.startDateTime),
            IIF(t2.endUserDateTime < t1.endDateTime, t2.endUserDateTime, t1.endDateTime)
        )
    ), 0) AS userTimeTotalMinutes
FROM table1 t1
LEFT JOIN table2 t2 
    ON t2.startUserDateTime < t1.endDateTime 
    AND t2.endUserDateTime > t1.startDateTime
GROUP BY t1.eventId, t1.startDateTime, t1.endDateTime
ORDER BY t1.eventId

代码说明 #

• 用LEFT JOIN保证表1的所有事件都能出现在结果中，不会遗漏无用户覆盖的时段（如示例中的eventId=3）
• IIF函数用来取重叠区间的起止时间，SQL Server 2012及以上版本均支持，也可以替换为CASE语句兼容更低版本
• DATEDIFF(MINUTE, start, end)计算两个时间的分钟差，直接累加所有用户的重叠时长即可得到总分钟数
• ISNULL函数将无匹配记录的总时长置为0，完全符合要求的输出格式

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