数据比对总结

大家好，欢迎来到IT知识分享网。

1. 数据量比对;
2. 数据内容比对;
3. 不同维度上比对, 查看维度因素;

一、 数据量比对

-- 总数据量比对 select aa.cnt as pre_cnt ,bb.cnt as pro_cnt ,( aa.cnt - bb.cnt ) as err ,( aa.cnt - bb.cn t)*1.000/bb.cnt as err_ratio from ( -- 测试数据 select '1' as id ,count(1) as cnt from ${tbl_name_1} where dt = '' ) aa join ( -- 生产数据 select '1' as id ,count(1) as cnt from ${tbl_name_2} where dt = '' ) bb on aa.id = bb.id ; -- 单维度比对数据量 select aa.cnt pro_cnt ,bb.cnt pre_cnt ,(aa.cnt - bb.cnt) as err_cnt ,(aa.cnt - bb.cnt)*1.00/aa.cnt as err_ratio from ( select app_id ,count(1) as cnt from tbl_pro where dt = '' group by app_id ) aa full join ( select app_id ,count(1) as cnt from tbl_pro where dt = '' group by app_id ) bb on aa.app_id = bb.app_id ;

二、数据内容比对

-- 内容比对 with tmp as ( select aa ,bb from ${tbl_name_1} where dt = '' except select aa ,bb from ${tbl_name_2} where dt = '' ) select * from tmp ;

三、指标比对

-- 比对指标总和 select aa.cnt pro_cnt ,bb.cnt pre_cnt ,(aa.cnt - bb.cnt) as err_cnt ,(aa.cnt - bb.cnt)*1.00/aa.cnt as err_ratio from ( select 'id' as id ,sum(active_num) as cnt from pro_tbl where dt = '' ) aa join ( select 'id' as id ,sum(active_num) as cnt from pre_tbl where dt = '' ) bb on aa.id = bb.id ; -- 某些维度上指标和比对 select aa.app_id ,aa.cnt as cnt_pro ,bb.cnt as cnt_pre ,(aa.cnt - bb.cnt) as err_cnt ,(aa.cnt - bb.cnt)*1.000/aa.cnt as error from ( select app_id ,sum(active_num) as cnt from pro_tbl where dt = '' group by app_id ) aa full join ( select app_id ,sum(active_num) as cnt from pre_tbl where dt = '' group by app_id ) bb on aa.app_id = bb.app_id order by aa.cnt desc ;

二十、差异分析

1. 随机函数

first、last函数会随机取维度值, 造成这些维度指标不完全一样, 可验证其它维度上指标是否一致.

2. join异常

表的join操作-CSDN博客

3. 增加过滤后数据变多

过滤掉了无效数据, 保留下了更过的有效数据;

比如pkgs数组字段有大量空值，row_number方式排序去重(rnk=1)前没有过滤pkgs为空的数据，这样获取了许多pkgs为空的数据，导致展开数组时会丢失有效数据.