資料測試全流程總結(小白易上手)

目錄

• 資料測試定義
  • 一緻性腳本寫作風格
  • 一緻性測試步驟
• 補充知識
  • 建立臨時表
  • 常用表達式
• SQL黑盒測試模闆
  • 主鍵唯一
  • null校驗
  • null decimal 為0
  • null varchar為空
  • 極值 decimal
  • 極值 varchar
  • 枚舉值

資料測試定義

做資料測試時，有黑盒測試和白盒測試，其中黑盒測試就是通過查詢語句，檢查目标表的表結構是否與設計文檔一緻，主鍵是否唯一非空，有無異常值，極值是否超出正常範圍，枚舉值檢查資料是否合理分布，對應字段和字段内容是否一緻。白盒測試就是檢查開發老師的源碼，字段資訊（字段長度，最大最小值，異常值），字段拼接是否有空是否去重，數學計算要考慮分母為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

1. 方法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
           

1. 方法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'