在BigQuery中实现每月第一个周三与最后一个周四的动态判断

可以通过日期函数结合取模运算，简洁地计算出每月的目标日期，以下是具体实现方案：

1. 计算每月第一个周三 #

利用DATE_TRUNC获取当月首日，结合MOD函数计算需要偏移的天数，得到第一个周三：

DATE_ADD(
  DATE_TRUNC(CURRENT_DATE(), MONTH),
  INTERVAL MOD(4 - EXTRACT(DAYOFWEEK FROM DATE_TRUNC(CURRENT_DATE(), MONTH)) + 7, 7) DAY
) AS first_wednesday_of_month

逻辑说明：

• BigQuery中DAYOFWEEK返回值为1（周日）到7（周六），周三对应数值4
• MOD(4 - 首日星期值 +7,7)计算出从首日到第一个周三需要的偏移天数，确保无论首日是星期几都能得到正确结果

2. 计算每月最后一个周四 #

先获取当月最后一天，再用MOD函数计算需要回退的天数，得到最后一个周四：

DATE_SUB(
  DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY),
  INTERVAL MOD(EXTRACT(DAYOFWEEK FROM DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)) -5 +7,7) DAY
) AS last_thursday_of_month

逻辑说明：

• DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)用于获取当月最后一天
• 周四对应DAYOFWEEK数值5，MOD(最后一天星期值 -5 +7,7)计算出从最后一天回退到最近的周四需要的天数

3. 整合到你的判断逻辑中 #

将上述计算直接嵌入到CASE语句里，同时修正了运算符错误（原代码中=>应为>=），最终SQL如下：

SELECT
  CASE
    WHEN latestRefreshTime >= DATE_ADD(
                                DATE_TRUNC(CURRENT_DATE(), MONTH),
                                INTERVAL MOD(4 - EXTRACT(DAYOFWEEK FROM DATE_TRUNC(CURRENT_DATE(), MONTH)) +7,7) DAY
                              ) THEN 'Up to Date'
    WHEN latestRefreshTime2 >= DATE_SUB(
                                DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY),
                                INTERVAL MOD(EXTRACT(DAYOFWEEK FROM DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)) -5 +7,7) DAY
                              ) THEN 'Up to Date'
    ELSE 'Not Up To Date'
  END AS upToDate

如果需要复用这些日期计算，可以先用WITH子句提前定义，让SQL更清晰：

WITH monthly_target_dates AS (
  SELECT
    DATE_ADD(
      DATE_TRUNC(CURRENT_DATE(), MONTH),
      INTERVAL MOD(4 - EXTRACT(DAYOFWEEK FROM DATE_TRUNC(CURRENT_DATE(), MONTH)) +7,7) DAY
    ) AS first_wednesday,
    DATE_SUB(
      DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY),
      INTERVAL MOD(EXTRACT(DAYOFWEEK FROM DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)) -5 +7,7) DAY
    ) AS last_thursday
)
SELECT
  CASE
    WHEN latestRefreshTime >= (SELECT first_wednesday FROM monthly_target_dates) THEN 'Up to Date'
    WHEN latestRefreshTime2 >= (SELECT last_thursday FROM monthly_target_dates) THEN 'Up to Date'
    ELSE 'Not Up To Date'
  END AS upToDate
FROM your_table_name

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