比较2个DW表之间的数据

I'm a little confused here. I'm testing some data quality issues in a DW, I need to know if the LOAN_SID in one table matches the other table. I was using this query but I'm not sure if I'm correct, if it matches there is an issue if it doesn't everything is good.

我在这里有点困惑。我正在测试DW中的一些数据质量问题,我需要知道一个表中的LOAN_SID是否与另一个表匹配。我正在使用这个查询,但我不确定我是否正确,如果匹配存在问题,如果它不是一切都很好。

So here is the query:

所以这是查询:

(select count(LOAN_SID)  from DW_DW.AGG_LOAN_SS_MONTHLY
 minus
 select LOAN_SID from DW_DW.F_LOAN_UNWOUND_TRAN_DAILY) 
union all
( 
 select LOAN_SID  from DW_DW.F_LOAN_UNWOUND_TRAN_DAILY
 minus
 select LOAN_SID from DW_DW.AGG_LOAN_SS_MONTHLY
)

Here is the other:

这是另一个:

SELECT  LOAN_SID
FROM DW_DW.AGG_LOAN_SS_MONTHLY A  
WHERE not EXISTS (SELECT LOAN_SID 
                  FROM DW_DW.F_LOAN_UNWOUND_TRAN_DAILY B
                  WHERE A.LOAN_SID = B.LOAN_SID);

Please Help!!!

1 个解决方案

#1

this gives all the sid that are unique in both tables. It is not a terribly efficient query and should not be run on large tables without some testing.

这给出了两个表中唯一的所有sid。它不是一个非常有效的查询,不应该在没有一些测试的情况下在大型表上运行。

 select * from
( (select LOAN_SID, 'F_LOAN_UNWOUND_TRAN_DAILY' AS SOURCE  
 from DW_DW.F_LOAN_UNWOUND_TRAN_DAILY
 minus
 select LOAN_SID, 'AGG_LOAN_SS_MONTHLY'
 from DW_DW.AGG_LOAN_SS_MONTHLY)
UNION ALL
 ( select LOAN_SID, 'AGG_LOAN_SS_MONTHLY' AS SOURCE  
 from DW_DW.AGG_LOAN_SS_MONTHLY)
 minus
select LOAN_SID, 'F_LOAN_UNWOUND_TRAN_DAILY' 
 from DW_DW.F_LOAN_UNWOUND_TRAN_DAILY))

To get the SIDS that are in both tables

获取两个表中的SIDS

select a.LOAD_SID
from DW_DW.AGG_LOAN_SS_MONTHLY a, DW_DW.F_LOAN_UNWOUND_TRAN_DAILY b
where a.LOAN_SID = b.LOAN_SID;

#1