数据稽核常用语法

查询重复记录
select count(A) from table group by A having count(A)>1
或者select count(*) from table where 具体日期 and 关键唯一字段 in(select 关键唯一字段 from table where 具体日期 group by 重复字段 having count(重复字段)>1 )

参照完整性(来源表数据大于目标表,表示正确)
select A,B from table1 where condition --目标表
except
select A,B from table2 where condition --来源表
或者 select secrity_code from t_secritya
where secrity_code not in (select secrity_code from t_secrity_d)

目标表与来源表一致性 (两表记录数应该一致)
select 'actualTableName' table_name, t1.field, t1_total -t2_total as diff_count from
(select 'aimTableName' table_name, field, count(1) t1_total from aimTableName group by field) t1, -- 目标表记录数
(select 'originalTableName' table_name, field, count(1) t2_total from originalTableName group by field) t2 -- 来源表记录数
where t1.field = t2.field; --两者无偏差输出 diff_count 为 0；

6.目标表与来源表的数据对比，使用except
校验数据提取正确性及在 ETL加载过程种是否产生被阶段、精度丢失、乱码等问题；
select field1, field2, ... from originalTable where condition
except
select field1, field2, ... from aimTable where condition