使用ROW_NUMBER去重后MINUS查询结果异常问题求助

我有两张结构完全相同的表table1和table2，分别在早间、晚间加载。直接执行以下MINUS查询：

select data1,data2,data3,file_dt from table1 minus select data1,data2,data3,file_dt from table2;

select data1,data2,data3,file_dt from table2 minus select data1,data2,data3,file_dt from table1;

返回0条记录，符合预期。但由于并行加载可能导致表内存在重复数据，我需要先获取两张表的唯一记录再做对比，尝试了两种方法后，MINUS查询仍返回非预期记录：

方法1（基于ROWID去重） #

select data1,data2,data3,file_dt from table1
 where rowid IN ( select rid
                    from (select rowid rid, 
                                 row_number() over (partition by data2,data3
                                   order by file_dt DESC) rn
                            from table1)
                   where rn = 1)
minus
select data1,data2,data3,file_dt from table2
 where rowid IN ( select rid
                    from (select rowid rid, 
                                 row_number() over (partition by data2,data3
                                   order by file_dt DESC) rn
                            from table2)
                   where rn = 1);

返回2条记录，预期应为0。

方法2（基于ROW_NUMBER去重） #

select data1,data2,data3,file_dt,
       ROW_NUMBER() OVER(PARTITION BY data2,data3
                         ORDER BY file_dt desc) rn        
from table1
WHERE rn = 1
minus
select data1,data2,data3,file_dt,
       ROW_NUMBER() OVER(PARTITION BY data2,data3
                         ORDER BY file_dt desc) rn        
from table2
WHERE rn = 1;

仍返回部分非预期记录。

问题根源分析 #

• 分区键与对比字段不匹配：窗口函数的PARTITION BY仅使用data2,data3，但对比时的字段包含data1,file_dt。这会导致同一data2,data3分组下，不同data1或file_dt的记录被归为一组，仅保留最新file_dt的一条，但两张表的同组数据可能因data1差异，去重后出现不一致。
• 方法2语法与逻辑错误：原SQL存在多余括号，且将生成的rn（行号）纳入MINUS对比。rn是单表内生成的序号，即使数据完全一致，两张表的行号也可能因数据存储顺序不同而不同，导致MINUS误判为不同记录。

修正方案 #

方案1：修正分区键，对齐对比维度

如果业务逻辑是按data1,data2,data3分组保留最新file_dt的记录，需将这三个字段都加入分区键：

-- 去重后对比table1与table2
select data1,data2,data3,file_dt
from (
    select data1,data2,data3,file_dt,
           row_number() over(partition by data1,data2,data3 order by file_dt desc) rn
    from table1
) t where rn = 1
minus
select data1,data2,data3,file_dt
from (
    select data1,data2,data3,file_dt,
           row_number() over(partition by data1,data2,data3 order by file_dt desc) rn
    from table2
) t where rn = 1;

方案2：用GROUP BY简化去重逻辑

若仅需保留每组最新的file_dt，可通过分组聚合实现：

select data1,data2,data3,max(file_dt) as file_dt
from table1
group by data1,data2,data3
minus
select data1,data2,data3,max(file_dt) as file_dt
from table2
group by data1,data2,data3;

方案3：验证并行加载数据完整性

虽然直接MINUS返回0，但并行加载可能存在数据未完全落库的情况。可先统计两张表去重后的总记录数，或用全字段对比确认数据是否真的一致。

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