基于非零数值为后续月份分配状态的SQL查询需求

需求 #

从给定的月度数据中，依据最后一个非零数值的时间为后续月份分配对应状态：

• 非零数值所在月份标记为'Active'
• 非零数值后的前3个月标记为'Newly Inactive'
• 非零数值后的第4至第6个月标记为'Inactive'
• 非零数值后的第7个月及以后标记为'Frozen'

输入数据 #

预期输出 #

初始代码问题分析 #

提供的初始查询存在几个关键问题：

1. 使用LAG(month)仅获取前一行的月份，无法定位到最后一个非零值的月份
2. 列名错误：用了total_amount但输入数据的数值列是value
3. 状态名称不匹配：写了'Freshly Inactive'，需求是'Newly Inactive'
4. 时间范围判断错误：需求中第4-6个月为Inactive，第7个月及以后为Frozen，但代码中写的是4-12个月为Inactive，大于12个月才是Frozen

修正后的SQL查询 #

WITH stg_status AS (
    SELECT 
        month,
        value,
        -- 获取全局最后一个非零数值的月份
        MAX(CASE WHEN value > 0 THEN month END) OVER () AS last_active_month,
        -- 计算当前月份与最后活跃月份的间隔（正数表示当前在活跃月份之后）
        MONTHS_BETWEEN(month, MAX(CASE WHEN value > 0 THEN month END) OVER ()) AS month_diff
    FROM 
        my_table
)
SELECT 
    month,
    value,
    CASE
        WHEN value > 0 THEN 'Active'
        WHEN month_diff BETWEEN 1 AND 3 THEN 'Newly Inactive'
        WHEN month_diff BETWEEN 4 AND 6 THEN 'Inactive'
        WHEN month_diff >=7 THEN 'Frozen'
        ELSE NULL -- 理论上不会出现，处理边界情况
    END AS status
FROM 
    stg_status
ORDER BY 
    month;

代码说明 #

1. 通过MAX(CASE WHEN value>0 THEN month END) OVER ()获取所有数据中最后一个有非零值的月份
2. 用MONTHS_BETWEEN计算当前月份与该活跃月份的间隔
3. 根据间隔范围匹配对应的状态规则，确保完全符合需求

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