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

中了毒蛇粉的貓發表於2021-06-01

目錄

資料測試定義

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

相關文章