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

Last updated at 2024-11-08Posted at 2023-09-12

はじめに

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

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

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

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

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

サブクエリを多用したSQLは複雑に絡み合った大きな複雑な塊になってしまいます。サブクエリを使ったSQLでは、処理の流れは上から下ではなく、ネストされた内側から始まります。しかも、必ず内側から読んでいけば理解できるかというとそうでもなくて、内側のクエリが外側のクエリの影響を受けていて、内側のクエリだけを読んでも内容を理解できないことがあります。

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

サブクエリを使った難しいSQLの例

select 
  employee_id,
  work_year,
  work_month,
  monthly_hours
from (
  select 
    employee_id,
    extract(year from work_date) as work_year,
    extract(month from work_date) as work_month,
    sum(work_hours) as monthly_hours,
    (select avg(sum(work_hours)) 
     from `project.dataset.attendance` as inner_attendance
     where extract(year from inner_attendance.work_date) = extract(year from outer_attendance.work_date)
       and extract(month from inner_attendance.work_date) = extract(month from outer_attendance.work_date)
     group by extract(year from inner_attendance.work_date), extract(month from inner_attendance.work_date)
    ) as avg_monthly_hours
  from `project.dataset.attendance` as outer_attendance
  group by employee_id, work_year, work_month
) as subquery
where monthly_hours > avg_monthly_hours
order by work_year, work_month, monthly_hours desc;

サブクエリではなく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日

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

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

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

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

例：CTEを使った例

with
-- 平均給与をWith句で計算
calc_avg_salary as (
    select avg(salary) as average_salary  
    from employees
),

-- 平均給与以上でフィルタリング
filter_employees as (
    select 
        employee_id,
        employee_name,
        salary
    from employees, calc_avg_salary
    where
        employees.salary > calc_avg_salary.average_salary
)
select * from filter_employees

おいおい、CTEを使った方が冗長でわかりずらいじゃないか！と思ったかもしれません。
この例では本当にその通りなのですが、SQLの内容がより複雑になったり巨大になっていったときに、上から順番に処理を読める方が処理を理解しやすくなります。

意図を明確にする

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関数」を使ってゴニョゴニュする無駄に複雑なSQLを生み出してしまったことがある

SQLの実行基盤を整える

これまではどんな環境でも使えるSQLのテクニックについて記載しました。
アプリケーションのCRUDで使うSQLではなく分析目的のSQLであれば、データ分析基盤を整えることによって複雑な分析を行うことが容易になります。
dbtやtrocco、redashのようなツールを使えばSQL実行結果に対してSQLを実行することができるので、処理を複数のSQLファイルに分割し、一つのSQLファイルをシンプルに保つことができます。
1つの巨大な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

クソコードなSQL。めちゃめちゃに長かったり、サブクエリのネストが深すぎて理解不能なSQLのこと。クソコードを憎んで人を憎まず。書いた人のことをバカにしたり責めたりする気はない。 ↩
次の行の値を取得する関数 https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#lead ↩

278

340

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up