Snowflake如何在WHERE子句中正确编写CASE日期筛选语句

需求规则 #

根据当前月份动态调整日期筛选范围：

• 当前月份为1月时，筛选范围为上一年年初至当日
• 当前月份非1月时，筛选范围为本年年初至当日

原有写法的语法问题 #

提供的SQL存在三处不符合Snowflake语法规则的问题：

• CASE表达式仅需在开头写一次CASE关键字，后续分支使用WHEN ... THEN ...结构即可，无需每个分支前重复写CASE
• 分支逻辑存在冗余，无需在每个THEN分支中重复写完整的BETWEEN判断，仅需通过CASE计算出动态的区间起始日期即可，结束日期统一为当前日期，可读性更高
• 原代码中使用了全角单引号‘’，SQL语法要求使用半角单引号'，否则会抛出标识符无效错误

可直接运行的实现方案 #

方案1：修正后的CASE写法（逻辑直观易读） #

WHERE
  -- 替换为实际日期字段，建议加表别名避免和DATE关键字冲突
  t.date_col BETWEEN
    CASE
      WHEN MONTH(CURRENT_DATE()) = 1 THEN DATE_TRUNC('YEAR', DATEADD(YEAR, -1, CURRENT_DATE()))
      ELSE DATE_TRUNC('YEAR', CURRENT_DATE())
    END
  AND CURRENT_DATE()

方案2：简化写法（无需CASE，代码更简洁） #

利用日期偏移特性可以省去CASE判断，执行效率无差异：

WHERE
  t.date_col BETWEEN
    -- 当前为1月时，往前推1个月落在上一年，年截断后取到上一年年初；非1月时往前推1个月仍在本年，年截断后取到本年年初
    DATE_TRUNC('YEAR', DATEADD(MONTH, -1, CURRENT_DATE()))
    AND CURRENT_DATE()

逻辑验证方法 #

可以单独运行日期计算部分确认结果符合预期：

-- 验证1月场景（手动指定日期为2024年1月15日）
SELECT
  DATE_TRUNC('YEAR', DATEADD(MONTH, -1, '2024-01-15'::DATE)) as start_date, -- 返回2023-01-01
  '2024-01-15'::DATE as end_date;

-- 验证非1月场景（手动指定日期为2024年5月15日）
SELECT
  DATE_TRUNC('YEAR', DATEADD(MONTH, -1, '2024-05-15'::DATE)) as start_date, -- 返回2024-01-01
  '2024-05-15'::DATE as end_date;

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