如何高效移除重复记录中含NULL值的行（保留非重复NULL行）

问题场景 #

原始数据：

需求：对于存在重复的（ID,X）组，移除其中Y为NULL的行；对于无重复的（ID,X）组，保留Y为NULL的行。最终期望结果：

由于表数据量庞大，不想用窗口函数生成辅助列，寻求最高效实现方案。

方案1：EXISTS关联直接过滤 #

SELECT t1.ID, t1.X, t1.Y
FROM your_table t1
WHERE NOT (
    t1.Y IS NULL 
    AND EXISTS (
        SELECT 1 
        FROM your_table t2 
        WHERE t2.ID = t1.ID 
          AND t2.X = t1.X 
          AND t2.Y IS NOT NULL
    )
);

逻辑说明：直接判断当前行是否需要排除——如果当前行Y是NULL，且同（ID,X）组里存在非NULL的Y值，就排除这行；其余情况（Y非NULL，或者Y为NULL但同组没有非NULL行）全部保留。

方案2：UNION ALL拆分查询（适合索引优化） #

-- 第一部分：取出所有Y不为NULL的行
SELECT ID, X, Y
FROM your_table
WHERE Y IS NOT NULL

UNION ALL

-- 第二部分：取出Y为NULL且同组无其他非NULL行的记录
SELECT t1.ID, t1.X, t1.Y
FROM your_table t1
WHERE t1.Y IS NULL
  AND NOT EXISTS (
      SELECT 1 
      FROM your_table t2 
      WHERE t2.ID = t1.ID 
        AND t2.X = t1.X 
        AND t2.Y IS NOT NULL
  );

逻辑说明：把需求拆成两个独立的查询，再用UNION ALL合并结果。这种写法能最大化利用索引：如果表上有(ID, X, Y)的联合索引，两个子查询都能快速定位数据，避免全表扫描。

性能优化提示 #

• 必须给表建立(ID, X, Y)的联合索引，两种方案都能依赖索引完成关联和过滤，大幅减少IO开销。
• 窗口函数（如COUNT() OVER()）需要全表扫描并计算分组统计，数据量大时内存和IO开销远高于上述两种方案，因此不推荐。

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