はじめに
本稿では分析用クエリをスラスラ書けるようになるまでの勉強方法や書き方のコツをまとめてみました。具体的には、自分がクエリを書けるようになるまでに利用した教材と、普段クエリを書く際に意識していることを言語化しています。
想定読者として、SQLをガンガン書く予定の新卒のデータアナリスト/データサイエンティストを想定しています。
勉強方法
基礎の基礎をサッと座学で勉強してから、実践教材で実際にクエリを書くのが望ましいです。
実務で使える分析クエリを書けるようになるためには、実務経験を積むのが一番良いですが、だからといって座学を御座なりにして良いというわけではありません。SQLに自信がない人は、一度基礎に立ち返って文法の理解度を確認した方が良いと思います。
書籍
SQL 第2版: ゼロからはじめるデータベース操作
前提として、SQLに関する書籍の多くがデータベース運用/構築に関する書籍がほとんどであり、分析に特化した書籍は多くありません。
selectとは？whereとは？というような基本的な構文などを理解せずにクエリを書くことはできないので、SQLの基礎の基礎を書籍 or インターネットで勉強すると良いでしょう。
自分はこの本を最初に読みました。内容が非常に軽くてオススメですが、有名な本であれば特に差がつかないと思うので、本屋で立ち読みして自分に合いそうなものを探すのが良いと思います。
ビッグデータ分析・活用のためのSQLレシピ
分析に特化したSQLの書籍として辞書的に使うのがオススメです。事例集という形なので、この本を最初から最後までじっくり読んで勉強するのはあまりオススメできません。
リーダブルコード ―より良いコードを書くためのシンプルで実践的なテクニック
分析用クエリもプログラミングコードであるため、適切な変数名や適切な粒度でのコメントを書ける方が望ましいです。実務でコーディングをする人間の通過儀礼として一読すべきと思います。
実践教材
書籍を読むだけでクエリを書けるようにはならないので、実際にクエリを書いて練習する必要があります。
環境構築含め簡単に実施できるものは以下の2つです。こういうのは初心者ほど面倒がって解かなかったりするものですが、SQLに自信がない人は絶対に一度全て解いてから実務に入るべきです。
データサイエンス100本ノック
データサイエンス協会が提供しているもの。Python/R/SQL編が存在しており、SQL編を解くと良い。
ここで利用されているデータセットは小売店の購買ログがテーマとなっています。携わる事業に依りますが、ToC向け事業の分析業務で扱うデータとテーブル構造がかなり似ていると思います。実務に入る前に絶対に一度は解いておくべきです。
SQLZoo
データサイエンス100本ノックはDockerやGoogle Colab上で環境構築する手間が必要ですが、こちらはブラウザ上ですぐにクエリを実行することができるので、気軽に勉強することができます。
データ内容は業務との関連性は低いものの、文法の理解があやふやだと解けない歯ごたえある問題が多いです。これを全て解くことができれば基本的な文法は抑えたことになると思うので、やはりこちらも実務に入る前に解いておくべきです。
その他
分析用クエリを書く上で役に立つ記事をまとめました。
SELECT文の評価順序の話
一般的にSQLを勉強する際には「select → where → group by → ...」というように、個々の構文を順番に勉強する形式になっていることが多いと思います。
これに影響されて、自分でクエリを書く際にも書籍で習った順序もしくは何の考えなしになんとなく書くのはいけません。クエリの各句がどの順番で評価され実行されるのかを理解していないと、いくら自分のクエリを見直しても正しい集計を行うことはできません。
余談ですが、自分はこのQiitaの記事を読んだことでクエリの理解が進んで飛躍的にクエリを書けるようになった過去があるので、色々な人にこの記事をオススメしています。
[Pythonコーディング規約]PEP8を読み解く
データ分析の際はSQLで集計 → Pythonで可視化の流れが多いと思うので、Pythonを意識した変数名をつけられると良いと考えています。これについてはPEP8やGoogleのPythonコーディング規約が参考になります。
実践的な分析クエリの書き方
以上の内容を勉強して頂いたうえで、実践で使えるクエリを書くためのコツをまとめます。
クエリを書く際に意識していること
具体の書き方に入る前に、お気持ち的な話を書いておきます。
クエリの1行1行が意思決定を左右する
データ分析をなぜ行うのか。意思決定の方針を示すためです。これは分析結果によって意思決定が変わりうるということであり、大袈裟に言えば、クエリの1行1行が意思決定を決めているとも言えるでしょう。where句で指定する条件1つで数値が変わり、意思決定がひっくり返ることもありえます。
意思決定を左右しうるということでクエリを書くのは非常に重要な作業なのですが、残念なことに仮に集計ミスをしたとしてもエディタがエラーを出してくれることはありません。ミスしないよう注意してコードを書いていくしかないのです。集計ミスをした時に、ドキュメントがなかったからログの特殊な入り方に気付けなかった、とか、初めて使うテーブルだからよく分からなかった、とか、ChatGPTがこう言ったから、などの言い訳は通用しないでしょう。（そもそも変なデータの持ち方をしているテーブルが存在すること自体良くないですが、それはまた別の問題なので一旦置いておきます）
自分の出した数値に責任を持つためにも、泥臭くデータを細かく確認し、1行1行気持ちを込めてクエリを書くべきと考えています。
少し思想強めで書いてしまいましたが、少なくとも集計に利用したテーブルのログの入り方、データ構造の理解があやふやなままコードを書くのは危険なので、ちゃんと他人に説明できるくらいにはデータのことを理解してコードを書きましょうよ、という話です。
再利用しやすいようwith句を適切な粒度で分ける
分析用クエリを書くとは、with句を適切に連ねる作業だと自分は考えます。with句を制するものは分析用クエリを制するのです。
分析用クエリは再利用することが非常に多いです。分析対象となる対象ユーザーや期間、KPIは課題によって都度変化するものの、大枠の分析設計や分析クエリはある程度パターンが決まっており、都度大きく変わるわけではありません。
再利用しやすいコードとは、with句が適度な粒度で疎になっているコードと考えています。これが成立していれば、with句の中身をちょちょいと変えるだけで様々な分析タスクに対応することができます。
読みやすいようにコメントを適切な粒度で書く
上と被るところもありますが、可読性を意識したコーディングも重要と考えています。
分析用クエリはプロダクトコードと異なり、複数人で長期間メンテナンスをするということはないため、雑に書いて良いと思われるかもしれません。
しかし、上述のようにクエリは再利用する機会が非常に多く、そのクエリを最も再利用するのは未来の自分です。どうせ自分しか読まないから丁寧に書かなくても分かるでしょ、と雑に書いたクエリも、一週間後に見直すと何も覚えていないものなんですよね。
他人にレビュー等でコードを読んでもらう時は言わずもがな、未来の自分が読み直した時に困らないよう、適度な粒度でコメントを残し、ある程度統一されたフォーマットでクエリを書くことは重要と考えています。
自分がコメントを残すタイミングは2つあり、①普段は行わないようなイレギュラーな集計をしている時、②joinやwhereなどレコード数が変化する時です。自分がコメントを残す際には、視認性が良い "▼" をよく使っています。
where
date between ...
and ...
and ...
-- ▼ hogehogeなのでfugafugaする
and hoge = 'fuga'
問題設定
ここでは具体例として、ECサイトにおけるクーポン施策の効果測定を考えます。
ロイヤリティが低いユーザーの購買金額の増加を促すことを目的として、クーポンが配布されました。クーポンの利用有無による売上差異を、性別や年齢ごとに調べたいとします。
（なおクーポン利用有無で単純なKPI比較ではセレクションバイアスが含まれる可能性が高いため望ましくないですが、あくまで例題なのでこの点はスルーしていただけると幸いです）
クエリの書き方
分析クエリの99%は以下の流れを脳死でやっていけば済むと考えています。
- スタースキーマを決める。
- 集計単位となるキー（ユーザー・商品・セッションなど）について、分析対象のデータを引っ張ってくるwith句を作る
- スタースキーマを構成する各カラムを集計するwith句を作成する。
- 2.で作成したwith句に対して、3.で作成したwith句を(left or inner) joinしていくことでスタースキーマを作成する。
- 4.で作成したスタースキーマに対して集計を行う。
1.スタースキーマを決める
スタースキーマなんてカッコいい言葉を使いましたが、要は今回の分析の集計単位となるキーに関する情報、集計単位に関する購買ログや行動ログ、が1つにまとまったテーブルを考える、ということです。
これを作っておけば、ミスがあった際の見直しやN=1調査が簡単になるので、よほど簡単な集計でない限りは作っておくべきである。
今回だと以下のようなテーブルをイメージ出来ると良いでしょう。
| user_id | sex | age | used_coupon | sales |
|---|---|---|---|---|
| hoge | 1 | 28 | 1 | 720 |
| fuga | 0 | 32 | 0 | 400 |
| foo | 0 | 19 | 1 | 2400 |
2.集計単位となるキー（ユーザー・商品・セッションなど）について、分析対象のデータを引っ張ってくるwith句を作る
今回の例では、クーポン配布対象のユーザー、すなわちロイヤリティが低いユーザーを引っ張ってくるwith句を作ることになります。より具体的に言えば、user_id、sex、ageのカラムを用意するということです。
ここで、検算用にレコード数をカウントしておくと良いです。正しい集計が行われていれば、最終的な集計値から計算されるレコード数と、ここで計算されるレコード数が一致するからです。これに限らずですが、最終的に計算されたレコード数や合計値がズレているかどうかのチェックはデータの信頼性担保のためにやるのが望ましいです。
3.スタースキーマを構成する各カラムを集計するwith句を作成する。
購買ログ、行動ログ等からそれぞれ必要な情報を集計します。
今回であれば、used_coupon、salesのカラムを用意することになります。
4.2.で作成したwith句に対して、3.で作成したwith句を(left or inner) joinしていくことでスタースキーマを作成する。
ここまで準備してきたwith句をひたすらjoinしていきます。この書き方であれば、joinが一方向になるので見通しが良い上に、with句がそれぞれ疎結合になっているので、ミスの切り分けもしやすくなります。
5.4.で作成したスタースキーマに対して集計を行う。
必要に応じて集計を行います。性別ごとにクーポン利用有無の売上差を見たければ性別とクーポン利用有無でgroupbyします。
これらの流れを踏まえると、以下のようなクエリが出来上がります。
繰り返しになりますが、分析クエリの99%がこのパターンだと思っているので、この流れだけ覚えておけば問題ありません。
with target_user_log as (
select
user_id
, sex
, age
from
user_log
where
user_segment = 'low'
)
, coupon_use_log as (
select
user_id
, 1 as used_coupon
from
coupon_log
where
date between 'yyyy-mm-dd' and 'yyyy-mm-dd'
and ...
)
, purchase_log as (
select
user_id
, sum(quantity * unit_price) as sales
from
purchase_log
where
date between 'yyyy-mm-dd' and 'yyyy-mm-dd'
group by
user_id
)
, result as (
select
t.user_id
, sex
, age
, coalesce(used_coupon, 0) as used_coupon
, coalesce(sales, 0) as sales
from
target_user_log as t
left join
coupon_use_log as c
on
t.user_id = c.user_id
left join
purchase_log as p
on
t.user_id = p,user_id
)
select
is_coupon_used
, sex
, avg(sales) as sales_per_uu
from
result
group by
is_coupon_used
, sex
order by
is_coupon_used
, sex
;
その他小言
以下は完全に小言ですので参考程度にしていただければと思います。
distinctを無闇につけない
完全に元上司の受け売りですが、distinctを"無闇に"つけるのはやめましょう。distinctに限らないですが、何の考えもなしに不自然なデータ除外処理を行うのは、分析に利用したテーブルについて無知を晒すようなものです。自分の集計した数値の説明責任を放棄するようなものです。
テーブル側（ログの入り方）に問題があってdistinctをつけるべきなのか、そもそも自分の集計に間違いがあってdistinctを使わなければいけなくなっているのか、この切り分けをせず脳死でdistinctをつけるのは良くないでしょう。ログの入り方に問題があれば、データ基盤を管理しているチームやログの仕込みを担当するチームに連絡をし、今後適切な集計ができるよう調整できると良いでしょう。
with句の1段の入れ子は積極的に使おう
自分も最近知ったのですが、with句は入れ子で書くことができます。
例えば、条件Aに合致するが条件Bには合致しないユーザーを対象に分析したいというケースを考えます。それぞれ独立してwith句を書くよりは入れ子を使ったほうが可読性や再利用性が高まると思っています。
適切な粒度で区切った方がミスした時の切り分けがやりやすくなるので、必要であれば積極的に入れ子を使いましょう。とはいえ2, 3段も入れ子になると読みづらいですし、2, 3段必要になるようなクエリを書く機会に出会ったことがありません。基本的には1段階までと考えるのが良いでしょう。
with target_user_log as (
with condition_A as (
select
user_id
from
hoge
)
, condition_B as (
select
user_id
from
fuga
)
select
a.user_id
from
condition_A as cA
left join
condition_B as cB
on
cA.user_id = cB.user_id
where
cB.user_id is null
)
1つのwith句で色々な値を集計しすぎない
クエリを書くことに慣れてきて自信が出てくると、「コーディング量を可能な限り減らすぞ！データへのアクセスを可能な限り減らすぞ！」となりがちです。
よほどクエリのコーディングに自信があるなら問題ないですが、1つのwith句で色々な集計を済ませようとすると、かえって汎用性が落ちますし、集計ミスする可能性が増えます。特に1つのwith句で複数期間の集計を行う際は注意が必要です。
ある一定それなりに正しいクエリが書けていれば、それ以上カリカリにチューニングしたとしても実行時間や実行コストに大きく差がつくことはないです。可読性や再利用性を優先して、1つのwith句に役割を沢山持たせないようにコードを書くべきです。
レコード数が変化するタイミングは意識してコメントを残しておくべき
集計ミスが発生するのは、joinやwhereなど、レコード数が変化しうるタイミングが多いです。実務のクエリでは特にwhereの条件指定は長くなりがちで、クエリを読み直す時に「ここはいつものwhereで良いんだな」とスルーしてしまうこともあります。こういう時に気がつけるよう、積極的にコメントを書いておくと良いと考えています。
おわりに
思いのほか長くなってしまいました。
偉そうにつらつらと書いてきましたが、分析クエリは意思決定を左右するからちゃんと勉強して責任感持って書こうね、という話です。
今よりもLLMが発達してくれれば、自然言語でデータの集計が可能になったり、クエリから集計ミスの検知を行ってくれるようになるかもしれません。そうした世界が明日にでも来てくれれば嬉しいのですが、少なくとも今後1年でそんな時代が来ると思えなかったのでこの記事を書きました。
分析クエリのコツは言語化が難しいところだと思うので、皆さんが普段気をつけていることなどあれば、コメント頂きたいです。よろしくお願いします🙏
参考記事
Discussion