Zenn
🍙

人間のためのリーダブルSQL

2025/01/28に公開
30

これは何?

  • 私 tenajima が分析用の SQL を書くときに意識していることになります
  • 以前書いた「データ基盤のためのリーダブル SQL」はデータ基盤開発者向けに書きましたが、今回はデータ基盤や dbt のコードではなく一般的な SQL を書く人向けのリーダブル SQL という立ち位置で書きたいと思います
  • 当初は「分析者のためのリーダブル SQL」として書こうと思いましたが、エンジニアの人も bizdev の人も人事の人も、SQL を書くなら誰にでも意識してほしいなと思い、「分析者」をとっぱらいました(結果人間なのかというツッコミはある)

https://zenn.dev/tenajima/articles/e8f82f6a0889d3

なぜ人間はリーダブルな SQL を書く必要があるのか

前回の記事ですが、データ基盤を使って SQL を書く人はデータ基盤を作る人に比べて「書く時間が読む時間より長い」→ ゆえにデータ基盤を作る人は SQL の読みやすさを重視する必要があるだという主張をしました。
しかし、日々を過ごすうちに「これはデータ基盤を作る人に特化した特殊技能ではない」という思いが強くなってきました。
そんな思いを徐々に育んでいったのは以下のようなケースにおいてです。

  • データ抽出の SQL を書いたが数値感が一致しないという問い合わせをもらう
  • 前任者が使っていた SQL を継続運用したい、でも指標をどうやって追加したらいいかわからないという問い合わせをもらう
  • 上記の問題があるので SQL をレビューしてほしいという依頼をもらう
  • データ抽出のための SQL を書いたが、ダブルチェックのために SQL のレビューをしてほしいという依頼をもらう
  • 先月と同じようなデータ抽出要件があるのだけど、先月の SQL ってどのような処理をしていたか読み返しておきたい

これらの困りごとの根っこは次のように考えています。
自分が書いた (前任者が書いた) SQLを理解するのに必要な脳のメモリ容量が多い
これに尽きると考えています。

  • データ抽出の SQL の数値感が一致しない、前任者の SQL に指標を追加したいがやり方がわからない
    • 手元にある SQL の複雑性が、自分の脳のメモリで処理できる量を超えてしまっている
  • 自分の脳のメモリで処理できない SQL のレビューをお願いしたい
    • より SQL を解読する脳のメモリが大きい人への依頼だけど、同じ人間なので辛さが移管しているにすぎない
  • 先月の自分が書いた SQL が解読できない
    • その SQL を解読するために脳のメモリに載せなきゃいけない情報量が多すぎる

ただ読みにくい SQL が残っていく分にはまだマシですが、脳のメモリを多く使う SQL はバグを見つけづらいと言う最大の問題があります。
結果、間違った数値による意思決定や、セキュリティインシデントに繋がる可能性が増大します。

この記事はこのように使えます

SQL を書く人のメリット

  • 自分が意図するデータ抽出が正しく行われることを手助けします
    • 書いた後に読みにくい SQL は、バグを見つけ出すのも難しいし、それをデバッグするのも容易ではありません
    • 意図せぬ join ミスによって値を多く見積ったり、少なく見積ったりすることを防ぐのに役立つでしょう
  • 同じようなデータ抽出を1ヶ月後にするときに、その SQL を再び理解するのに役立ちます
    • もし1ヶ月前の自分が書いた SQL が非常に読みにくかった場合、戦々恐々としながらその SQL を再びクエリするか、その難解な SQL を解読するしかありません
    • 読みやすいクエリは1ヶ月後の自分が理解しやすいのはもちろん、1ヶ月後に必要になる条件の変更(期間の変更など)も非常に行いやすく書かれているはずです

SQL をレビューする人のメリット

(これはまさに私自身が受けたいメリットです)

  • SQL の複雑性をレビュー時に自分が受け取り、それを受け取り続けることをやめるきっかけになります
    • 「せめてこのルールは守ってほしい、その上でレビューするよ」このラインを明示することはレビュアー側だけではなく、レビュイーに道を指ししめることにもなります
    • ゆくゆくはレビュイー側の依頼が「SQL をデバッグしてほしい」から「SQL のロジックに齟齬がないかの確認してほしい」へと次元が一段階上がることを目指します

データ基盤のためのリーダブルSQLとの関係

前回の記事との立ち位置としては以下の画像のようになります。
データ基盤を作る時よりも制約は少ないし、それほど運用することも想定していないと思っています。
なのでこれから書くこともそれほどガチガチのことを言うつもりもなく、そんなに構えなくてもいいよ、という立ち位置を取ろうと思います。
その中で

  1. 最低限これだけは守ってほしい
  2. これも考えられるとより良いSQLになる
  3. 私はこれも気にして書いています

のルールに分けて書いていこうと思います。

データ基盤のためのリーダブル SQL との関係

サンプルSQL

まず極悪読みづらい SQL を頑張って作ってみます。

こちらのテーブルを参考に作ってみます。
https://github.com/dbt-labs/jaffle-shop-classic

select
  T1.customer_id,
  month,
  first_order_date,
  order_count,
  order_total_amount,
from (
  select
    customer_id,
    date_trunc(order_date, month) as month,
    count(*) as order_count,
    sum(amount) as total_amount,
  from `your_project.jaffle_shop.orders`
  where status = "completed"
  group by customer_id, month
) as T1
inner join (
  select
    *
  from `your_project.jaffle_shop.customers`
) as T2
  on T1.customer_id = T2.customer_id

いかがでしょうか?我ながら極悪なSQLが書けたのではないかと思っています。
ではこの SQL をルールを適用しながら修正していってあげましょう。

最低限これだけは守ってほしい

「レビューをお願いするなら、その前に以下の事項が守られているかは担保した上でレビューお願いしてね」というテンションのものを以下に記していきます。
また、以降の言葉尻が「〇〇してあげる」というようになっています。これはレビュアーへの礼儀、思いやりの意図を込めているのと、その恩恵を受ける人に紛れもない自分自身も含まれるので、将来の自分への親切心という意味も込めています。

CTEが使える環境にあるならサブクエリは使わないであげる

CTE とは Common Table Expression (共通テーブル式) のことです。with 句で書けるあれです。
CTE って名前なのだと頭の片隅にあるといろんなものを調べるのに便利かもしれません。

why

サブクエリを使うといきなり見たことのない情報が飛び込んできてしまいます。
いきなり「あれ?order_total_amount なんてカラムあったっけな?」と読んでいくとサブクエリでその場で定義されているという事象に出会います。
SQL を上から順に読んでいたら、それを中断してサブクエリがどのような処理をしているか理解しなければいけません。
例えるなら「数学の証明問題を解いてる途中で、古文の文章問題を解かされて、また証明問題の続きを解かされる」そんな気分になります。
数学の証明問題を一度脳のメモリに置いたまま古文の文章問題が始まるのでとても大変です。
これを CTE を使ってあげると意味のあるまとまりごとに理解することを促せます。

それでは SQL を修正してみましょう。

修正版

with 
  T1 as (
    select
      customer_id,
      date_trunc(order_date, month) as month,
      count(*) as order_count,
      sum(amount) as total_amount,
    from `your_project.jaffle_shop.orders`
    where status = "completed"
    group by customer_id, month
),

  T2 as (
    select
      *
    from `your_project.jaffle_shop.customers`
  )

select
  T1.customer_id,
  month,
  first_order_date,
  order_count,
  order_total_amount,
from T1
inner join T2
  on T1.customer_id = T2.customer_id

サブクエリがなくなると上から順番に頭の中に処理していけば良くなるように感じませんか?
少なくとも最後の select 句で「途中でg roup by を意識しなきゃいけない」というようなことはなくなっていると思います。

CTEには意図を込めた名前をつけてあげる

次に違和感が残るのは T1、T2 というものです。

why

人間はものに名前をつけることで対象を認識できるので、適当な名前をつけることは認識を諦めてることと同義です。
T1、T2 などの適当な名前をつけているときは SQLのエラーを回避するためにつけていることがほとんど と考えています。
読みやすい SQL などの観点からは程遠いところにあるのではないかなと思っています。
SQL は長いと100行くらいになっていきます。最初の方に定義した CTE が最後の CTE に登場することもあります。
そのときに T1 というのが唐突に出てくると「T1 って何だっけ?」と見返すことが必要になるかもしれません。それは今まで読んできた処理を一旦脳のメモリに置いて最初の方の CTE を読むことになり、脳のメモリを不当にたくさん必要とします。
いやいや、100行の SQL くらい...と思うかもしれませんが、適切な名前をつけるだけで不当な脳のメモリを削減できるならやれば良くない?のスタンスをとっています。
今や AI に適当な名前の候補を数個出してもらうことも簡単な時代なのでさほど難しいことではありませんね。

修正版

with 
  order_metrics_by_user_monthly as (
    select
      customer_id,
      date_trunc(order_date, month) as month,
      count(*) as order_count,
      sum(amount) as total_amount,
    from `your_project.jaffle_shop.orders`
    where status = "completed"
    group by customer_id, month
),

  customer_info as (
    select
      *
    from `your_project.jaffle_shop.customers`
  )

select
  order_metrics_by_user_monthly.customer_id,
  month,
  first_order_date,
  order_count,
  order_total_amount,
from order_metrics_by_user_monthly
inner join customer_info
  on order_metrics_by_user_monthly.customer_id = customer_info.customer_id

できるだけその CTE が何を表しているかは命名できた方が良いと考えています。
「joined_data」や「all_data」のような CTE 名もよく目にするのですが、ビジネスで用いる言葉が全く現れない CTE 名は何を表しているか読み取れないので避けるべき です。

ここまでのまとめ

ここまでが「最低限これだけは守ってほしい」ものをまとめたものになります。
いきなりいろんなことを意識するのは難しいので、まずはこのポイントだけを意識して始めてみてください。
これだけでもあなたの SQL は何倍もリーダブルになったはずです。

これも考えられるとより良い SQL になる

複雑な SQL も安全に書いていきたいんだという思いがある方はぜひ意識してほしいルールになります。

適切にコメントをつけてあげる

why

この SQL を読む人が同じ前提を持っているか分からない場合はコメントがあるとよりリーダブルになります。
「この SQL を読む人」はもしかしたら1ヶ月後のあなたかもしれません。コメントのレベルは1ヶ月後の自分が読んでも困らないレベルにコメントをつけてあげるのが良いでしょう

修正版

with 
  order_metrics_by_user_monthly as (
    select
      customer_id,
      date_trunc(order_date, month) as month,
      count(*) as order_count,
      sum(amount) as total_amount, -- 消費税は含まれない
    from `your_project.jaffle_shop.orders`
    where status = "completed"
    group by customer_id, month
),

--- 割愛

最後は final の CTE で締めてあげる

why

このルールを適用すると SQL の最後は select * from final で統一されていきます。
これができると、途中の CTE がどのようなアウトプットになっているか確認したいときに select * from order_metrics_by_user_monthly とすると確認できるようになります。
デバッグしやすくするためのルールです。
SQL はデバッグし続けなければ正しいものは出ない、小さく確かめながら進めないと間違ってしまう、という姿勢を取るためのルールの一つになります。

修正版

with 
  order_metrics_by_user_monthly as (
    select
      customer_id,
      date_trunc(order_date, month) as month,
      count(*) as order_count,
      sum(amount) as total_amount, -- 消費税は含まれない
    from `your_project.jaffle_shop.orders`
    where status = "completed"
    group by customer_id, month
),

  customer_info as (
    select
      *
    from `your_project.jaffle_shop.customers`
  ),

  final as (
    select
      order_metrics_by_user_monthly.customer_id,
      month,
      first_order_date,
      order_count,
      order_total_amount,
    from order_metrics_by_user_monthly
    inner join customer_info
      on order_metrics_by_user_monthly.customer_id = customer_info.customer_id
  )

select
  *
from final

selectするカラム名は明示してあげる

why

「このカラムってどこで定義されてるんだっけ?」と思ったときにすぐアクセスしやすくなります。
アクセスしにくい場合、SQL を読み進めるのを一旦止めて、各テーブルのスキーマ定義を探すようなことが必要になります。それは脳のメモリを(以下略)
しかし短い SQL の場合(十数行の場合)は私もさっとselect *してしまうこともあるのでケースバイケースで使い分けても良いと考えています。

修正版

join したときにそのカラムはどこの CTE から来たのかを明示してあげるとさらに読みやすさが上がると思います。

with 
  order_metrics_by_user_monthly as (
    select
      customer_id,
      date_trunc(order_date, month) as month,
      count(*) as order_count,
      sum(amount) as total_amount, -- 消費税は含まれない
    from `your_project.jaffle_shop.orders`
    where status = "completed"
    group by customer_id, month
),

  customer_info as (
    select
      customer_id,
      first_order_date,
    from `your_project.jaffle_shop.customers`
  ),

  final as (
    select
      order_metrics_by_user_monthly.customer_id,
      order_metrics_by_user_monthly.month,
      customer_info.first_order_date,
      order_metrics_by_user_monthly.order_count,
      order_metrics_by_user_monthly.order_total_amount,
    from order_metrics_by_user_monthly
    inner join customer_info
      on order_metrics_by_user_monthly.customer_id = customer_info.customer_id
  )

select
  *
from final

まとめ

ここまでが本記事で伝えたかったことになります。
当初の極悪 SQL と比べてみて、どちらが脳に優しいかな?と比べてもらえると良いかなと思います。(「いやー、あんまり変わらなくない?」という思いが湧いたら、「これが150行の SQLだったら」を思い浮かべてもう一度考えてみてください)

当初の極悪 SQL
select
  T1.customer_id,
  month,
  first_order_date,
  order_count,
  order_total_amount,
from (
  select
    customer_id,
    date_trunc(order_date, month) as month,
    count(*) as order_count,
    sum(amount) as total_amount,
  from `your_project.jaffle_shop.orders`
  where status = "completed"
  group by customer_id, month
) as T1
inner join (
  select
    *
  from `your_project.jaffle_shop.customers`
) as T2
  on T1.customer_id = T2.customer_id

最後にここまでのルールをまとめます。

これらを意識して、人間のためのリーダブル SQL を用いることで、皆さんの生産性が上がることを祈っています。

(おまけ) 私はこれも気にして書いています

データサイエンティスト、データアナリスト、アナリティクスエンジニアとして7年間たくさん SQL を書いてきた私が分析用のクエリを書くときに意識していることになります。
主題とは少しズレるというか、やりすぎと感じる場合もあるのでおまけとして切り出します。
以下はすべてのケースで適用するというわけではなく、より複雑な SQL を書くときに使う道具という感覚です。
今回例に上げた SQL は簡単な例なので、ここからは修正版は割愛します。

指標を複数 join するときは join key のインターフェイスを整えてあげる

どういうものかと言うと、今までの要件に加えて「ユーザー*月毎のカート追加商品数」のようなものを考えるとしましょう。
これは「orders」で表されているビジネスプロセスと別になるので、保存されているテーブルも別になるでしょう。
そのときに以下のような CTE を追加してあげるイメージです。

  add_cart_metrics_by_user_monthly as (
    select
      customer_id,
      date_trunc(event_date, month) as month,
      count(*) as add_cart_count,
    from `your_project.jaffle_shop.add_carts`
    group by customer_id, month
  ),

これを最後に final で customer_id と month を join key にしてあげます。

why

add_cart_metrics_by_user_monthlyorder_cart_metrics_by_user_monthly と同じ構成で書かれていて、CTE の名前も揃っています。
するとadd_cart_metrics_by_user_monthlyを読む時もorder_cart_metrics_by_user_monthlyを読むときの脳のメモリと大差ないリソースで読むことができるようになります。
最後の join も join hoge xx_metrics_by_user_monthly using (customer_id, month) がずっと繰り返されるような SQL を書くことができます。
その繰り返しの join を考えるときには都度「この join key で fan-out しないかな?」と考えなくても良くなります。

読むスコープが限られる CTE はネストして CTE のスコープを絞ってあげる

CTE はネストすることが可能です。

with hoge as (
  with fuga as (
    ...
  )
)

why

使うケースとしては lag 関数を使って、前月の購入回数を計算する必要があるなどの時などを想定しています。
「fuga CTE は hoge の CTE でしか使わないよ」という意味でネストしてあげます。(実際 hoge の外のスコープで fuga にアクセスすることは BigQuery ではできないようになっています。)
ネストしてあげることで、hoge を読む時以外 fuga を気にしなくても良くなるので、fuga を脳のメモリからすぐに落とすことが可能となります。
注意としては多段のネストは可読性を下げる原因にもなります。
私は CTE のスコープを絞る以外の用途には使う必要がないと考えているので、CTE が3段になることはないと考えます(このケースは2段)。

アンカーポイントとしてテストの CTE を用意してあげる

風音屋 TechBlog のデータ分析で用いる SQL クエリの設計方法で紹介されていた内容になります。
この記事自体とても良いブログだなと感じたのでぜひ一読してみてください。
この中で CTE でテストを用意するというのが個人的に衝撃でした。

why

SQL を書くうえで「何を確かめたから自分のSQLは妥当であると言えるか」を担保しておく姿勢というのはとても大切だと思っています。
自分の書いた SQL によってでた数値が正しいと絶対的に言えることはないと思っているからです。ただし「こういう観点では数値の確からしさを担保して数値を出しています」と言うことはできるので、それを行えることがプロとしての矜持かと思っています。
(少々概念的になりすぎたので実用面でも)
何か数値がおかしそうというときに、「ここまでは正しそう」というアンカーポイントがテストとして確かめられていれば、どこでバグが含まれているかの調査範囲を絞ることができます。
テストはアンカーポイントとして役立つのです。

最後に

ここまで読んでいただいた方ありがとうございました。
前回の「データ基盤のためのリーダブル SQL」はデータエンジニアの方向けに書いていたのですが、エンジニアの方にも読んでもらえたりと思ったより大きい反響を頂けました。
今回のこの記事がエンジニアやアナリストに留まらず、SQL を書くいろんな人に届くと嬉しいなと思います。
この記事をもってあとは「AI のためのリーダブル SQL」くらいしか残っていないことになるので、そこも考えていこうかなと思います。(現時点では構想0)

30

Discussion

ログインするとコメントできます