BigQuery中结合节假日表查询日期对应的前后工作日

前置规则 #

• 基准日期范围：2020-01-01 至查询执行当日的所有自然日
• 工作日判定标准：排除周六、周日，排除us_holidays_list表中登记的美国法定节假日
• 输出要求：序列中每一个日期（含周末、节假日）都要返回对应的最近上一个工作日、最近下一个工作日

可直接运行的查询语句 #

WITH
-- 生成连续日期序列
date_series AS (
  SELECT boms AS calendar_date
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 DAY)) boms
),
-- 标记工作日并为工作日生成连续排序号
workday_with_seq AS (
  SELECT
    ds.calendar_date,
    CASE
      WHEN EXTRACT(DAYOFWEEK FROM ds.calendar_date) IN (1,7) THEN FALSE
      WHEN hl.date_id IS NOT NULL THEN FALSE
      ELSE TRUE
    END AS is_workday,
    CASE
      WHEN
        EXTRACT(DAYOFWEEK FROM ds.calendar_date) NOT IN (1,7)
        AND hl.date_id IS NULL
      THEN ROW_NUMBER() OVER (
        PARTITION BY CASE
          WHEN EXTRACT(DAYOFWEEK FROM ds.calendar_date) NOT IN (1,7) AND hl.date_id IS NULL THEN 1
          ELSE 0
        END
        ORDER BY ds.calendar_date
      )
    END AS workday_seq
  FROM date_series ds
  LEFT JOIN us_holidays_list hl
    ON ds.calendar_date = hl.date_id
),
-- 为非工作日填充前后最近工作日的序号
seq_filled AS (
  SELECT
    calendar_date,
    is_workday,
    LAST_VALUE(workday_seq IGNORE NULLS) OVER (ORDER BY calendar_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_seq,
    FIRST_VALUE(workday_seq IGNORE NULLS) OVER (ORDER BY calendar_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_seq
  FROM workday_with_seq
)
-- 关联得到具体的工作日日期
SELECT
  sf.calendar_date,
  sf.is_workday,
  prev.calendar_date AS previous_workday,
  next.calendar_date AS next_workday
FROM seq_filled sf
LEFT JOIN workday_with_seq prev
  ON sf.prev_seq = prev.workday_seq
LEFT JOIN workday_with_seq next
  ON sf.next_seq = next.workday_seq
ORDER BY sf.calendar_date

逻辑说明 #

• 日期序列生成直接复用给定的GENERATE_DATE_ARRAY写法，保证日期无断层
• 关联节假日表判定工作日时，BigQuery中EXTRACT(DAYOFWEEK FROM 日期)返回1代表周日、返回7代表周六，直接排除这两个值对应的日期即可筛掉周末
• 给所有工作日按时间顺序生成连续数字序号，再通过窗口函数的IGNORE NULLS能力，给所有非工作日填充离它最近的前序、后序工作日序号，最后通过序号关联拿到具体日期，避免逐行递归的性能损耗
• 后续如果需要调整日期范围，直接修改GENERATE_DATE_ARRAY的起止参数即可；新增节假日只需要往us_holidays_list插入对应记录，查询会自动适配，不需要修改SQL逻辑

边界提示：2020-01-01如果本身是节假日，它的上一个工作日会返回空值，属于正常情况，如果需要覆盖更早的日期，把日期序列的起始时间往前调整即可。

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