はじめに

こんにちは、久しぶりに技術系の記事を書きます、株式会社カンムで機械学習エンジニアをしている fkubota です。
今日はSQLについてです。
弊社に入社してから毎日のようにSQLのクエリを書いてきました。
クエリを書き始めてからもう3年が経とうとしています。
日々クエリを書きながら少しずつ自分のスタイルが出来上がってきているのを日々実感しています。
僕は

正確で
読みやすく
再利用しやすいクエリを
高速に

生み出すための工夫を重ねてきました。
結果的にテスト駆動開発ぽいスタイルが生まれたので今日は紹介してみようと思います。
似たような記事がないので少しドキドキですが温かい気持ちで読んでもらえると嬉しいです。

対象読者

対象読者は、分析のためにクエリを書いている人とします。
プロダクトに乗せるクエリというより、ビジネス的になにか示唆を得たいときにクエリを書く人を想定します。

痛み

クエリを日々書いていると書きたいものが書けているか不安になることはないでしょうか？
僕はそういう気持ちになることは頻繁で、とくに慣れていないテーブルや新しいパターンの集計を行うときなんかは、クエリの隙間に select hoge from foo などと書いて一部実行して目で確認するということをよく行います。
「nullはないよな？」「xxxに対してユニークだよな？」「xxxはマイナスにならないはず...」などなど心配事は多岐に及び、さらに複雑になるほどその数は増えていきます。
そうやって一つ一つを確認していきながら丁寧に書いたクエリは、時間が立つと行数も増え、何を確認して何を確認していないかがあやふやになり、不鮮明な不安が積もるのは必須です。
コメントで確認したことをメモしていても、クエリ前半で確認が取れていたことが、クエリの後半でも担保されているかは別問題ということもよくある状況でしょう。(予期しない動作のjoinにより前半部分では存在しなかったnullがクエリの途中から追加されていた。など)

再利用するときにも苦しくなるときがあります。
このクエリはどこまで信用できるんだっけ？といった不安がつきまといます。
クエリを書いている時間は、ワーキングメモリに可視化されないクエリの事情が脳内に展開されていますが、1ヶ月後に見たときにはどこまで信用して良いものかわからないのが常です。
クエリというものは常にそうした不安と付き合いながら分析していくものだとは思いますが、こういう痛みを弱める工夫はあってもいいでしょう。

ということで、僕の場合はこうしているよというのを紹介してみます。
少なくとも僕はこの方法を取ることによって、

より高速に
より正確性の有る

クエリを書くことができています。
加えて後で読んだクエリの品質の把握も容易にできていて上記の痛みはかなり解消されたと実感しています。
それでは少しずつ説明していきます。

クエリにテストを書く

上述した痛みを解決するために行っているクエリに書くテストを例を交えながら紹介していきたいと思います。
今回はBigQueryで書いていますが、その他でも同様なことはできます。(把握している限りですが)

題材としてECサイトのテーブルを想定します。
テーブルは、usersとshoppingの二種類。
前者はユーザそれぞれの情報で、shoppingはそのユーザの購買行動を記録しています。

user_id	name	age	registered_date
1	aaa	25	2023-01-01
2	bbb	30	2023-01-01
3	ccc	20	2023-03-01
4	ddd	43	2023-07-01
5	eee	18	2023-12-01

shopping_id	user_id	amount	shopping_date
1	1	1000	2023-01-01
2	3	2000	2023-03-04
3	3	1500	2023-04-10
4	1	1000	2023-05-01
5	4	5000	2023-07-02
6	4	3000	2023-10-15

だれでも手元でイジイジできるように with句でCTE(Common Table Expression)化しておきました。

with users as (
SELECT * FROM UNNEST([STRUCT<user_id INT64, name STRING, age INT64, registered_date date>
    (1, 'aaa', 25, date('2023-01-01'))
    , (2, 'bbb', 30, date('2023-01-01'))
    , (3, 'ccc', 20, date('2023-03-01'))
    , (4, 'ddd', 43, date('2023-07-01'))
    , (5, 'eee', 18, date('2023-12-01'))
    ])
)


, shopping as (
SELECT * FROM UNNEST([STRUCT<shopping_id INT64, user_id INT64, amount INT64, shopping_date date>
    (1, 1, 1000, date('2023-01-01'))
    , (2, 3, 2000, date('2023-03-04'))
    , (3, 3, 1500, date('2023-04-10'))
    , (4, 1, 1000, date('2023-05-01'))
    , (5, 4, 5000, date('2023-07-02'))
    , (6, 4, 3000, date('2023-10-15'))
    ])
)

20歳以上のユーザの合計購入金額を出してみます。

with users as (
SELECT * FROM UNNEST([STRUCT<user_id INT64, name STRING, age INT64, registered_date date>
    (1, 'aaa', 25, date('2023-01-01'))
    , (2, 'bbb', 30, date('2023-01-01'))
    , (3, 'ccc', 20, date('2023-03-01'))
    , (4, 'ddd', 43, date('2023-07-01'))
    , (5, 'eee', 18, date('2023-12-01'))
    ])
)


, shopping as (
SELECT * FROM UNNEST([STRUCT<shopping_id INT64, user_id INT64, amount INT64, shopping_date date>
    (1, 1, 1000, date('2023-01-01'))
    , (2, 3, 2000, date('2023-03-04'))
    , (3, 3, 1500, date('2023-04-10'))
    , (4, 1, 1000, date('2023-05-01'))
    , (5, 4, 5000, date('2023-07-02'))
    , (6, 4, 3000, date('2023-10-15'))
    ])
)

-- ここから分析クエリ

, users_over20 as (
select *
from users
where age>=20 
)

, tbl_agg as (
select
    u.user_id
    , max(u.age) as age
    , sum(s.amount) as total_amount
from users_over20 u
left join shopping s on u.user_id=s.user_id
group by u.user_id 
)

select *
from tbl_agg

結果

というまぁ簡単なクエリですが、これが大規模で全数を目視できなかったり初めて使うので元のテーブルへの理解が十分でないと想定して、確認作業というものをやっていこうと思います。
今回は、users.ageに nullはないか？、 tbl_agg.total_amountにnullはないか？ を確認していこうと思います。
users.ageに関しては、ユーザの自由記入欄でnullになっているかも？とかそういう目線での疑いです。
すでにわかっていることですが、total_amountにはnullがあります。shoppingテーブルに存在しない→購買行動を行っていない、のでこれは0とするのが適切な処理でしょう。
こうあるべき を確認してクエリの品質を担保していきます。

users.age に nullがないと確認するのはどうやるべきでしょうか？
通常であれば、CTEとCTEの間に select ... from ... と書いて部分実行するか、別クエリで確認するなどでしょう。
total_amountの確認も同様かと思います。

テストを行うために tbl_agg の CTE と最後の select の間に test という名前のCTEを追加する形で書いていきたいと思います。
結論から言うと以下のようになります。

with users as (
SELECT * FROM UNNEST([STRUCT<user_id INT64, name STRING, age INT64, registered_date date>
    (1, 'aaa', 25, date('2023-01-01'))
    , (2, 'bbb', 30, date('2023-01-01'))
    , (3, 'ccc', 20, date('2023-03-01'))
    , (4, 'ddd', 43, date('2023-07-01'))
    , (5, 'eee', 18, date('2023-12-01'))
    ])
)


, shopping as (
SELECT * FROM UNNEST([STRUCT<shopping_id INT64, user_id INT64, amount INT64, shopping_date date>
    (1, 1, 1000, date('2023-01-01'))
    , (2, 3, 2000, date('2023-03-04'))
    , (3, 3, 1500, date('2023-04-10'))
    , (4, 1, 1000, date('2023-05-01'))
    , (5, 4, 5000, date('2023-07-02'))
    , (5, 4, 3000, date('2023-10-15'))
    ])
)



, users_over20 as (
select *
from users
where age>=20 
)


, tbl_agg as (
select
    u.user_id
    , max(u.age) as age
    , sum(s.amount) as total_amount
from users_over20 u
left join shopping s on u.user_id=s.user_id
group by u.user_id 
)


, test as (
select
    -- ageにnullがない
    case
        when exists(
            select 1
            from users
            where age is null
            ) then error('ageにnullが存在します')
        else '問題なし'
        end as test1
    
    
    -- total_amountにnullがない  
    , case
        when exists(
            select 1
            from tbl_agg as t
            where t.total_amount is null
            ) then error('total_amountにnullが存在します')
        else '問題なし'
        end as test2
)



select *
from tbl_agg
cross join test

結果は以下のようになりました。

total_amountにnullが存在するために、用意していたエラー文が出ました。

sum(s.amount) as total_amount を sum(case when s.amount is null then 0 else s.amount end) as total_amount とすると以下のように出力されます。

解説の前にポイントを軽く解説しておくと、

test というCTE内で、別々のテーブルusersとtbl_aggの中身をチェックしています。クエリがどんなに大きくなろうと、このCTE testを起点にどこのテーブルにでも自由度高く確認ができるということです。
このテストは、クエリを実行するたびに実行されるので、この時点では問題なかったが後の追加作業でバグが埋め込まれたとしても検知できます。
さらに後日このクエリを読んだときに、どの程度のことまでが保証されているのか一目でわかります。
実行速度の問題もCTE のtestはselect一つ、つまり一行の実行で後にcross joinされます。そのため大規模な計算が行われることはありません。気になる場合は、cross join test をコメントアウトするだけでテスト実行がオンオフできます。

という感じのメリットがあります。

簡単な解説

select
    -- ageにnullがない
    case
        when exists(
            select 1
            from users
            where age is null
            ) then error('ageにnullが存在します')
        else '問題なし'
        end as test1

さてこれはどういう挙動をしているのでしょうか？
分析を普段からやっている人を対象とするので少々くどいと思いますがとりあえず解説していきます。

まず case 文があります。trueなら error(xxx) が発生してエラー文が出る。falseであれば、問題なしが出力されるようになっています。
true/falseを決めるのは exists() 。これはexists内のサブクエリに結果が帰ってこればtrueでそうでなければelseを返します。
今回は、users.ageにnullが存在しないので、サブクエリ

select 1
from users
where age is null

は何も返ってきません。結果として exists は falseを返すためエラーにならないという挙動です。

逆に test2のほうは、existsがtrueを返していたためにエラーが出たという仕組みでした。

解説完了です。

こんな感じであらゆるテーブルに自由度高く確認が行える点が強力だと個人的には思っております。
データ分析経験者であれば遭遇したことのある以下のようなケースにも対応できます。

ユーザをいくつかのセグメントに分けたセグメントのユニークユーザ数の和が、もとの和と一致しない。(セグメントの分け方がMECEでない)
ある単位でユニークだと思っていたが実は違っていて、left join 時に数倍に膨れ上がり想定の数倍の数値が集計された。

などなど枚挙にいとまがないと思います。
こういう複雑なケースにも自由度高く対応できて、実際に何度も僕を救ってくれました。

クエリを書く流れ

クエリを書きはじめるときは、だいたい以下のようなテンプレートになります。

with const as (
...
)

, tbl1 as (
...
)

, tbl2 as (
...
)

, tbl_agg as (
...
)


, test as (
...
)

select *
from tbl_agg
cross join test

constに変数のようなもの、日付とかを書いて、tbl1, tbl2などで集計前のCTEを作成して、tbl_aggで集計、そしてtest内でテストを実行。
これが大まかな流れです。
testに書き込むタイミングはわりと好きなタイミングでやっています。
クエリって書いていると脳汁がドバドバ出て勢いで書けるときってありますよね？あのときのペースを邪魔したくないので、testにコメントだけを書いてあとからチェックするなど好きなタイミングで書くなどをよくします。

, test as (
select
    -- test1
    , case
        when ...
    
    -- tableA.col1にnullは存在しない
    
    -- tableB.hoge_at は 2022年以降のデータのみ存在する

    -- tableAのuuとtableDのuuが一致する
)

と開発過程はこんな感じで進むことは多いです。
確認していないことが可視化されるだけでも大きいです。

基礎編終わり

ここまでで何がしたいのかはわかってもらえたかと思います。
自分で気を張って監視をしなくても、常にクエリがそのクオリティを担保しようと働いてくれます。
クエリが複雑化し大きくなったとしても抑えるべきところは抑えている状態でクエリを書けるので心理的面でも大きな意味が有ると思います。
安心して、より難しいクエリを書くことに集中できます。

僕は結果的に

より高速に
より正確性の有る

クエリが書けるようになりました。

最初は慣れないと思いますが、慣れればこっちも悪くないと実感してもらえると思います。
実際に試してもらえると嬉しいです。
そしてより良い方法があればお互いに共有していければなと願っております。

とまあ一旦ここで締めるのですが、以降で少し発展的な例を起点に、あらゆるテストを行うことができることを主張した後、ある低程度体系化してみようと思います。こういうテストはこういう書き方でできるよ的なことを書いてみます。

発展編

もう一つ違う例を書こうと思います。
ユーザ数は意図した数になっているか？を見ます。

具体的には、 users_over20 のユーザ数と、tbl_agg のユーザ数が一致するか？を確認します。
集計の手順として、users_over20で集計対象とする集団を作って、tbl_aggでユーザそれぞれにtotal_amountの情報を付与という手順を取っています。

users_over20 から最終的な出力まで、ユーザは増えも減りもしない前提ですが、この間にあらゆるjoinを行った結果user_idに対してユニークになっていなかったり、減っていたり増えていたりする事故は往々にしてあります。
今回の簡単なクエリであれば心配ありませんが、大きめのクエリを書いている気持ちになってこのタイプのテストを行います。

以前の２つのテストと大きく違う点は、2つのテーブルの集約値を比較すること にあります。
テストはこうなります。

, test as (
select
    -- test1
    -- test2

    -- users_over20 とユーザ数が一致しません
    , case
        when (select count(user_id) from users_over20) = (select count(distinct user_id) from tbl_agg) then '問題なし'
        else error('users_over20 とユーザ数が一致しません')
        end as test3
)

このように２つのテーブルをまたがって比較することも可能です。
自由度の高さを示す例の一つでしょう。

テストは大きく4種類に分かれると思っています。
軸が２つあり

1行単位の確認なのか？それともテーブルの集約値なのか？の軸
2つのテーブル同士の比較なのか？の軸

です。2つの軸で4象限あり、表にすると以下のような形になります。

	1つのテーブル	2つのテーブル
1行単位	A	B
集約値	C	D

4つの領域にA,B,C,Dと名前を振って例を示していこうと思います。

A: 1行単位 x 1つのテーブル

これは、最初に示した2つの例のことですね。
今回は、監視したい対象が負になっていないかを見ることとします。

, test as (
select
    -- colに負の値が存在しない
    case
        when exists(
            select 1
            from table1
            where col < 0
            ) then error('colに負の値が存在します')
        else '問題なし'
        end as testA
)

B: 1行単位 x 2つのテーブル

2つのテーブルの各行間を比較します。
これは経験上、そこまで多くないパターンです。
table1のcol1とcol2の和がtable2のcol1と一致するという例を書きます。

, test as (
select
    -- table1のcol1とcol2の和がtable2のcol1と一致する
    case
        when exists(
            select 1
            from table1 t1
            left join table2 t2 on t1.user_id=t2.user_id
            where t1.col1+t1.col2 = t2.col1
            ) then error('table1のcol1とcol2の和がtable2のcol1と一致しません')
        else '問題なし'
        end as testB
)

C: 集約値 x 1つのテーブル

これも頻出ですね。
例えば、すでにどこかで出た実績値と一致しているか見るとか、合計値が0になっているはずとかそういうときに使います。

, test as (
select
    -- col1の合計値が 1234 と一致する
    case
        when (select sum(col1) from table1) = 1234 then '問題なし'
        else error('col1の合計値が 1234 と一致しません')
        end as testC
)

D: 集約値 x 2つのテーブル

これは例で出したパターンですね。
セグメント切った前後がMECEか？などのチェックで活躍します。
例えば、ある月の購入額が3000円以上/未満のユーザの数 を数えるときに、あるユーザが3000円未満と3000円以上の購入をしたがために両方のセグメントに属してしまうなどあるあるではないでしょうか？
こういう考慮漏れが起こったときにテストが活躍して自分の問題定義の甘さが露呈します。
この場合は、 ある月の最高購買額が3000円以上/未満のユーザの数 が妥当でしょう。こうやって問がシャープになっていくことはよくあることだと思います。

, test as (
select
    -- table1のユニークなユーザ数とtable2の2つのセグメントのユニークなユーザ数が一致する
    case
        when (select count(distinct user_id) from table1) = (select count(distinct case when seg='A' then user_id else null end)+count(distinct case when seg='B' then user_id else null end) from table2) then '問題なし'
        else error('table1のユニークなユーザ数とtable2の2つのセグメントのユニークなユーザ数が一致しません')
        end as testD
)

やっと終わり

以上です。
これで頻出のケースは網羅できたかなと思います。
このテストを活用する方法は、日進月歩で育ってきました。
これが完成形ではないと思っているので、これからも発展させていくつもりです。
また共有できることがあれば書いちゃいます。

長いのに最後まで読んでいただいありがとうございます。
お礼に沖縄に遊びに来ていいよ券を置いてておきますね。

つ 🎫

ハヤオキスルフクロウ

早起き < 呑み会

より信頼できるクエリを書くために、SQLでもテストを書く