リーダブルSQL[より良いSQLを書くためのシンプルで実践的なテクニック]

はじめに

最近エンジニア界隈では「リーダブルコード」が話題なっていますね。
リーダブルコードでは、このような定理が紹介されています。

「コードは他の人が最短時間で理解できるように書かなければいけない。」
Dustin Boswell リーダブルコード P.3 より引用

SQLでも同じことが言えそうです。
リーダブルなSQLを書いてないと結婚できない時代が今まさに到来しようとしています。

皆さん、クソSQL¹を読んだことがありますね？ クソSQLを書いたことがありますね？
僕は、あります。

そこで、本記事ではどうしたらリーダブルなSQLが書けるかというアイデアを紹介します。

処理の流れの順に上から読めるようにする

人間のメンタルモデルは、問題やタスクを小さなステップに分割し、それぞれを順番に実行することに適しています。
サブクエリを使ったSQLでは、処理の流れは上から下ではなく、ネストされた内側から始まります。ネストされたクエリが外側のクエリの影響を受けることもあります。そう言った場合では、内側のクエリを読んだだけでは内容を理解できないことがあります。

SQL上で処理の流れを順に上から読めるようにすることは、リーダブルなSQLを書く上で重要な要素です。

サブクエリではなくCTEを使う

サブクエリは難しすぎます。もちろん、サブクエリを使わざるを得ないケースはありますが、避けられるケースでは避けるべきです。
CTE（Common Table Expression）はSQLのクエリ内で一時的な結果セットを定義するためのテクニックです。
近年では、大半のデータベース製品でCTEがサポートされています。

RDBMS	サポートバージョン	CTEサポートのリリース日
PostgreSQL	8.4以降	2009年7月1日
MySQL	8.0以降	2018年4月18日
SQLite	3.8.3以降	2014年2月3日
Microsoft SQL Server	2005以降	2005年11月7日
Oracle Database	9i以降	2001年
MariaDB	10.2以降	20２2年2月12日

Ruby on Rails²やLaravel³といったWebフレームワークのORマッパーでもCTEを利用できるようです。

CTEを使って処理の流れを整理する

CTEでは「With句」を用います。
例として従業員テーブルから、各従業員の給与が平均給与よりも高い従業員の情報を取得するクエリを見てみましょう。

例: CTEを使わずにサブクエリーを使った例

select employee_id, employee_name, salary
from employees
where salary > (
    select avg(salary)  -- 平均給与をサブクエリで計算
    from employees
)

例：CTEを使った例

with
avg_salary as (
    select avg(salary) as average_salary  -- 平均給与をWith句で計算
    from employees
),
filter_employees_by_avg as (
    select e.employee_id, e.employee_name, e.salary
        from employees as e
    inner join avg_salary as a
        on e.salary > a.average_salary;
)
select * from filter_employees_by_avg

CTEを使った方が、「平均を計算してそれ以上の従業員を取得する」という頭の中の処理の流れと一致した記述ができ、理解しやすいSQLになります。
※この例ではCTEを使った方が冗長に思えるかもしれませんが、SQLの内容がより複雑になったり巨大になっていったときにCTEのありがたみが身に染みるようになります。

意図を明確にする

SQL内の処理について意図を明確にすることが重要です。
処理を理解する手掛かりを残しましょう。

適切なコメントをつける

言わずもがなですが、SQLだけ見ても理解が難しい場合はコメントを追加しましょう！

select * from companies
where status = 3   -- 公開状態で絞り込む

With句を適切に分割する

パフォーマンスが許せば、CTE は単一の論理的な作業単位を実行する必要があります。
一度に多くの作業を一つのWith句で記述すると、読みづらくなってしまいます。

With句は次の主なカテゴリに分類されます。

カテゴリ	定義
Import	データを取り込むために使用される。結合や列変換など複雑な操作を避ける。
Logical	最終結果に向けてImportされたデータを使用して論理ステップを実行するために使用される。

インポートを分離する

データを取り込むSQLの記述をWith句に切り出します。
これは、通常のRDBMSでは過剰かもしれませんが、BigQueryなどのテーブル名を指定するために長い記述が必要な場合は実施するとよいです。

-- importの部分だけ別のWith句に切り出す
with
companies as (
    select * from `test_project.staging__mysql.stg_companies`
),
filter_companies as (
    select * from companies
        where is_enable = true
)
select * from filter_companies

ロジックを処理ごとに分けて適切な名前をつける

パフォーマンスが許せば、CTE は単一の論理的な作業単位を実行する必要があります。
また、CTE名は、その機能を伝えるために必要なだけ冗長にする必要があります。

最後のSQLは単純なselect句のみにする

with句に全てのロジックを含め、with句よりうしろの最後のselect文はシンプルなselect句のみにしましょう。
これは、トラブルシューティング中に途中のロジックを簡単に実行できるようにするためです。

with
    do_something_1 as (
        select * from -- ...
    ),
    do_something_2 as (
        select * from -- ...
    ),
    do_something_final as (
        select * from -- ...
    ),

    -- この最後のselect文はシンプルなselect文にする
    select * from do_something_final

見ただけで構造がわかるようにする

コードスタイルの統一

SQLにはさまざまな書き方のスタイルがあります。
予約語を大文字で書く？ 値の最後にカンマをつける? どこで改行する？ など
この記事ではdbtのSQLベストプラクティスを参考に小文字で記載しましたが、
予約語は大文字の方が見やすいという人も多いかもしれません。
言語ごとに一般的なコードスタイルがあると思うのでそちらに合わせるのが良いかと思います。

（言語ごとの一般的なコードスタイルのリンクを貼る予定です）

スタイルを整える

コード行数を減らすために最適化しないでください。
自動フォーマット機能がある環境では、その機能を活用しましょう。

オンラインでフォーマットしてくれるツールもあります。

キーワードを省略しない

AS、INNER JOINなど省略してもよいキーワードがありますが、省略しないようにしましょう。

用意された素晴らしい機能を活用する

データベースごとに便利な関数やステートメントが用意されています。
知ってるSQLの表現を愚直に組み合わせれば実現できる場合でも、適切な機能を使うことでよりシンプルにできるケースがあります。

データベースごとのSQLについての公式ドキュメントや有志の日本語ドキュメントが用意されているので、眺めてみると新しい発見があるかもしれません。
（投げやりですみません。「これ知らなかったけど便利だよ！」というものがあればぜひ教えてください。）

• BigQuery https://cloud.google.com/bigquery/docs/introduction-sql?hl=ja
• MySQL https://dev.mysql.com/doc/refman/8.0/ja/
• PostgreSQL日本語ドキュメント https://www.postgresql.jp/document/15/html/sql.html

余談:筆者の場合

筆者の場合, BigQueryの「LEAD関数⁴」を知らなかったため、「ROW_NUMBER関数」で行番号を追加したテーブル（CTE）を作り、そのテーブルに対して行番号+１した行を取得するみたいな回りくどいSQLを書いてしまったことがある。

SQLの実行基盤を整える

これまではどんな環境でも使えるSQLのテクニックについて記載しました。
アプリケーションのCRUDで使うSQLではなく分析目的のSQLであれば、データ分析基盤を整えることによって複雑な分析を行うことが容易になります。
1つの巨大なSQLで一発でどうにかするのが困難な場合は分析基盤を検討しましょう。
dbtやtrocco、redashのようなツールを使えば、SQLの結果に対してSQLを実行することができるので、一つのSQLをシンプルに保つことができます。

終わりに

最後まで読んでいただきありがとうございます。
思いつくままに、筆者が思うリーダブルなSQLの書き方を紹介しました。

「おい、逆にそれは見づらいだろ！」
「こういうふうに書いたらもっと見やすいよ！」
といった、皆さんの多様なご意見お待ちしてます。

参考

• https://xtech.nikkei.com/it/article/NEWS/20090702/333088/ （PostgreSQL 8.4リリース日）
• https://www.s-style.co.jp/oss/22746 (MySQL8.0 GA版リリース日)
• https://www.sqlite.org/releaselog/3_8_3.html (SQLite 3.8.3 リリース日)
• https://atmarkit.itmedia.co.jp/news/200106/16/oracle9.html (Oracele Databale 9iリリース日)
• https://www.s-style.co.jp/oss/20176 （MariaDB GA版リリース日）
• dbt Style Guide

1. クソコードなSQL。めちゃめちゃに長かったり、サブクエリのネストが深すぎて理解不能なSQLのこと。クソコードを憎んで人を憎まず。書いた人のことをバカにしたり責めたりする気はない。 ↩

2. https://blog.kiprosh.com/rails-7-1-construct-cte-using-with-query-method/ ↩

3. 外部パッケージではあるが https://github.com/staudenmeir/laravel-cte ↩

4. 次の行の値を取得する関数（え、そんなことできるのか！？と思った） 　 https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#lead ↩