読みやすく、再利用しやすいSQL分析クエリを書くコツ
こんにちは、Wantedlyでデータサイエンティストをしている樋口です!
自分は過去、分析のためにSQLを書いているとき、以下のようなミスをしていました。
- メール開封率を集計したら何故か100%を超えてしまった。でもどこが原因かがわからない
- メール開封率を集計したのち、クリック率も追加で依頼を受けたが、再利用できるクエリになっておらず、一から書き直した
- クエリのレビューを依頼したら、複雑すぎると言われてしまった
こういった経験がある方は自分以外にもいるのではないでしょうか...! SQLは非常に自由度が高く便利である反面、書き方が個人に委ねられ、複雑・難解になりやすいと感じています。
そこで本記事では、自分が実務で学んだ、読みやすく、再利用しやすいSQLクエリの書くコツを紹介したいと思います。個人の経験によるものなので、より良い書き方・考え方もあるかもしれないです。ご了承ください🙏
読みやすく、再利用しやすいクエリの意義
自分は主に以下の3つの理由から、読みやすく再利用しやすいクエリを書く必要があると思っています。
間違った集計は、価値を生まない
データの集計で誤った結果をしてしまうと、そのまま誤った意思決定を下し、むしろ損失を生み出してしまう恐れがあります。
集計で価値を生み出すには間違っていないことが前提にあるため、間違いを生みづらい、気づきやすいクエリを書く技能が必要だと考えます。
過去の集計を再利用したいケースは多い
会社やチームは基本的に共通目標に向かうため、関心事も近くなると思います。そのため、何かを集計したい場合、過去に似た集計をしていたというケースは意外とあります。
社内にクエリが再利用しやすい形で溜まっていると、2回目以降の集計が楽になります。
クエリのレビューは難しい
SQLクエリのレビューは以下の点でレビューが難しいです。
- テストが書き辛い
- テストデータの用意が難しい
- テストツールやIDEがそこまで発達していない
- コーディングの自由度が高い
- 型がない
- 分割が難しいので、1ファイル(クエリ)が難解で長大になりやすい
読みやすいクエリを書けるとレビュワーの負担が減り、チーム全体の業務効率が上げられるはずです。
前提・問題設定
この記事では、”A/Bテストで送っているメールのユーザ性別ごとの(送信,開封,コンテンツクリック)数を集計する”。という擬似ケースを元に紹介します。また、記事内のクエリはBigQueryを想定しています。
再利用しやすいクエリ
スタースキーマを構築する
データ集計する際、「〇〇別の▲▲数」と、ある切り口で集約した指標を集計することがほとんどだと思います。この時の〇〇をディメンション、▲▲をメジャーと呼びます。
集計する時は、必要なディメンション・メジャー洗い出し、これらをカラムにもった、スタースキーマと呼ばれるテーブルを構築すると再利用しやすいクエリを組み立てられます。
例えば、”A/Bテストで送っているメールの結果を男女別に集計する”場合のディメンション、メジャーを考えてみます。
- ディメンション
- A/Bグループ(対照群 or 実験群)
- 男女
- 送信日
- メジャー
- 送信(数/ユーザ数)
- 開封(数/ユーザ数)
- メール内コンテンツクリック(数/ユーザ数)
そして、それらをカラムにもった、スタースキーマテーブルは以下のように構築できます。
| mail_uuid | user_id | send_at | open_at | click_at | ab_group | gender |
|---|---|---|---|---|---|---|
| m1 | u1 | t1 | t5 | t9 | controll | male |
| m2 | u2 | t2 | t6 | null | controll | female |
| m3 | u3 | t3 | null | null | treatment | male |
| m4 | u3 | t4 | t8 | null | treatment | male |
このように作っておけば様々な切り口の指標を算出できます。
- 全期間の男女、abグループ別のメール開封数
- 1週間あたりのabグループ別のメールクリック率
- 1日あたりのユーザへのメール送信数
- etc.
例えば、「全期間の男女、abグループ別のメール開封数」は↓のように書けます
select
ab_group,
gender,
count(distinct if(open_at is not null, mail_uuid, null)) / count(distinct mail_uuid) as open_ratio
from star_schema
group by ab_group, gender
集計前にチームでどんな指標が見たいかはすり合わせるべきですが、結果を見てから別の指標を知りたくなる場合もあります。そのようなケースでは、スタースキーマテーブルを作っておくと良いでしょう。
またスタースキーマテーブルを作っておけば、すぐに結果のデバッグもできます。(あるユーザごとにレコードがユニークになっているか、など)
スタースキーマ・メジャー・ディメンションの考え方については以下の記事が詳しいです。
読みやすいクエリ
早くnull, 重複を除去する
クエリのバグの多くはjoinに使うkeyに意図しないnullや重複が生じることで起きます。
そのため不必要なnull,重複はクエリの早い段階で除去すると良いでしょう。こうすると、後段のクエリでは意識せずにすみます。
また、これはクエリに閉じた話ではないです。もしログテーブルで意図しない欠損があれば、ログ生産者にエスカレーションして、今後も同様の処理をしなくて済むように、仕様変更を依頼するとよりbetterかと思います💪
テーブルとして切り出せるようなサブクエリを作る
テーブルとして切り出しても違和感のない単位でサブクエリを構築することで高凝集・疎結合な実装が保たれます。
高凝集・疎結合であるコードは影響範囲が閉じられる上、理解するために読む範囲が狭められます。これはSQLに置いても同様です。
例えば、"A/Bテストして送っているメールが(開封,クリック) ごと、(controll, treatment)ごとに別のテーブルに格納されている"というケースを考えてみます。(そんなケースは稀だと思いますが...)
この時、トップレベルのサブクエリに、開封、クリックのログを格納し、その内部のサブクエリで、treatment,controllのレコードをまとめるようにすると良さそうです。
with
open_logs as (
with raw_controll_open_logs as (
...
),
raw_treatment_open_logs as (
...
)
select * from raw_controll_open_logs
union all
select * from raw_treatment_open_logs
),
click_logs as (
with raw_controll_click_logs as (
...
),
with raw_treatment_click_logs as (
...
)
select * from raw_controll_click_logs
union all
select * from raw_treatment_click_logs
)
ここで、クリック・開封も一つのサブクエリにまとめることもできますが、2段階以上ネストが続くと自分は読みづらく感じるので、トップレベルをクリック・開封としています。
サブクエリ間のスキーマを揃える
スタースキーマテーブルを構築するには、ほとんどのケースで複数のサブテーブルが必要になります。それらのサブクエリに存在するレコードやスキーマをなるべく揃えると、join句がシンプルになり、読みやすくなります。
例えば、"集計対象のメールログが(送信, 開封, クリック) ごとにテーブルが分かれていて、それぞれ必要な絞り込みが違う"ケースを考えてみます。このとき、スキーマを揃えたサブクエリを作り、最後にjoinするとスッキリするはずです。
with send_logs as (
select
mail_uuid,
user_id,
ab_group,
gender,
send_at,
from raw_send_logs
where ...
),
open_logs as (
select
mail_uuid,
user_id,
ab_group,
gender,
open_at,
from raw_open_logs
where ...
),
click_logs as (
select
mail_uuid,
user_id,
ab_group,
gender,
click_at,
from raw_click_logs
where ...
),
mail_logs as (
select
sl.mail_uuid,
sl.user_id,
sl.ab_group,
sl.gender,
ol.open_at,
cl.click_at
from send_logs as sl
left join open_logs as ol using(mail_uuid)
left join click_logs as cl using(mail_uuid)
)
select
count(distinct mail_uuid) as send_cnt,
count(distinct click_at) as click_cnt,
from mail_id
集約keyを持つテーブルに対してleft joinする
集約keyを持つテーブル(≒スタースキーマ)を作り、他のサブクエリをleft joinしていくと、joinが1方向になり、見通しが良いクエリができます。
企業での集計ではファネル分析が多いと思います。このとき、ファネルごとにサブクエリを切り出し、最も広い(前の)ファネルに対してそれより狭い(奥の)ファネルをleft joinしていくと綺麗に書けると思います。
with send_logs as (
...
),
open_logs as (
...
),
click_logs as (
...
),
mail_logs as (
select
sl.mail_uuid,
sl.user_id,
sl.ab_group,
sl.gender,
ol.open_at,
cl.click_at
from send_logs as sl
left join open_logs as ol using(mail_uuid)
left join click_logs as cl using(mail_uuid)
)
中身がわかる変数名をつける
テーブルやカラムには中身がすぐにわかるような、変数名をつけると読みやすくなります。具体的には、社内で一般的に使われている用語かつ、型がわかるような変数名だと良いです。
(例)
- カラム
- 何らかのkey: hoge_id(ex. user_id)
- 何らかの数値: hoge_cnt(ex. click_cnt)
- 何らかのフラグ: is_hoge(ex. is_click)
- 何らかのカテゴリ変数: hoge_type(ex. mail_type)
- テーブル
- hoge_users
- hoge_logs
from文で複雑なサブクエリを書かない
sqlではselect hoge from (select ... from ... where...)のように、from文の中にもサブクエリを書くことができます。
ただし、from文にサブクエリがあると、上から下にクエリを追えず、読みづらくなります。
複雑な条件になる場合はwith句に切り出しましょう。自分は目安として、サブクエリが4行以上になる場合はwith句に切り出します。
フォーマットを統一する
クエリ内で書き方がバラバラだと読みづらいので、フォーマットを統一して書きましょう。正直好みですが、自分は以下のような自分ルールを作って運用してます。
- 予約語も全て小文字
- shift押すのが面倒 + 全ての予約語を把握できないため
- カンマは変数の後
- 変数名は全てスネークケース
- テーブル名は全て複数形
- 8文字以上のテーブルにはスネークケースの先頭の小文字でエイリアスを貼る(click_logs → cl)
また、フォーマットを統一するためには、formatterを利用するという手もあります。
実例
最後に実際にこれらのtipsを意識して書いた、クエリを紹介します。
問題設定
"A/Bテストしているメールをユーザに送信し、どちらの方が開封率・クリック率が高いかを男女別に集計する”ケースを考えます。
問題設計を簡単にするため、メールごとにmail_uuidが振られ、mail_uuidごとに初回の送信・開封・クリックのみログが残るとします。またdatetime型のカラムの値は全てuniqueとします。
ただし、controll群とtreatment群のメールログではテーブルのスキーマが違い、またcontroll群のsend_logのみ意図せずmail_uuidに重複が存在しています😱
※ この記事ために作った架空の問題設計、テーブル構成です。
テーブル形式
上記の問題に対応したテーブル形式とテーブル名を以下に示します。
controllのメールログはユーザのアクションごとにテーブルが分かれており、treatmentは一つのテーブルにユーザのアクションごとにレコードが生成されています。
controll_mail_send_logs(mail_uuidに重複あり)
| mail_uuid | user_id | created_at | gender |
|---|---|---|---|
| m1 | u1 | t1 | male |
| m1 | u1 | t2 | male |
controll_mail_open_logs
| mail_uuid | user_id | created_at | gender |
|---|---|---|---|
| m1 | u1 | t1 | male |
controll_mail_click_logs
| mail_uuid | user_id | created_at | gender |
|---|---|---|---|
| m1 | u1 | t1 | male |
treatment_mail_logs
| mail_uuid | action_type | created_at | user_id | gender |
|---|---|---|---|---|
| m1 | send | t1 | u1 | male |
| m1 | open | t2 | u1 | male |
| m1 | click | t3 | u1 | male |
考え方
男女・abグループごとに開封率・クリック率を見るために、以下のようなスタースキーマの構築を目指します。
| mail_uuid | user_id | send_at | open_at | click_at | mail_type | gender |
|---|---|---|---|---|---|---|
| m1 | u1 | t1 | t5 | t9 | controll | male |
| m2 | u2 | t2 | t6 | null | controll | female |
| m3 | u3 | t3 | null | null | treatment | male |
| m4 | u3 | t4 | t8 | null | treatment | male |
そのためにまず、abグループごとに、メールの(送信・開封・クリック)をmail_uuidごとに集約したサブクエリを作ります。そして、これらをunion allでまとめたものをスタースキーマにします。
集計クエリ
上記の考え方に沿った集約クエリは以下のようになります。コメントと紹介したtipsと対応づけています。
with controll_mail_logs as ( -- 意味のある単位にサブクエリを区切る
with distinct_controll_mail_send_logs as ( -- 早い段階でkeyのnull, 重複を処理する
select
mail_uuid,
any_value(user_id) as user_id,
any_value(gender) as gender,
min(created_at) as created_at,
from controll_mail_send_logs
group by mail
)
select
sl.mail_uuid, -- サブクエリ間のスキーマを揃える
sl.user_id, -- 中身がわかる変数名をつける
sl.gender,
sl.created_at as send_at,
ol.created_at as open_at,
cl.created_at as click_at,
"controll" as mail_type -- のちのunion all用の識別子
from distinct_controll_mail_send_logs as sl -- 集約したいkeyに対して他のテーブルをleft joinする
left join controll_mail_open_logs as ol using(mail_uuid)
left join controll_mail_click_logs as cl using(mail_uuid)
),
treatment_mail_logs as (
select
mail_uuid, -- サブクエリ間のスキーマを揃える
any_value(user_id) as user_id,
any_value(gender) as gender,
min(case when action_type = "send" then created_at else null end) as send_at,
min(case when action_type = "open" then created_at else null end) as open_at,
min(case when action_type = "click" then created_at else null end) as click_at,
"treatment" as mail_type -- のちのunion all用の識別子
from treatment_mail_logs
group by mail_uuid
),
mail_logs as ( --スタースキーマの構築
select * from controll_mail_logs
union all
select * from treatment_mail_logs
)
select
mail_type,
gender,
count(distinct open_at) / count(distinct send_at) as open_ratio,
count(distinct click_at) / count(distinct open_at) as click_ratio,
from mail_logs
group by mail_type, gender
order by mail_type, gender
まとめ
本記事では、読みやすく、再利用しやすいSQLクエリの書き方について紹介しました。全ての分析に当てはまるものでなかったり、うまく抽象化できていない部分もあるかもです。一例として捉えていただければ幸いです🙏
本記事ではクエリの書き方にフォーカスしましたが、データ分析者が複雑なクエリを書けるようにするだけでなく、複雑なクエリを書かずに済むようにするに工夫する必要もあると思います。
データ生産者と分析者では、データの用途や見え方が違って見えるはずです。分析者は積極的に生産者やデータエンジニアに対してフィードバックし、企業の中でより使いやすく綺麗なデータを貯めていけるようにすると良いと思います💪
今後もデータ分析や推薦システムなどについて記事を書こうと思っているので、よろしければいいねやTwitterのフォローいただけると助かります!
参考資料
この記事を書くにあたって参考にした記事や書籍を紹介します。
分析クエリを書くときの思考回路について紹介しています。去年大変お世話になりましたし、本記事の内容もこの記事からインスパイアを受けています。as地獄には行きたくないものです。😇
Bigqueryの便利な関数がまとまっています。公式ドキュメントはGoogle特有の言葉遣いでちょっと難しく感じる時もあるので、助かります
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
SQLの正しい書き方・考え方を紹介した本。前半はモダンなSQL機能を駆使したクエリの書き方、後半はリレーショナルデータベースの開発の歴史から、SQL を作った人が何を考えて現在の形にしたのかというバックグラウンドについて書かれています。
SQLの構文が集合論や述語論理の分野を交えて紹介されており、読む前よりどういう時にどんな構文を利用すれば良いか、区別をつけられるようになりました。
ビッグデータ分析・活用のためのSQLレシピ
Web企業における分析クエリの事例と、それに紐づくクエリが紹介されています。網羅性が高く、コホート分析やWeb内の回遊の分析など、かなり複雑な例も載っていて、参考になります。
良いコード/悪いコードで学ぶ設計入門 ―保守しやすい 成長し続けるコードの書き方
良いコード・悪いコードの事例付きで、保守しやすいコードが紹介されています。この本はSQLで書かれているものではありませんが、高凝集・疎結合など、言語によらない保守性の高いコードの原則を学ぶことができます。
プリンシプル オブ プログラミング3年目までに身につけたい一生役立つ101の原理原則
良いコード/悪いコード本と同じく、コードの原理・原則が紹介されています。こちらにはコードはあまり出てこなく、より原則が抽象化してまとまっています。
Discussion