Snowflake中基于old_group和new_group列填充缺失值的SQL实现

根据你的数据需求，核心逻辑是初始分组为BATCH，当出现非空的new_group时，后续行继承该分组，直到下一个非空new_group出现，同时需要将L3映射为LEVEL_3。以下是可行的SQL方案：

实现步骤 #

1. 清洗new_group：将L3转换为LEVEL_3，统一分组命名。
2. 标记分组阶段：通过累加非空new_group的数量，将数据划分为不同的分组阶段。
3. 关联阶段与分组：为每个阶段分配对应的分组值，最终关联到原始数据。

完整SQL代码 #

WITH cleaned_data AS (
    SELECT 
        review_id,
        old_group,
        new_group,
        -- 统一分组命名，处理L3到LEVEL_3的映射
        CASE new_group
            WHEN 'L3' THEN 'LEVEL_3'
            ELSE new_group
        END AS cleaned_new_group,
        -- 标记分组阶段：每遇到一个有效分组，阶段数+1
        SUM(CASE WHEN cleaned_new_group IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY review_id) AS group_stage
    FROM your_table -- 替换为你的实际表名
),
group_stage_mapping AS (
    SELECT 
        group_stage,
        -- 阶段0对应初始分组BATCH，其他阶段取该阶段的有效分组
        CASE 
            WHEN group_stage = 0 THEN 'BATCH'
            ELSE MAX(cleaned_new_group) 
        END AS desired_group
    FROM cleaned_data
    GROUP BY group_stage
)
SELECT 
    cd.review_id,
    cd.old_group,
    cd.new_group,
    gsm.desired_group AS desired_result
FROM cleaned_data cd
JOIN group_stage_mapping gsm ON cd.group_stage = gsm.group_stage
ORDER BY cd.review_id;

为什么你的原语句失效？ #

你之前使用的FIRST_VALUE(new_group IGNORE NULLS) OVER (ORDER BY review_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)存在两个问题：

• 从当前行向后取第一个非空值，会导致最前面的行（如review_id=1、2）取到后续的LEVEL_1，而非初始的BATCH。
• 最后几行（review_id=7-9）之后没有非空的new_group，因此返回null。

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