ワンランク上のSQLを書くためのポイント3つ

この記事はNuco Advent Calendar 2022の17日目の記事です。

ワンランク上のSQLとは？

1年近く、データ分析に関わる業務に携わっってきた中で、良いSQL、いまいちなSQLをいろいろ見てきました。

自分が書くSQLも、最初の頃は目も当てられないSQLも書いてきました。そんな中で、こんなことを意識していくと、より良いSQLになるのでは？というポイントをまとめていきます。

とりあえずSQLの文法は一通り勉強して、取得したいデータをとってくるSQLをかけるようになったぞ。という人に向けたものなので、本当に基礎的な文法は解説していません。

ワンランク上のSQLを書くためのポイントは、
・読みやすい
・再利用しやすい
・処理が早い
の３つを押さえられているかどうかだと感じています。

なお、本記事は特にデータ分析業務に使うSQLという観点でのポイントを紹介していきます。

可読性が高いメリット

間違いにくくなる/デバックが容易になる

エラーが出てくれれば間違っていることは容易にわかるのですが、取りたかった値とは違うにも関わらず値が取れてしまうことがあります。

そのため、
「出てきた数字が妥当かどうか(出てきた数字の桁が事前に予想されたものと合っているか)」
を常に確認しながら進める必要があります。

しかし、たまたま大まかな数字感は同じだったり、細かい条件の絞り方の違いなどは、気付くことが困難です。

算出したデータをもとに今後の経営判断がされることもあるので、こういったミスは致命的な影響を与えかねません。

人為的なケアレスミスを減らすためにも、読みやすいSQLを書くことが大切になってきます。

再利用性が高いメリット

将来の自分にとって便利

人間は忘れる動物です。
SQLに限らずですが、自分で書いたコードであっても、1ヶ月もすればまるで他人が書いたもののように感じることがあります。

データ分析業務においては、思った以上に過去の分析と似た分析をする機会は多いです。

「毎月実施する施策の分析」をすることもありますし、他の分析であっても「ユーザーのセグメント分け」の方法は同じだったりします。そういった場面で、読みやすいSQLを書いておくと、非常に自分自身を助けることとなります。
　

他の人が見たときに理解/利用しやすい

将来の自分にとって便利であれば、他人にとっても便利です。

大きな組織になればなるほど、他の人も同じような分析に携わる可能性は大きくなります。

前任者から業務の引き継いだり、逆に後任の人に仕事を引き渡すことも出てくるかと思います。その時に、解読するのに何時間もかかるような、もはや解読不能なSQLになってしまっていると、完全な二度手間になってしまいます。

また、SQLを作成した人が退職してしまった後も、誰も作った意図を理解していないクエリを、毎月律儀に叩き続けているといったカオスな場面に出くわすことも少なくありません。

可読性も高く、再利用性も高いクエリは、組織にとっても貴重な資産となります。

処理時間が短いメリット

時間の節約になる

自作のアプリケーションで練習している間はそれほどきにする必要は無いですが、いざ業務でデータ分析をしていると、サービスによってはとんでもない量のデータと格闘することになります。

1日分のレコードだけで数千万単位のレコードが溜まっている。というようなプロジェクトにおいて、処理時間を考えずに突っ込むと、1日かけて1つのSQLしか実行できずに終わったなんてこともあります。

自分の叩こうとしているクエリが、どれくらいの実行時間になりそうなものなのか、ここを理解しておくことで、PDCAのサイクルを回すスピードが段違いになります。

お金の節約になる

「時間は気合でカバーします！」という人がいるかもしれませんが、処理が短いことはお金の節約にもなります。

サービスによって料金体系は違うので、全てのプロジェクトで当てはまるわけではありませんが、例えばGoogleが提供しているBigQueryはSQLの処理に対しての従量課金が発生します。

つまり毎回取る必要のない情報までとってきていると、その分のお金がどんどん積み重なっていってしまいます。

何も考えずにクエリを叩いてしまっていると、プロジェクト予算を食い潰してしまうということもあるので、「クエリを叩く際にどれくらいの負荷がかかるのか」を理解しながら書けるようになると一人前のデータアナリストと言えるかなと思います。

Lv.1 可読性が高いSQLにする

SQLを読みやすいものにする。これは専門知識が必要というよりも思いやりであり、礼儀礼節みたいなものです。

まずは最低限、初めて見た時にそのクエリの内容を理解しやすいようにしておくというのが、
ただ動くだけのSQLからレベルアップする最初のポイントです。

書き方を揃える

SQLの書き方は厳格なルールがあるわけではありません。(チームで決めているプロジェクトもあるとは思います)

個人差が出てしまうのはある程度仕方ないと思いますし、それを揃えることに力を注ぐべきかはプロジェクトの考え方によると思います。

ただ、同じクエリの中で、上の方の書き方と、下の方の書き方がぶれていたりすると、非常に読みづらいです。

有名どころでいうと、例えば下記のようなポイントだけでも気をつけるだけでも、非常に見やすくなります。(下記は一例ですので、読みやすいなと思う人の真似をしていくのが良いと思います)

• 予約語/関数は大文字にする
• 2スペースでインデントを揃える
• カンマは行の最後につける
• 長いテーブル名はエイリアスをはる(view_logs → vlなど)

中身がわかる変数名をつける

エンジニアの仕事の中で、コードを読むという時間は馬鹿になりません。
SQLを書くときに、クエリを読む時間も同じく馬鹿になりません。

その時間を少しでも抑えるために大切になってくるのは、わかりやすい命名です。
ミスリードしてしまわず、内容の詳細をみなくても理解できるような名前をつけてあげることが大切です。

-- ❌ わかりづらい
SELECT
  click_date,
  COUNT(DISTINCT user_id) AS click_unique_user,  -- 変数が冗長
  COUNT(user_id) AS user_cnt  -- user_cntと書いてしまうとユニークユーザーと間違う恐れも
FROM
  click_logs
GROUP BY 
  click_date

-- ⭕️ 変数名だけでイメージが湧く
SELECT
  click_date,
  COUNT(DISTINCT user_id) AS click_uu,
  COUNT(user_id) as click_cnt
FROM
  click_logs
GROUP BY 
  click_date

また、case whenで作っているカラムなどは特に、条件文を良く見ないと理解できなかったりするので丁寧に命名してあげると良いと思います。

必要な箇所(ドメイン知識)にはコメントを入れる

SQLは何をやっているのかはわかりやすい(文法としてそれほど複雑なものがないので)基本的にはコメントが必要ない場面がほとんどです。
しかし、いざ実務で分析業務をしていると、様々な条件を設定する必要が出てくる場面があります。
SQLの一般的な文法に関してのコメントは不要ですが、なぜその条件文が必要なのか？という下記のようなコメントは積極的に入れましょう。

例.sql

SELECT
 ...
WHERE
  user_type = 1  -- 未会員のユーザーを除外するため
  AND mail_id = '444422'  -- 「ポイント進呈メール」からの流入者に絞る

Lv.2 再利用性が高いSQLにする

条件だけを変えて、そのクエリをそのまま使うという場面もありますし、そのユーザー情報などの一部のクエリだけを使う場面もあります。
そうした別の分析をするにあたり、使い回しをしやすいSQLにすることが、次のステップです。

最初のクエリを作る際には余分に時間がかかるかもしれませんが、長期的に見れば必ず作業効率が上がります。

テーブルとして切り出せるようなサブクエリを作る

スタースキーマ(wikipedia)

スタースキーマ または 星型スキーマ はデータウェアハウスに利用される最も単純なスキーマである。スタースキーマには唯1つもしくは少数のファクト表と複数のディメンション表が含まれる。スタースキーマはスノーフレークスキーマの一種であるが、多くの用途で利用されている。

ファクトとディメンションという言葉は少し馴染みないかもしれません。
集計するときには、「〇〇別の××」 (ex. 年齢別の売り上げ)を出すと思います。
この時の、〇〇をディメンション、××をファクト といいます(集計時にはメジャーとも呼びます)。

集計する時は、必要なディメンションとファクトを洗い出して、それらを含むスタースキーマをサブクエリとして作成してあげると、再利用しやすいクエリにすることができます。

例で見てみましょう。

「メルマガ分析」をするとなると、例えば下記のようなディメンションやファクトが考えられます。

ディメンションの例
・性別
・年齢
・ユーザーセグメント(新規/既存)

ファクトの例
・メルマガの送信
・メルマガの開封
・メルマガ内のリンククリック
・メルマガからの許諾OFF

となると、作成すべきスタースキーマテーブルは下記のようになります。

mail_id	user_id	sex	age	user_class	send_at	open_at	click_at	turn_off
m1	u1	1	age1	new	12/10	12/10	12/10	null
m2	u2	2	age2	exist	12/10	12/11	null	null
m3	u3	2	age2	exist	12/10	12/12	12/12	null
m4	u4	1	age4	exist	12/10	12/15	null	null
m5	u5	99	age3	exist	12/12	12/18	null	12/18
m6	u1	1	age1	new	12/12	12/12	null	null

このようなテーブルをサブクエリとして作っておくとことで、
・年齢別でリンクのクリック率(クリック数/開封数)を知りたい
・12月のメールの開封率を知りたい
・新規と既存でのメールを開いた人の中で許諾OFFにした割合を比較したい
のような分析を全てこのテーブルをもとにして取得することができます。

「年齢別で開封率(開封数/送信数)とリンククリック率(クリック数/開封数)を知りたい」
場合は、下記のようなクエリになります(上記のテーブルを仮でstar_schemaとしています)。

SELECT
  age,
  COUNT(CASE WHEN open_at IS NOT NULL THEN mail_id ELSE null END) / COUNT(mail_id) AS open_ratio,
  COUNT(CASE WHEN click_at IS NOT NULL THEN mail_id ELSE null END) / COUNT(CASE WHEN open_at IS NOT NULL THEN mail_id ELSE null END) AS click_ratio
FROM
  star_schema
GROUP BY age

このように、スタースキーマテーブル(を作るサブクエリ)さえ作ってしまえば、これを再利用する(使い回す)ことによって、様々なクエリを簡単に書くことができるようになります。

スタースキーマテーブルを作る時のコツは、この先で書いています。
最初に集約するテーブルを決めて、そのテーブルにJOINする

いち早く重複を除去する

SQLでよく見る失敗の一つが
「JOINしたらなんか行数めっちゃ増えたんですけど、、」
というものです。

そして、これの最もよくみる原因は、
「重複が紛れているテーブルをJOIN」
してしまっていることです。

もちろん、「重複がある＝間違っている」ではない ですが、
必要のない重複は、極力早い段階で重複を省いてあげることが大切です。

また、nullが紛れていることでの数値のブレも同じくらいよくみます。

nullと重複の有無は常に確認して、必要ない場合は早い段階で除去してしまうようにしましょう。

最初に集約するテーブルを決めて、そのテーブルにJOINする

「最終的にどんなテーブルを作成したくて、そのために、どんなテーブルにどんなテーブルをJOINするのか」
を最初に計画しましょう。

よく、いきなりクエリを書き始める人がいますが、簡単なクエリ以外では、よほどの天才でないと難しいです。

例として上述したスタースキーマテーブルを作成する場合を考えてみましょう。

最終的なテーブルを思い描き、そのために必要なテーブルを列挙します(送信履歴テーブル、開封履歴テーブル、クリックログのテーブル、など)。

今回で言えば、送信履歴テーブル(send_logs)を集約するテーブルとして適切そうです(mail_idでJOINしていくときに、送信していないと、それ以外のファクト(開封やクリック)が発生しないため)。

具体的なカラム名などは省略しますが、下記のような枠組みを最初に思い描いてから書き始めると、スムーズに作成できると思います。

WITH send_logs AS (
   ...
),
open_logs AS (
   ...
),
click_logs AS (
   ...
),
...
star_schema AS (
  SELECT  
    sl.mail_id,
    sl.user_id,
    sl.sex,
    sl.age,
    sl.user_class,
    ol.open_at,
    cl.click_at,
    ...
  FROM send_logs AS sl
  LEFT JOIN open_logs AS ol ON sl.mail_id = ol.mail_id
  LEFT JOIN click_logs AS cl ON sl.mail_id = cl.mail_id
)

Lv.3 処理時間が短いSQLにする

優先して身につけるべきは、上述した可読性のある再利用性のあるSQLを書けるようになることだと個人的には感じます。しかし、「自分の叩いているクエリがどれくらい時間がかかりそうな処理なのか」を理解することも非常に大切です。

本質的にSQLの処理速度を理解するためには、indexの理解が必要不可欠です。
今回は導入として、具体的なtipsを紹介するに留めますが、この記事を足掛かりにしてよりSQLの理解を深めていっていただければ幸いです。

SELECT * を利用しない

便利なのでついつい使ってしまうランキングNo1。

別にこれくらいそれほど変わらないのでは？という気になったりもしますが、業務で扱うデータだと、これだけでもパフォーマンスに差が出ます。
カラム数が百を超えるようなテーブルと出会うこともあると、流石にやらない方がいいのだろうと思うようになります。

それに加えて、どのカラムを利用しているのかが明確になるため、デバック時や他の人からの引き継ぎつぐ際には書かれていると非常に重宝します。

bad.sql

SELECT * FROM user_info

good.sql

SELECT id, name, mail FROM user_info

WHERE句で関数や計算を利用しない

where句で関数や計算式を利用してしまうと、indexの検索利用できずに全件検索をしてしまうため、速度が遅くなってしまう恐れがあります。
indexはそのままで、比較するもの(下記の例で言えばwhere句の右辺)に関数や計算式を持ってくるようにすると良いです。

bad.sql

SELECT *
FROM view_log
WHERE TO_CHAR(view_date,'YYYY/MM/DD') = '2022/12/17'

good.sql

SELECT *
FROM view_log
WHERE view_date = TO_DATE('2022/12/17' ,'YYYY/MM/DD')

bad.sql

SELECT user_id, item_id, price 
FROM purchase_log
WHERE price * 1.1 > 1000

good.sql

SELECT user_id, item_id, price 
FROM purchase_log
WHERE price > 1000/1.1

JOINする前に条件を絞る

テーブルをJOINするときに、必要なものに絞ってからJOINをすると、処理が軽くなります。
※ 厳密に言えば、サブクエリにはindexが存在しないことで処理が軽くならない場合もあります(が、一般的に処理が軽くなる場合が多いです)

具体的なクエリを見ながら考えてみましょう。

user_infomationのテーブルにおいて、sexが1(男性),2(女性),99(不明)の3つがあるとします。このとき99のユーザーが9割にのぼるとしましょう(ほんの一部のuserしか性別がわかっていない)。
そして、今回は男女で差があるかどうかを分析したいとすると、sexが99に関しては計算する必要がありません。

そういう場合は、下記のようにwhre条件を追加することで、処理を早くすることができます。

修正版.sql

WITH user_info AS (
  SELECT
    user_id,
    sex
  FROM
    user_information
  -- このWHERE条件を追加する
  WHERE
    sex IN (1, 2) 
)
SELECT
  info.sex,
  COUNT(DISTINCT click.user_id) AS top_uu
FROM
  click_log_history AS click
  LEFT JOIN user_info AS info
    ON click.user_id = info.user_id
WHERE
  click.created_at BETWEEN '2022-10-01' AND '2022-10-31'
  AND click.page_title = 'TOP'
GROUP BY info.sex

おわりに

SQLだけでも解説書が何冊も発売されているほど奥が深いです。
データ分析や機械学習系の業務をするには、切っても切り離せない相棒です。
この記事が、よりSQLを見直す一つのきっかけになれば幸いです。

弊社では、経験の有無を問わず、社員やインターン生の採用を行っています。

興味のある方はこちらをご覧ください。