如何在Redshift数据表中对比字段对值并处理NULL情况？

核心规则回顾 #

• NO_CHANGE：忽略含NULL的字段对后，所有非NULL的Old/New字段对值均相等；若所有字段对都含NULL，也标记为NO_CHANGE
• CHANGE：忽略含NULL的字段对后，存在任意一组非NULL的Old/New字段对值不相等

SQL实现示例 #

通用逻辑写法（适用于多数数据库） #

假设你的表包含old_name/new_name、old_age/new_age、old_email/new_email三组字段对，可通过以下SQL生成标签：

SELECT 
    *,
    CASE
        WHEN (
            -- 检查每组字段对：要么值相等，要么至少一方为NULL
            (old_name = new_name OR old_name IS NULL OR new_name IS NULL)
            AND (old_age = new_age OR old_age IS NULL OR new_age IS NULL)
            AND (old_email = new_email OR old_email IS NULL OR new_email IS NULL)
        ) THEN 'NO_CHANGE'
        ELSE 'CHANGE'
    END AS change_tag
FROM your_table;

更灵活的动态检查写法（MySQL 8.0+） #

如果字段对数量较多，可通过构造字段对集合来简化逻辑：

SELECT 
    *,
    CASE
        -- 检查是否存在非NULL且值不等的字段对
        WHEN EXISTS (
            SELECT 1
            FROM (
                VALUES 
                    (old_name, new_name),
                    (old_age, new_age),
                    (old_email, new_email)
                    -- 按需添加更多字段对
            ) AS pairs(old_val, new_val)
            WHERE old_val IS NOT NULL 
              AND new_val IS NOT NULL 
              AND old_val != new_val
        ) THEN 'CHANGE'
        ELSE 'NO_CHANGE'
    END AS change_tag
FROM your_table;

PostgreSQL 写法 #

SELECT 
    *,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM UNNEST(ARRAY[old_name, old_age, old_email]) AS old_vals
            WITH ORDINALITY
            JOIN UNNEST(ARRAY[new_name, new_age, new_email]) AS new_vals
            WITH ORDINALITY ON old_vals.ordinality = new_vals.ordinality
            WHERE old_vals IS NOT NULL 
              AND new_vals IS NOT NULL 
              AND old_vals != new_vals
        ) THEN 'CHANGE'
        ELSE 'NO_CHANGE'
    END AS change_tag
FROM your_table;

注意事项 #

• 将your_table替换为你的实际表名
• 根据数据表中的字段对，替换示例中的字段名
• 若字段为字符串类型，需注意数据库的大小写敏感配置，必要时用LOWER(old_val) = LOWER(new_val)进行统一大小写后的比较

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