如何编写Snowflake环境SQL查询按日期规则获取发票数据

核心逻辑 #

通过判断当前日期的所属区间，动态计算需要筛选的发票起始日期，最终仅保留大于等于该起始日期的发票数据即可。 #

分步实现逻辑 #

• 第一步：取当前日期的日数值，使用Snowflake原生函数 DAY(CURRENT_DATE) 即可获取
• 第二步：根据日数值判断所属区间，计算筛选起始日期：
  • 若当日在14日-27日区间：起始日期为当月14日，计算方式为 DATE_FROM_PARTS(YEAR(CURRENT_DATE), MONTH(CURRENT_DATE), 14)
  • 若当日在28日-月末区间：起始日期为当月28日，计算方式为 DATE_FROM_PARTS(YEAR(CURRENT_DATE), MONTH(CURRENT_DATE), 28)
  • 若当日在1日-13日区间：起始日期为上月28日，计算方式为 DATEADD('day', 27, DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE)))（先取上月第一天，再加27天即为上月28日，自动适配跨月、跨年场景）
• 第三步：将上述判断逻辑写入WHERE条件的CASE WHEN语句中即可

参考伪代码 #

SELECT *
FROM 你的发票表名
WHERE 发票日期字段 >= CASE
    -- 当月14-27日，取当月14日及之后
    WHEN DAY(CURRENT_DATE) BETWEEN 14 AND 27 
        THEN DATE_FROM_PARTS(YEAR(CURRENT_DATE), MONTH(CURRENT_DATE), 14)
    -- 当月28日及之后，取当月28日及之后
    WHEN DAY(CURRENT_DATE) >= 28 
        THEN DATE_FROM_PARTS(YEAR(CURRENT_DATE), MONTH(CURRENT_DATE), 28)
    -- 当月1-13日，取上月28日及之后
    ELSE DATEADD('day', 27, DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE)))
END

补充说明 #

• 所有用到的函数均为Snowflake官方内置函数，无需额外依赖
• 如果业务需要指定时区，可将CURRENT_DATE替换为CONVERT_TIMEZONE('你所需的时区', CURRENT_TIMESTAMP)::DATE，避免时区差异导致的日期判断错误
• 逻辑已自动适配跨年场景：例如当前为2024年1月5日，会自动取2023年12月28日作为起始筛选日期

内容的提问来源于stack exchange，提问作者João Pedro Tavares