Snowflake宽格式财年表：筛选最近36个月数据并置零历史值

问题说明 #

现有Snowflake宽格式表，财年始于10月，需实现以下需求：

• 以当前日期为基准，仅保留最近36个月的有效数据
• 2021年5月之前的所有数据统一置为0
• 保留原表的宽格式结构

原始表结构与数据 #

期望结果 #

SQL实现脚本 #

WITH date_params AS (
    -- 计算有效数据的起始日期：取2021年5月1日和当前日期往前推36个月的较晚值
    SELECT 
        GREATEST('2021-05-01'::DATE, DATEADD(MONTH, -36, CURRENT_DATE())) AS valid_start_date
)
SELECT 
    Account,
    Year,
    -- 按财年规则映射每个月份字段到实际日期，判断是否在有效范围内
    CASE WHEN DATE_FROM_PARTS(Year - 1, 10, 1) >= valid_start_date THEN Oct ELSE 0 END AS Oct,
    CASE WHEN DATE_FROM_PARTS(Year - 1, 11, 1) >= valid_start_date THEN Nov ELSE 0 END AS Nov,
    CASE WHEN DATE_FROM_PARTS(Year - 1, 12, 1) >= valid_start_date THEN Dec ELSE 0 END AS Dec,
    CASE WHEN DATE_FROM_PARTS(Year, 1, 1) >= valid_start_date THEN Jan ELSE 0 END AS Jan,
    CASE WHEN DATE_FROM_PARTS(Year, 2, 1) >= valid_start_date THEN Feb ELSE 0 END AS Feb,
    CASE WHEN DATE_FROM_PARTS(Year, 3, 1) >= valid_start_date THEN Mar ELSE 0 END AS Mar,
    CASE WHEN DATE_FROM_PARTS(Year, 4, 1) >= valid_start_date THEN Apr ELSE 0 END AS Apr,
    CASE WHEN DATE_FROM_PARTS(Year, 5, 1) >= valid_start_date THEN May ELSE 0 END AS May,
    CASE WHEN DATE_FROM_PARTS(Year, 6, 1) >= valid_start_date THEN Jun ELSE 0 END AS Jun,
    CASE WHEN DATE_FROM_PARTS(Year, 7, 1) >= valid_start_date THEN Jul ELSE 0 END AS Jul,
    CASE WHEN DATE_FROM_PARTS(Year, 8, 1) >= valid_start_date THEN Aug ELSE 0 END AS Aug,
    CASE WHEN DATE_FROM_PARTS(Year, 9, 1) >= valid_start_date THEN Sep ELSE 0 END AS Sep
FROM your_table_name, date_params;

逻辑说明 #

1. 有效起始日期计算：通过GREATEST函数确保数据不会早于2021年5月，同时也不会超出最近36个月的范围。
2. 财年日期映射：由于财年始于10月，每行的Year对应财年的结束年份（如Year=2021对应2020年10月至2021年9月），因此将Oct-Dec映射到上一年，Jan-Sep映射到当前年份。
3. 条件判断：对每个月份字段使用CASE语句，若该月份的实际日期在有效范围内则保留原值，否则置为0。

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