Snowflake软删除标记逻辑修正：按规则标记前置行D

问题回顾 #

现有两张Snowflake表：

• original_table：字段为case_num、code、project_name、sp_id、updated_date，同一case_num下code+project_name+sp_id的组合唯一
• additional_table：仅含case_num、timestamp，每日执行截断加载

需要实现两个软删除（标记为D）规则：

1. 同一case_num分区内，若当前行与前一行的code/project_name/sp_id任一存在变更，前一行需标记为D
2. 若case_num未出现在当日的additional_table中，对应所有记录标记为D

原SQL存在两个问题：

• 错误将D标记在发生变更的行，而非其前置行
• 同一case_num、同一updated_date存在多条记录时，错误标记其中一条为删除状态

修正后的SQL方案 #

WITH current_day_cases AS (
    -- 提取当日存在的case_num集合
    SELECT DISTINCT case_num
    FROM additional_table
    -- 若timestamp是datetime类型，替换为DATE(timestamp) = CURRENT_DATE()
    WHERE timestamp = CURRENT_DATE()
),
ranked_records AS (
    SELECT 
        ot.*,
        -- 生成当前行的唯一哈希键，用于对比字段变更
        HASH(code, project_name, sp_id) AS curr_hash,
        -- 获取前一行的哈希键，按case_num分区、更新时间+字段排序保证顺序稳定
        LAG(HASH(code, project_name, sp_id)) OVER (
            PARTITION BY case_num 
            ORDER BY updated_date ASC, code ASC, project_name ASC, sp_id ASC
        ) AS prev_hash,
        -- 标记当前行是否为case_num下的最新记录（rn=1为最新）
        ROW_NUMBER() OVER (
            PARTITION BY case_num 
            ORDER BY updated_date DESC, code DESC, project_name DESC, sp_id DESC
        ) AS rn
    FROM original_table ot
)
SELECT 
    case_num,
    code,
    project_name,
    sp_id,
    updated_date,
    CASE
        -- 规则1：当前行的下一行存在字段变更，且当前行不是最新行 → 标记为D（前置行）
        WHEN LEAD(curr_hash) OVER (
            PARTITION BY case_num 
            ORDER BY updated_date ASC, code ASC, project_name ASC, sp_id ASC
        ) <> curr_hash 
             AND rn <> 1 THEN 'D'
        -- 规则2：case_num不在当日集合中 → 所有行标记为D
        WHEN cc.case_num IS NULL THEN 'D'
        -- 其他情况为有效记录，不标记删除
        ELSE NULL
    END AS delete_flag
FROM ranked_records rr
LEFT JOIN current_day_cases cc ON rr.case_num = cc.case_num
ORDER BY case_num, updated_date ASC;

关键逻辑说明 #

1. 当日case_num集合：current_day_cases通过additional_table筛选出当日存在的case_num，用于规则2的判断
2. 哈希键对比：用HASH()函数生成三个字段的唯一哈希值，替代字符串拼接，避免因字段含特殊字符导致的误判，更可靠
3. 稳定排序：窗口函数排序时，除了updated_date，额外加上code/project_name/sp_id，确保同一日期多条记录的排序稳定，避免随机错误标记
4. 前置行标记：通过LEAD()函数判断当前行的下一行是否发生变更，若有变更且当前行不是最新行，就标记为D，精准对应规则1的前置行要求
5. 最新行处理：每个case_num的最新行（rn=1）仅在case_num不在当日集合时才标记D，否则保留为有效记录

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