BigQuery按1小时间隔拆分班次时长查询异常排查

现有SQL没有对每个小时切片的时长做重新计算，直接读取了原始表中存储的整班次总时长duration_in_hours，因此所有匹配到的整点区间都会显示整班次的3小时。
之前在其他数据表能得到正确结果，是因为那些表的duration_in_hours本身就是按小时粒度预拆分存储的，不需要二次计算；当前表存储的是班次维度的总时长，直接读取必然返回错误值。
另外原SQL存在两处语法瑕疵：

• 字段名误写为带空格的duration in hours，和表中实际的下划线命名字段duration_in_hours不匹配
• SELECT子句末尾shift_end_at后多了一个冗余逗号，部分SQL引擎会直接报语法错误

每个整点区间的实际落入时长按三类场景分别计算即可：

• 班次起始所在小时：用「当前整点+1小时的区间结束时间」减去「班次开始时间」，结果换算为小时单位
• 班次结束所在小时：用「班次结束时间」减去「当前整点的区间开始时间」，结果换算为小时单位
• 完全被班次覆盖的中间小时：固定返回1小时

SELECT
  date,
  TIME(hour, 0, 0) AS time,
  CASE
    -- 计算班次起始小时的实际时长
    WHEN TIME(hour, 0, 0) = TIME_TRUNC(shift_start_at, HOUR)
      THEN TIMESTAMP_DIFF(
        TIMESTAMP_ADD(TIMESTAMP(date, TIME(hour, 0, 0)), INTERVAL 1 HOUR),
        TIMESTAMP(date, shift_start_at),
        SECOND
      ) / 3600
    -- 计算班次结束小时的实际时长
    WHEN TIME(hour, 0, 0) = TIME_TRUNC(shift_end_at, HOUR)
      THEN TIMESTAMP_DIFF(
        TIMESTAMP(date, shift_end_at),
        TIMESTAMP(date, TIME(hour, 0, 0)),
        SECOND
      ) / 3600
    -- 中间完整覆盖的小时固定为1小时
    ELSE 1
  END AS duration_in_hours,
  shift_start_at,
  shift_end_at
FROM a, UNNEST(GENERATE_ARRAY(0, 23)) AS hour
WHERE
  TIME(hour, 0, 0) >= TIME_TRUNC(shift_start_at, HOUR)
  AND TIME(hour, 0, 0) < shift_end_at

针对你给出的2022-05-24班次（19:30:00-22:30:00），运行上述SQL会返回如下结果，完全符合预期：

• 19:00:00区间：0.5小时
• 20:00:00区间：1小时
• 21:00:00区间：1小时
• 22:00:00区间：0.5小时

注：如果存在跨天班次，需要调整时间拼接逻辑，从你给出的样例来看所有班次均为单日区间，当前逻辑可直接使用。

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