SQL WHERE子句中使用CASE按星期动态筛选日期范围报错排查

需求 #

编写WHERE筛选语句，根据数据拉取当日对应的星期几动态过滤数据：拉取日为周三时仅返回当周周一至周三的数据，拉取日为周五时返回当周周一至周五的数据，其余日期按该逻辑类推。

原有代码问题 #

你之前写的SQL存在两个核心问题：

• CASE表达式仅能返回单个值，在CASE分支内部用and连接两个日期的写法不符合SQL语法，BETWEEN需要的起止范围值无法通过单个CASE返回
• 逐一枚举7个星期几的分支逻辑过于冗余，维护成本高

正确实现代码 #

select 
    uuid,
    acquisition_campaign,
    REFERRAL_PROMO,
    channel,
    partner,
    created_at::date as created_date
from CONSUMER_TMP
where created_at::date between 
    -- 动态计算拉取日所在周的周一日期
    DATEADD(
        'day',
        - CASE 
            -- 适配DAYOFWEEK返回0为周日的默认规则，周日需要往前推6天拿到周一
            WHEN DAYOFWEEK(GETDATE()) = 0 THEN 6 
            ELSE DAYOFWEEK(GETDATE()) - 1 
          END,
        GETDATE()::date
    )
    and GETDATE()::date

注意事项 #

• 上述写法适配大部分SQL引擎DAYOFWEEK默认返回规则：0=周日、1=周一、2=周二……6=周六，如果你使用的数仓该函数返回规则不同（比如周一返回0、周日返回6），仅需要调整偏移量计算逻辑即可，规则为周一返回值为0时直接用-DAYOFWEEK(GETDATE())作为日期间隔
• 所有日期计算统一转为date类型（截断时分秒），避免时间精度问题导致的边界数据遗漏

内容的提问来源于stack exchange，提问作者C.Carrel