数据测试全流程总结(小白易上手)
目录
数据测试定义
做数据测试时，有黑盒测试和白盒测试，其中黑盒测试就是通过查询语句，检查目标表的表结构是否与设计文档一致，主键是否唯一非空，有无异常值，极值是否超出正常范围，枚举值检查数据是否合理分布，对应字段和字段内容是否一致。白盒测试就是检查开发老师的源码，字段信息（字段长度，最大最小值，异常值），字段拼接是否有空是否去重，数学计算要考虑分母为0的情况，null值，计算单位是否统一等，默认值设置是否合理，关联约束条件时，关联条件约束和where条件约束，是否符合需求文档、口径。
一致性脚本写作风格
- 风格1可以单个字段新建子查询，然后left join。子查询里查询主键+加工的字段
- 风格2可以将每个子查询都新建一个临时表格，最后构建大宽表时，可以left join多张临时表，这种思维更加清晰，更符合编程思维，一个小模块一个小模块的解决问题
- 风格2强调主表，主表的条数与目标表一致，null值置空(coalesce)。这里的主表字段可以少一点，比如简单的客户号。其他临时表，可以有多个字段
一致性测试步骤
- 步骤一，union之后字段条数对不上
- 步骤二，一个字段一个字段对比，检查出条数不一致的字段
- 步骤三，提取不一致的字段下面的某些行数据，对比目标表和测试表，找出差异点，返回主键（客户号）
- 步骤四，根据主键，找出目标表，测试表，以及源表中的相关数据，进行对比
步骤一
方法1：大体上看条数是否一致
select count(*) from
(
select * from 开发老师的表
union
select * from 自己的表
)A
;
select count(*) from 开发老师的表
方法2：找出不一致的A.字段1
select A.字段1 from
(
select * from 开发老师的表
union
select * from 自己的表
)A
group by A.字段1 having count(1)>1
步骤二
一个字段一个字段对比，看条数是否一致，a表与b表中的字段1/字段2条数是否和目标表一致。
方法1：
select count(*) from
(
select a.字段1, a.字段2 from 开发老师的表 a
union
select b.字段1, b.字段2 from 自己的表 b
)A
;
select count(*) from 开发老师的表 --目标表
union 个数与单个表个数一致，就表示两张表字段内容与个数都一致。union有去重功能，union all不去重。
方法2：
select A.字段1 from
(
select 字段1, 字段2 from 开发老师的表
union
select 字段1, 字段2 from 自己的表
)A
group by A.字段1 having count(1)>1
对比字段2不同时，获取到字段1（主键）
这样我们从单个字段的条数上可以找到，不一致的列有哪些。
接下来我们再进一步，找出具体的某些行数据，进行目标表与测试表的对比。
步骤三
- 方法1：目标表与测试表，通过字段2（主键）左连接，对比字段1是否相等
Select a.字段2, a.字段1, b.字段1
from 开发老师目标表a left join 我的测试表b
on a.字段2=b.字段2
and a.data_dt = '2021-04-25'
where a.字段1<>b.字段1 or a.字段1 is null or b.字段1 is null
这里where后面的字段限制条件，还可以添加一些，where a.字段1<>b.字段1 or a.字段1 is null or b.字段1 is null
- 方法2：子查询替换上面的目标表和测试表
select a.字段2, a.字段1, b.字段1
from (select distinct 字段1, 字段2 from 我的测试表)a
left join (select distinct 字段1, 字段2 from 开发老师的目标表)b
on a.字段2=b.字段2
where a.字段1<>b.字段1 or a.字段1 is null or b.字段1 is null
- 方法3：下面可以看出同一个字段在不同表里的一些情况，同一列下面，我多的是哪些行。
select 字段1 from 我的测试表 where 字段1 not in ( select 字段1 from 开发老师的目标表)
步骤四
将步骤三中返回的主键（客户号、字段2），字段2下面的具体参数'xxx','xxx'提取出来，在目标表，测试表，源表中对比数据
select * from 库名.表名
where data_dt='2021-04-25'
and 字段2 in ('xxx','xxx'); --目标表
select * from 库名.表名
where 字段2 in ('xxx','xxx'); --测试表
select * from 库名.表名
where data_dt='2021-04-25'
字段2 in ('xxx','xxx'); --源表
补充知识
创建临时表
drop table if exists 库名.表名;
create table 库名.表名 as
select
cast(q.cust_id as varchar(20)) as cust_id --客户号
,cast(coalesce(q.cash_instl, 0) as decimal(28,4)) as cash_instl --现金
,cast('2021-04-25' as varchar(10)) as etl_dt --时间
from
主表A
left join
表B
on A.cust_id = B.cust_id
常用表达式
sum(if(字段1='消费', 字段2, 0)) as 字段3如果字段1等于消费，就返回字段2，
否则返回0，对返回结果求和，作为字段3trim(字段)去掉字段前后空格trim(字段) <>''来判断字段是否为空case when trim(字段) <>'' then 1 else 0 end如果字段不为空，则返回1，等同于if函数if( trim(字段) <>'' , 1, 0)如果字段不为空，则返回1case when 字段 is null then 1 else 0 end如果字段为null值，则返回1ifnull(字段表达式, 1)如果字段表达式为空，则返回1coalesce(字段1, 字段2, 9)返回第一个不为空的参数值rtrim(字段1, '号')删除末尾的‘号’字substr(字段1, 1, 10)从第1个位置开始截取10个字符（从左向右）length(字段)返回字段长度'' as 新字段名置空cast 字段1 as varchar(30)将字段1的字段类型转化为varchar(30)cast(case when age>=18 and <=30 then '[18, 30]' when age>30 and <=40 then '(30, 40]' else '未知' end as varchar(20)) as age_g等同于单元格下拉框效果date_add(timestamp startdate, int days)日期增加函数to_date(timestamp)返回日期部分
SQL黑盒测试模板
主键唯一
select
'表名' as name
, sum(case when coalesce(主键, '')='' then 1 else 0 end) as null_count --主键为空校验
,count(distinct 主键) key --重复性校验
,count(1) as row_num --总行数校验
from 库名.表名
where data_dt ='2021-04-25'
null校验
select
sum(case when 字段 is null then 1 else 0 end) as 字段
,sum(case when 字段 is null then 1 else 0 end) as 字段
from 库名.表名
where data_dt ='2021-04-25'
null decimal 为0
select
sum(case when 字段 <>0 then 1 else 0 end)as 字段
,sum(case when 字段 <>0 then 1 else 0 end)as 字段
from 库名.表名
where data_dt ='2021-04-25'
null varchar为空
select
sum(case when trim(字段)<>'' then 1 else 0 end) as 字段
,sum(case when trim(字段)<>'' then 1 else 0 end) as 字段
from 库名.表名
where data_dt ='2021-04-25'
极值 decimal
select '字段' as name, max(字段)as max, min(字段)as min from 库名.表名 where data_dt ='2021-04-25' union all
select '字段' as name, max(字段)as max, min(字段)as min from 库名.表名 where data_dt ='2021-04-25'
极值 varchar
select '字段' as name, max(length)as max_lth from (select lenght(字段)as length from 库名.表名 where data_dt ='2021-04-25') a union all
select '字段' as name, max(length)as max_lth from (select lenght(字段)as length from 库名.表名 where data_dt ='2021-04-25') a
枚举值
select '字段' as name, count(distinct 字段) as js from 库名.表名 where data_dt='2021-04-25' union all
select '字段' as name, count(distinct 字段) as js from 库名.表名 where data_dt='2021-04-25'
