分析用SQLを書くときの思考回路について

本稿では、分析用のSQLを書くときに則っている思考回路について述べて行こうと思います。
この言語化はあまりきちんとされている印象が無いので、自分がそこそこ初めての言語化だと思って頑張ってやってみようと思います。
言い換えれば、私はこういう思考回路でSQLを書きますが、みなさんどうですか、という話でもあります。
あとは、前提として、現代的な分析用の分散エンジンにSQLを投げるときを考えています。それ以外の場合はむしろ非効率になることも多いかもしれません。

0.問題設定

今回の題材は、待てばチケットが復活する無料単話があり、有料で無料単話も買える、そして単行本購買もできる、というマンガサービスとしましょう。

このサービスの企画者から、チケットで無料単話だけ読むユーザが、もし有料で単話を買うようになったらどれくらい売上が伸びるのか教えてほしい、という依頼が来たとします。
これを仮説形式に直すと、

無料でのみ使っていたユーザに有料で単話を読ませるべきである。
具体的には、有料で単話を読んだユーザは、月あたりxxx円程度ARPUが伸び、これは以下の仮定のもと売上換算すると月間yyy円の売上増となる。

みたいな仮説構築になると思います。

1.仮説のために出すべきデータを整理する

1-1.出すデータのアウトプット形式をイメージせよ
これは何かというと、まずデータを取る前に自分で図を描けということです。
今回のお題は様々な交絡が考えられるので、例えば交絡に効きそうな変数による層別化を行って、横軸が層別化用の変数、縦軸が目的変数、色で効果を知りたい変数、みたいな可視化でもいいですし、凝りたい人は傾向スコアとかを使ってもいいと思います。
何も揃えずに、ある時期に使った人と使わなかった人を比べるような比較をやろうとした人は、その試算をもとに、一回実際にキャンペーンをやってみて燃えるところまで経験してみるといいと思います。
まあ、分析設計をここに押し込めてるみたいになりましたが、要はこういうチャートやこういう表があればこの仮説は言えるな、ということをまずイメージします。

1-2.出すデータをKPIツリー形式で整理せよ
次に、上の仮説をKPIツリー形式に整理しましょう。今回だとARPUの差が知りたいので、全体を、ある月に無料単話を読んだユーザとして定め、ここから上の分析で比較したい2グループのKPIを比較するような形のKPIツリー、というものを作ることになるでしょう。
今回のお題はいわゆるアップセルを狙った施策なので、
(対象ユーザ数) x (アップセル率) x (2グループ間のARPU差)が売上増分となるはずです。
これを改めて明瞭にKPIツリー形式で整理しておくとはかどります。

2.uniqueになるべきkeyを決める

さて、上までで分析設計はだいたい終わったと言えます。
ここからは、欲しいデータを出すときの思考回路について綴っていきます。

2-1.DBを作った人の気持ちになる
まず、情報サービスというものがなぜ成立するかを考えましょう。
これは、現実のある一部分を単純化して、その他の要素を捨象していってもなお、潤滑な価値の交換が行われるために、このような商売が成立するのです。
言いたいことは、DBを作るときすでに抽象化すると要素としてどういう単位があって、それは何をもって一意に特定されるべきなのか、という問いを経てきているわけです。
この話を踏まえると、我々も欲しいデータの粒度というのは自ずと決まるわけですし、存在するDBが何でユニークになっているべきかはなんとなく想像がつくわけです。
この思考回路を持っていない人が意外と多いです。
そういう人はdistinct病という悪い病気にかかってしまいます。これは後述します。

2-2.ファクトとディメンジョン
上のような大上段をぶちかましましたが、実際にはある程度セオリーがあります。これがファクトとディメンジョン、あるいはスタースキーマと呼ばれるものです。要はファクトというのはログで、閲覧ログや購買ログをイメージしてください。ディメンジョンというのはメタデータで、今回のお題だと、作者が誰とか、どこの出版社だとか、そういう情報です。
スタースキーマというのは、ログに対してすべてのディメンションテーブルをちゃんとユニークなkeyでぶつけたものを一回作ってから、あるカラムの単位で集約するという操作を行っていけば分析には困りませんよ、というものです。
もっというと、ほとんどのデータマートはこのログに対してディメンジョンをつけたものだと言えるでしょう。この枠に収まらないデータマートも存在するのですが。
そして、単一ファクトの場合は、欲しい単位で集約するだけです。そう考えるとめっちゃ簡単ですね。
今回のお題だと、使うログは購買ログだったり、チケット使用ログだったりになるでしょう。（テーブル設計によるので、チケットと購買を分けてる可能性もあります。ここはサービス依存です。）そして、これらを適切な範囲で指定し、横からディメンジョンをつける必要があるでしょう。

2-3.複数ファクトの場合
ここで面倒なのが、複数ファクトにまたがってデータを出さないといけないパターンです。つまり、購買ログと閲覧ログ両方使いたいという状況です。
この場合のちゃんとしたスッキリしたノウハウはなくて、一回全部のファクトにディメンジョンをつけてから（それぞれのファクトに対してスタースキーマを作ってから）、ファクト間でユニークになるべきキーを決めてそれぞれ集約したあと、左から順にjoinしていってね、というのが現時点での解答だと思います。スタースキーマは役立つ方針ですが、複数ファクトのときはそりゃそうだよな感が強い気もします。それでも、クエリの書き方の方針としてはまあまあでしょう。

3.集約する

さて、あとは集約するだけなのですが、ここでも少しノウハウがあるので、紹介しておきます。

3-1.max-if-pattern、sum-if-pattern、count-distinct-if-patternに帰着せよ
分析用SQLで良く使う3つのパターンについてはここで紹介しておきます。
ちなみに、これは完全に個人的な呼び方です。

max-if-pattern。これは、max(if(condition,1,0))というパターンです。
これは、ある行動をしたかどうか、ある条件を満たしているかどうか、というフラグを生やす場合に使うやり方です。たとえば、今回だとmax(if(price > 0,1,0))とかで購買ログから有料で読んだことがあるかが取れたりするでしょう。（まあ、ほんとはテーブル設計によるけど。）

sum-if-pattern。これは、sum(if(condition,column,0))という式のパターンです。
これは、conditionが入らない普通のsum()のほうが多いかもしれません。

count-distinct-if-pattern。これは、count(distinct if(condition,column,null))というパターンです。
これは、○○あたりの、という指標を出したいときに使います。
例えば、count(distinct if(condition,dt,null))としてみると、これは条件を満たす日辺りの〜という指標の分母が出せることになります。
これもやはり、count(distinct column)のパターンのほうが多いかもしれません。

ファクトはこの3つのどれかでだいたい欲しいものに集約できる、集約出来なかったら何かがおかしい、というのがこの章で言いたいことです。

今回のお題も、ファクトに適切にメタをぶつけ、上の3つを組み合わせてユニークに集計したあと、適切にファクト同士をジョインすれば、分析に必要なデータセットが出来ます。これは需要があれば解答編を書こうと思います。

4.その他のtips

ちょっと物足りないかな〜と思ったので、最後に書くときはこうしたほうが良いですよというtipsを9個用意しました。

1.distinct病
これは非常に罪が重いと思っており、途中で変なdistinctを入れてしまうことです。
定義を考えれば普通にユニークになるべきところでdistinctを入れてはいけません。この人ちゃんと考えてないのかな、という目で見られて、信用低下につながります。

2.union-patternを考慮に入れよ
これはちょっと特殊なケースなのですが、複数のファクトを一つのファクトに無理やりまとめたいときがあります。そういうときは、複数のファクトをunionして一つのファクトにすることも考えましょう。

3.join句に複雑な条件を書くな
これは完全に親父の小言です。
SQLのバグの99%はjoinで発生します。それを考えると、joinは出来るだけ簡単な条件で書くべきなのです。
等号かつand以外の条件句をjoinに書くのはできるだけ避けましょう。

4.where句は迷ったら最小の影響範囲にせよ
これは結構難しいテーマです。
where句もミスは仕込みやすいので、迷ったら影響範囲が小さくなるようにこまめにwhereを書くのが私は今の所の正解だと思っています。

5.window関数はとりあえずleadとlagとrow_numberだけでよい
これは、上記のとおりです。
window関数が苦手な人でも、leadとlagとrow_numberは使えるべきです。
理由としては、SQLという言語は基本的には縦の方向を考えないので、次の〜前の〜何回目の〜という概念をとりたい場合はこれらを使う必要がどうしてもあります。
これら以外は、SQLの外でなんとかするか、やや手間ですが、サマリのテーブルを作る段階で別に集約するなどで事足ります。もちろん、使えたほうがいいんですけどね。

6.最後の数字を見よ
これも、たまにサボってしまうのですが。
スタースキーマでの集約がうまく行っていれば、そして複数ファクトの際も重複が無いようにちゃんとキーを設定できていれば、何かの数字が増えたり減ったりすることはwhere句でしか絶対に起きません。そのため、ちゃんとファクトのテーブルの行数を数えるだとか、そういうチェックは分析後には必ずやるべきです。このチェックを真面目にやれるかどうかで、あなたの分析への信頼度は倍どころか三倍変わるでしょう。
私はこれをやらなくて前の上司に叱られてから真剣にやるようになりました。叱ってくださった前の上司に感謝しています。

7.asをつけなさい
これは完全に宗教ですが、asをつけない人が行く地獄があると言われており、この地獄では鬼が書いたクエリに全部asを足してはまた鬼がasのないクエリを持ってくるという作業を延々とするそうです。怖いですね。絶対にasはつけましょう。

8.サブクエリは使わないで
サブクエリ使われると頭がバグるんで使わないでください。お願いします。with句を上から下まで眺めていく仮定で処理のDAGを紙なり脳内でなり作れるので、この点でwith句で何でも書くほうがワークフローとかに慣れてる人は明らかに書きやすいと思います。

9.left joinとright joinを混ぜるな
left joinとright joinを混ぜたクエリを書いてはいけません。論理的に等価なら必ず一つのやり方に統一すべきだと私は思っています。それ以上に、leftとrightを一つのクエリに混ぜてるときは多分自分に酔っています。人間はシラフでleftとrightを一つのクエリに混ぜることは無いです。

いかがでしたでしょうか。
意外に奥の深い、分析用SQLの世界についてもっと議論が深まればと思います。