数据测试

做数据测试时，有黑盒测试和白盒测试，其中黑盒测试就是通过查询语句，检查目标表的表结构是否与设计文档一致，主键是否唯一非空，有无异常值，极值是否超出正常范围，枚举值检查数据是否合理分布，对应字段和字段内容是否一致。白盒测试就是检查开发老师的源码，字段信息（字段长度，最大最小值，异常值），字段拼接是否有空是否去重，数学计算要考虑分母为0的情况，null值，计算单位是否统一等，默认值设置是否合理，关联约束条件时，关联条件约束和where条件约束，是否符合需求文档、口径。

一致性脚本写作风格

1. 风格1可以单个字段新建子查询，然后left join。子查询里查询主键+加工的字段
2. 风格2可以将每个子查询都新建一个临时表格，最后构建大宽表时，可以left join多张临时表，这种思维更加清晰，更符合编程思维，一个小模块一个小模块的解决问题
3. 风格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. 方法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. 方法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. 方法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

常用表达式

1. sum(if(字段1='消费', 字段2, 0)) as 字段3 如果字段1等于消费，就返回字段2，
   否则返回0，对返回结果求和，作为字段3
2. trim(字段) 去掉字段前后空格
3. trim(字段) <>'' 来判断字段是否为空
4. case when trim(字段) <>'' then 1 else 0 end 如果字段不为空，则返回1，等同于if函数
5. if( trim(字段) <>'' , 1, 0) 如果字段不为空，则返回1
6. case when 字段 is null then 1 else 0 end 如果字段为null值，则返回1
7. ifnull(字段表达式, 1) 如果字段表达式为空，则返回1
8. coalesce(字段1, 字段2, 9) 返回第一个不为空的参数值
9. rtrim(字段1, '号') 删除末尾的‘号’字
10. substr(字段1, 1, 10) 从第1个位置开始截取10个字符（从左向右）
11. length(字段) 返回字段长度
12. '' as 新字段名 置空
13. cast 字段1 as varchar(30) 将字段1的字段类型转化为varchar(30)
14. 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 等同于单元格下拉框效果
15. date_add(timestamp startdate, int days) 日期增加函数
16. 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'