如何用SQL将事件时间段列表转换为按日期统计时长表

假设你已经生成了包含目标日期范围的日期表（记为date_dim，核心字段为date，格式为YYYY-MM-DD），可以按以下步骤处理：

1. 关联事件表与日期表 #

将事件表（记为incidents）和日期表做关联，筛选出所有事件与日期存在时间重叠的记录。关联条件为：

SELECT 
  d.date,
  i.Incident_Start,
  i.Incident_End
FROM date_dim d
JOIN incidents i 
  ON d.date >= DATE(i.Incident_Start) 
  AND d.date <= DATE(i.Incident_End)

这一步会把每个跨天事件拆分成多条记录，对应其覆盖的每一天。

2. 计算单条事件在对应日期的活跃分钟数 #

通过CASE WHEN分支处理四种时间重叠场景，计算当日的有效活跃时长：

CASE
  -- 场景1：事件完全在当天内
  WHEN DATE(i.Incident_Start) = d.date AND DATE(i.Incident_End) = d.date THEN
    TIMESTAMPDIFF(MINUTE, i.Incident_Start, i.Incident_End)
  -- 场景2：事件从当天开始，延续到后续日期
  WHEN DATE(i.Incident_Start) = d.date AND DATE(i.Incident_End) > d.date THEN
    TIMESTAMPDIFF(MINUTE, i.Incident_Start, CONCAT(d.date, ' 23:59:59'))
  -- 场景3：事件在当天结束，起始于之前的日期
  WHEN DATE(i.Incident_End) = d.date AND DATE(i.Incident_Start) < d.date THEN
    TIMESTAMPDIFF(MINUTE, CONCAT(d.date, ' 00:00:00'), i.Incident_End)
  -- 场景4：事件完全覆盖当天（跨天事件的中间日期）
  ELSE 1440 -- 24*60，全天分钟数
END AS daily_active_minutes

注：不同SQL方言的时间函数略有差异，比如PostgreSQL可使用EXTRACT(EPOCH FROM (结束时间 - 开始时间)) / 60计算分钟数，SQL Server用DATEDIFF(MINUTE, 开始时间, 结束时间)，核心逻辑一致。

3. 按日期汇总总活跃时长 #

将第二步的计算结果按日期分组求和，得到最终的每日总活跃分钟数：

SELECT
  d.date,
  SUM(
    CASE
      WHEN DATE(i.Incident_Start) = d.date AND DATE(i.Incident_End) = d.date THEN
        TIMESTAMPDIFF(MINUTE, i.Incident_Start, i.Incident_End)
      WHEN DATE(i.Incident_Start) = d.date AND DATE(i.Incident_End) > d.date THEN
        TIMESTAMPDIFF(MINUTE, i.Incident_Start, CONCAT(d.date, ' 23:59:59'))
      WHEN DATE(i.Incident_End) = d.date AND DATE(i.Incident_Start) < d.date THEN
        TIMESTAMPDIFF(MINUTE, CONCAT(d.date, ' 00:00:00'), i.Incident_End)
      ELSE 1440
    END
  ) AS total_daily_minutes
FROM date_dim d
JOIN incidents i 
  ON d.date >= DATE(i.Incident_Start) 
  AND d.date <= DATE(i.Incident_End)
GROUP BY d.date
ORDER BY d.date

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