BigQuery SQL实现：计算任务起止时间差并排除非工作时段

以下是针对需求编写的BigQuery SQL语句，可自动排除非工作时段，计算任务的真实有效SLA时长：

WITH adjusted_times AS (
  SELECT
    TaskID,
    -- 调整任务开始时间：非工作时段自动移至最近的工作时段起始点
    CASE
      WHEN EXTRACT(HOUR FROM TaskStartedDateTime) < 8 THEN TIMESTAMP(DATE(TaskStartedDateTime) + INTERVAL '8' HOUR)
      WHEN EXTRACT(HOUR FROM TaskStartedDateTime) >= 14 THEN TIMESTAMP(DATE(TaskStartedDateTime) + INTERVAL '1' DAY + INTERVAL '8' HOUR)
      ELSE TaskStartedDateTime
    END AS adjusted_start,
    -- 调整任务结束时间：非工作时段自动移至最近的工作时段结束点
    CASE
      WHEN EXTRACT(HOUR FROM TaskCompletedDateTime) < 8 THEN TIMESTAMP(DATE(TaskCompletedDateTime) + INTERVAL '8' HOUR)
      WHEN EXTRACT(HOUR FROM TaskCompletedDateTime) >= 14 THEN TIMESTAMP(DATE(TaskCompletedDateTime) + INTERVAL '14' HOUR)
      ELSE TaskCompletedDateTime
    END AS adjusted_end,
    TaskStartedDateTime,
    TaskCompletedDateTime
  FROM Tasks
),
full_days_calculation AS (
  SELECT
    TaskID,
    adjusted_start,
    adjusted_end,
    TaskStartedDateTime,
    TaskCompletedDateTime,
    -- 统计开始/结束日期之间的完整工作日数量（不含当天）
    DATE_DIFF(DATE(adjusted_end), DATE(adjusted_start), DAY) - 1 AS full_work_days
  FROM adjusted_times
  WHERE DATE(adjusted_end) > DATE(adjusted_start)
  UNION ALL
  SELECT
    TaskID,
    adjusted_start,
    adjusted_end,
    TaskStartedDateTime,
    TaskCompletedDateTime,
    0 AS full_work_days
  FROM adjusted_times
  WHERE DATE(adjusted_end) <= DATE(adjusted_start)
)
SELECT
  TaskID,
  TaskStartedDateTime,
  TaskCompletedDateTime,
  -- 计算总有效时长：完整工作日时长 + 开始当日剩余时长 + 结束当日有效时长
  TIMESTAMP_ADD(
    TIMESTAMP_ADD(
      INTERVAL full_work_days * 6 HOUR,
      TIMESTAMP_DIFF(TIMESTAMP(DATE(adjusted_start) + INTERVAL '14' HOUR), adjusted_start, SECOND) * INTERVAL 1 SECOND
    ),
    TIMESTAMP_DIFF(adjusted_end, TIMESTAMP(DATE(adjusted_end) + INTERVAL '8' HOUR), SECOND) * INTERVAL 1 SECOND
  ) AS actual_sla_duration,
  -- 可选：转换为可读的时分秒格式
  FORMAT_TIMESTAMP('%H:%M:%S', TIMESTAMP_ADD(TIMESTAMP('1970-01-01'), 
    TIMESTAMP_ADD(
      INTERVAL full_work_days * 6 HOUR,
      TIMESTAMP_DIFF(TIMESTAMP(DATE(adjusted_start) + INTERVAL '14' HOUR), adjusted_start, SECOND) * INTERVAL 1 SECOND
    ) + 
    TIMESTAMP_DIFF(adjusted_end, TIMESTAMP(DATE(adjusted_end) + INTERVAL '8' HOUR), SECOND) * INTERVAL 1 SECOND
  )) AS actual_sla_duration_readable
FROM full_days_calculation
ORDER BY TaskID;

关键逻辑说明 #

1. 时间窗口修正：第一个CTE adjusted_times 自动修正非工作时段的起止时间：
   • 开始时间早于08:00 → 修正为当日08:00
   • 开始时间晚于14:00 → 修正为次日08:00
   • 结束时间早于08:00 → 修正为当日08:00
   • 结束时间晚于14:00 → 修正为当日14:00
2. 完整工作日统计：第二个CTE full_days_calculation 计算起止日期之间的完整工作日数，每个工作日贡献6小时有效时长。
3. 总时长计算：
   • 开始当日有效时长：从修正后的开始时间到当日14:00的时长
   • 结束当日有效时长：从当日08:00到修正后的结束时间的时长
   • 三者相加得到最终真实SLA时长

示例验证 #

• 示例1：任务2023-05-27 18:05:23开始，2023-05-28 12:05:23结束
  • 修正后开始时间为2023-05-28 08:00:00，结束时间不变
  • 总有效时长为4小时5分23秒，符合预期
• 示例2：任务2023-03-22 07:45:01开始，2023-03-22 09:05:16结束
  • 修正后开始时间为2023-03-22 08:00:00，结束时间不变
  • 总有效时长为1小时5分16秒，符合预期
• 示例3：任务2023-01-18 07:45:01开始，2023-01-20 09:00:07结束
  • 修正后开始时间为2023-01-18 08:00:00，结束时间不变
  • 完整工作日1天（1月19日）贡献6小时，1月18日贡献6小时，1月20日贡献1小时0分7秒，总时长13小时0分7秒，符合预期

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