【データ検証/BigQuery】SQLでのデータ検証について

今回の課題

自社でSQLのテストを行っていなかったため、
SQLで抽出するデータが正しいかテストする方法について調べてみた。

データ検証の方法という記事を参考に解釈したことをまとめた。

制約の検証

UNIQUE制約

指定したカラムに、ユニークな値が格納されているかどうか確認できる。
ユニークな値が入っていればtrueが出力される。

select
    count(*) = count(distinct [ユニークな値が入っているか確認したいカラム] as check 
from
    `テーブル名`

NOT NULL制約

指定したカラムに、NULLの存在の有無を確認できる。
NULLが存在しなければ、trueと出力される。

select
    count(*) = 0 as check 
from
    `テーブル名`
where
    [nullの有無を確認したいカラム] is null

CHECK制約

指定したカラムに、指定した値以外が格納されていないかを確認できる。
指定した値（下記で言うと「PC」又は「SP」）以外が含まれていなければ、trueが出力される。

select
    count(*) = 0 as check
from
    `テーブル名` 
where
    [指定した値以外含まれていないか確認したいカラム] not in ('PC', 'SP')

FOREIGN KEY制約

外部キーでJOINできなかったレコードが存在しなかったことを確認できる。
JOINできなかったレコードが存在しなかった場合、trueが出力される。

select
    count(*) = 0 as check
from
    `テーブル名` as tbl1
    left join `テーブル名` as tbl2
        on tbl1.key = tbl2.key
where
    tbl2 is null

テーブル同士の比較検証

レコードの突き合わせ

確認したいテーブル同士を相互にEXCEPT演算をして差分を取、完全一致しているかを確認する。
trueが出力されたら、両テーブルのデータは一致すると分かる。

with master as (
    select * from `テーブル1` except select * from `テーブル2`
union all
    select * from `テーブル2` except select * from `テーブル1`
)
select 
    count(*) = 0 as check
from
    master

集計値の比較

1)レコード数を確認する場合の例

レコード数がお互いのテーブルで一致するかを確認する。
一致すればtrueを出力する。

select (select count(*) from `テーブル1`) = (select count(*) from `テーブル2) as check

2)pageiviewの平均値を確認する場合の例

先週時点から平均値が増加したかを確認する。
平均値が増加していれば、trueを返す。

select (select avg(pv) from `今週の値が格納されたテーブル`) > (select avg(pv) from `先週の値が格納されたテーブル`) as check

※参考：データ検証の方法