Redshift中5亿条含NULL数据去重并保留最新上传日期的最优方案

核心需求 #

处理一张含5亿条记录的Redshift表，表结构为id1、id2、id3、upload_date：

• id1为关键字段，存在重复记录
• upload_date非空，其余列可能为NULL
• 最终需保留每个id1对应的upload_date最新的完整记录，且不能丢失原有的NULL值

已尝试方案的问题 #

• GROUP BY + MAX(upload_date)：因id2/id3存在NULL值，GROUP BY时NULL会被视为不同分组，导致重复结果
• NVL处理NULL：强制将NULL替换为其他值，会丢失原始数据的NULL状态，不符合需求
• SELECT DISTINCT：无法精准筛选出每个id1对应的最新upload_date记录

ROW_NUMBER() 方案的可行性 #

方案代码 #

SELECT id1, id2, id3, upload_date
FROM (
    SELECT 
        id1, id2, id3, upload_date,
        ROW_NUMBER() OVER (PARTITION BY id1 ORDER BY upload_date DESC) AS row_num
    FROM your_table_name
) t
WHERE row_num = 1;

在5亿数据量下的可行性 #

Redshift作为MPP架构的数据仓库，对窗口函数的处理有原生优化能力，该方案完全可行，但需注意几个关键点：

• 分区基数影响：如果id1的不同值数量（基数）较高，Redshift会将数据分散到多个节点并行处理，效率会更高；若id1基数极低，单个分区数据量过大可能拖慢性能
• 排序效率：upload_date非空，排序时无额外异常；如果upload_date被设为表的排序键（SORT KEY），能直接利用预排序数据，减少计算开销
• 资源配置：确保集群有足够的节点数量、CPU和内存资源，避免因资源不足导致任务超时

更优替代方案 #

1. 用QUALIFY子句简化代码 #

Redshift支持QUALIFY子句，可直接在主查询中过滤窗口函数结果，省去一层子查询，代码更简洁：

SELECT id1, id2, id3, upload_date
FROM your_table_name
QUALIFY ROW_NUMBER() OVER (PARTITION BY id1 ORDER BY upload_date DESC) = 1;

性能和ROW_NUMBER()子查询方案基本一致，但可读性更强。

2. 优化表的DIST/SORT KEY #

如果将表的分布键（DIST KEY）设为id1，相同id1的数据会集中在同一节点，窗口函数的分区处理无需跨节点传输数据，能大幅提升性能；同时将upload_date设为排序键（SORT KEY），排序操作会直接利用预排序数据，减少计算量。

3. 增量处理（针对持续写入的表） #

如果表是持续新增数据的，无需每次全量扫描5亿条记录：

• 记录上次处理的最大upload_date
• 仅对新增的记录进行去重合并，再和历史去重结果合并
  这种方式能显著降低每次处理的数据量，适合周期性去重需求。

4. MAX()关联原表方案 #

若担心ROW_NUMBER()的排序开销，可先通过GROUP BY获取每个id1的最新日期，再关联原表取对应记录：

WITH latest_dates AS (
    SELECT id1, MAX(upload_date) AS latest_upload_date
    FROM your_table_name
    GROUP BY id1
)
SELECT t.id1, t.id2, t.id3, t.upload_date
FROM your_table_name t
JOIN latest_dates ld ON t.id1 = ld.id1 AND t.upload_date = ld.latest_upload_date;

注意：如果同一个id1有多条记录的upload_date等于最新日期，该方案会返回多条结果；若业务上保证每个id1的最新日期唯一，该方案效率可能略高于ROW_NUMBER()，因为避免了全局排序。

总结 #

• ROW_NUMBER()（或QUALIFY简化版）是最稳妥的方案，能准确保留NULL值且逻辑清晰，5亿数据量下Redshift完全可以支撑
• 最优方案需结合表的键设计和业务场景：
  • 全量处理优先优化表的DIST/SORT KEY配置，再选择QUALIFY或ROW_NUMBER()方案
  • 增量处理优先采用增量合并方式，减少计算开销

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