Looker(GBQ SQL)中DATEDIFF如何排除多个节假日计算正确日期差

你当前代码的多层CASE WHEN属于互斥匹配逻辑，命中第一个符合条件的节假日后就会直接返回对应分支结果，不再校验后续节假日，因此区间包含2个及以上节假日时仅会扣减1天，不符合按实际数量扣减的需求。

放弃分支判断的写法，直接统计两个日期区间内实际包含的节假日总数量，从计算出的基础工作日间隔中扣除对应数值即可，自动适配任意数量的节假日扣减场景。

调整后代码 #

CASE
  WHEN ${DATA_1.COUNTRY} = 'GB'
  THEN
    -- 保留原代码的基础间隔计算逻辑：扣除周末后的基础工作日天数
    DATE_DIFF(${TIMESTAMPS.first_attempted_date}, ${TIMESTAMPS.created_date}, DAY) 
    - DATE_DIFF(${TIMESTAMPS.first_attempted_date}, ${TIMESTAMPS.created_date}, WEEK(SUNDAY))
    -- 按区间内实际节假日数量扣减
    - (
      SELECT COUNT(1)
      FROM UNNEST([
        DATE('2022-05-02'),
        DATE('2022-05-03'),
        DATE('2022-05-04')
        -- 后续新增GB地区节假日直接在该数组内追加日期即可
      ]) AS holiday
      WHERE holiday BETWEEN ${TIMESTAMPS.created_date} AND ${TIMESTAMPS.first_attempted_date}
    )
  ELSE
    DATE_DIFF(${TIMESTAMPS.first_attempted_date}, ${TIMESTAMPS.created_date}, DAY) 
    - DATE_DIFF(${TIMESTAMPS.first_attempted_date}, ${TIMESTAMPS.created_date}, WEEK(SUNDAY))
END

• 基础日期间隔计算完全保留原代码逻辑：先计算两个日期的自然日差值，再减去区间内包含的周日总数（WEEK(SUNDAY)的差值即为区间内周日的个数），得到排除周日的基础天数
• 节假日扣减部分通过BigQuery原生的UNNEST函数，将配置的所有节假日拆分为单行日期表，统计落在当前起止日期区间内的节假日个数后直接扣减，无论区间内包含几个节假日，都会按实际数量完成扣减
• 后续维护节假日配置时，只需要在UNNEST对应的日期数组中追加对应国家/地区的节假日日期即可，无需重复编写冗余的CASE WHEN判断分支

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