在BigQuery中计算两个日期间的有效工作时长（排除周末并限定工作时段）

Hey there! 看了你的需求和现有脚本，你用GENERATE_DATE_ARRAY的思路方向是对的，但确实还需要处理首尾两天的非完整工作时段，另外你原来的周末判断条件有点小问题——BigQuery里DAYOFWEEK的规则是周日=1、周六=7，所以要排除周末的话，应该过滤掉这两个值，而不是用BETWEEN 6 AND 2哦。

下面给你一个完整的解决方案，既能排除周末，又能精准计算每天的有效工作时长（限定8:00-17:00，可灵活调整），最后输出十进制的小时数：

WITH test_table AS (
  SELECT
    CAST('2024-05-02 10:00:00' AS DATETIME) AS start_date,
    CAST('2024-05-06 11:00:00' AS DATETIME) AS end_date
),
-- 定义工作时段参数，方便后续调整
work_hours AS (
  SELECT
    TIME('08:00:00') AS work_start,
    TIME('17:00:00') AS work_end,
    -- 每天有效工作时长（秒），8:00到17:00是9小时=32400秒，若实际是8小时可改成28800
    DATETIME_DIFF(DATETIME(DATE('2024-01-01'), TIME('17:00:00')), DATETIME(DATE('2024-01-01'), TIME('08:00:00')), SECOND) AS daily_work_seconds
),
-- 生成所有介于起始和结束日期之间的日期（包含首尾），并判断是否为工作日
date_range AS (
  SELECT
    dt,
    CASE WHEN EXTRACT(DAYOFWEEK FROM dt) NOT IN (1, 7) THEN TRUE ELSE FALSE END AS is_workday
  FROM test_table,
       UNNEST(GENERATE_DATE_ARRAY(DATE(start_date), DATE(end_date))) dt
),
-- 计算每个日期的有效工作秒数
daily_seconds AS (
  SELECT
    dr.dt,
    dr.is_workday,
    tt.start_date,
    tt.end_date,
    wh.work_start,
    wh.work_end,
    wh.daily_work_seconds,
    CASE
      -- 非工作日有效时长为0
      WHEN NOT dr.is_workday THEN 0
      -- 首尾日期是同一天的情况，取两个时间的交集
      WHEN dr.dt = DATE(tt.start_date) AND dr.dt = DATE(tt.end_date) THEN
        DATETIME_DIFF(
          LEAST(tt.end_date, DATETIME(dr.dt, wh.work_end)),
          GREATEST(tt.start_date, DATETIME(dr.dt, wh.work_start)),
          SECOND
        )
      -- 起始日期：从实际开始时间到当天下班时间的差，不早于上班时间
      WHEN dr.dt = DATE(tt.start_date) THEN
        DATETIME_DIFF(
          DATETIME(dr.dt, wh.work_end),
          GREATEST(tt.start_date, DATETIME(dr.dt, wh.work_start)),
          SECOND
        )
      -- 结束日期：从当天上班时间到实际结束时间的差，不晚于下班时间
      WHEN dr.dt = DATE(tt.end_date) THEN
        DATETIME_DIFF(
          LEAST(tt.end_date, DATETIME(dr.dt, wh.work_end)),
          DATETIME(dr.dt, wh.work_start),
          SECOND
        )
      -- 中间工作日，取完整的每日有效时长
      ELSE wh.daily_work_seconds
    END AS effective_seconds
  FROM date_range dr
  CROSS JOIN test_table tt
  CROSS JOIN work_hours wh
)
-- 汇总总有效时长，转换为十进制小时数
SELECT
  start_date,
  end_date,
  SUM(effective_seconds) AS total_work_seconds,
  ROUND(SUM(effective_seconds) / 3600, 2) AS total_work_hours_decimal
FROM daily_seconds
GROUP BY start_date, end_date;

关键逻辑说明： #

• 参数化工作时段：work_hours CTE把上班时间、下班时间和每日有效时长做成可配置参数，后续要调整工作时间（比如改成8:00-16:00），直接改这里就行，不用修改核心计算逻辑。
• 精准的工作日判断：修正了周末过滤逻辑，确保只保留周一到周五的日期。
• 分场景计算时长：针对首尾非完整工作日、中间完整工作日、同一天的特殊情况分别处理，保证每一段的时长计算都符合工作时段规则。
• 十进制输出：最后把总秒数转换成小时数并保留两位小数，完全符合你要的十进制格式需求。

备注：内容来源于stack exchange，提问作者Gray Meiring