296
357

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@zackey2in株式会社M&Aクラウド

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

296
Last updated at Posted at 2023-09-12

1. はじめに

エンジニア界隈で長く読み継がれている『リーダブルコード』には、こんな定理があります。

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

ところで皆さん、読みづらいSQL1を読んだことがありますね？ 書いたことも、ありますね？
僕は、あります。

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

  • 構造を整える ── 処理の流れを上から順に読めるようにする
  • 意図を明確にする ── 命名・コメント・マジックナンバー対策
  • JOIN と SELECT を丁寧に書く ── 暗黙の挙動に頼らない
  • 高機能を活用する ── ウィンドウ関数や QUALIFY を使う
  • スタイルを統一する ── フォーマッタとリンタに任せる
  • 実行基盤を整える ── dbt などで巨大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;

このSQLの読みにくさのポイントを、頭の片隅に置いておいてください。

  • 処理の起点がネストの内側にあって上から読めない
  • outer_attendance を相関サブクエリで参照していて、内側だけ読んでも意味が取れない
  • 同じ extract(year/month from ...) が何度も登場する

2. 構造を整える ── 処理を上から順に読めるようにする

サブクエリを多用したSQLは、複雑に絡み合った大きな塊になりがちです。
処理の流れが上から下ではなく、内側から外側に進むからです。

しかも厄介なことに、内側だけ読んでも意味が分からないことがあります。先ほどの例の相関サブクエリ（outer_attendance を内側から参照する書き方）が、まさにそれです。

リーダブルなSQLの第一歩は、処理を上から下へ、ストーリーとして読めるようにすることです。

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

CTE（Common Table Expression、共通テーブル式）は、SQL内で名前付きの一時的な結果セットを定義するテクニックです。WITH 句で書きます。

主要なRDBMSのほとんどで、すでにCTEがサポートされています。

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

サブクエリで書ける処理の多くは CTE で書き直せます。簡単な例で比べてみましょう。

-- サブクエリで書いた例：給与が平均以上の従業員を抽出
select
    employee_id,
    employee_name,
    salary
from employees
where salary > (
    select avg(salary)
    from employees
)

-- CTEで書いた例
with
-- (1) 平均給与を計算
avg_salary as (
    select avg(salary) as average_salary
    from employees
),

-- (2) 平均給与を超える従業員に絞り込む
high_earners as (
    select
        employees.employee_id,
        employees.employee_name,
        employees.salary
    from employees
    cross join avg_salary
    where employees.salary > avg_salary.average_salary
)

select * from high_earners

「いやいや、CTE のほうが冗長で読みづらいじゃないか！」と感じたかもしれません。
このサイズの例ではその通りです。真価を発揮するのはSQLが大きく複雑になったときです。

CTE を使う利点は3つあります。

  1. 処理の流れを上から下へ読める ── 「平均を出してから、絞り込む」という順序がそのままコードの順序になる
  2. 各ステップに名前が付けられる ── avg_salary high_earners のように、何をしているかが名前で語れる
  3. 途中の結果を確認しやすい ── デバッグ時に最後の select * from high_earners を select * from avg_salary に差し替えるだけで途中経過を覗ける

2.2 CTE を役割ごとに分割する ── Import / Logical / Final

CTE は1つにつき1つの論理的な作業を担うのが理想です。
一つの CTE で取り込みも結合も集計もやってしまうと、結局サブクエリと同じ読みにくさに戻ります。

CTE は役割で次の3つに分類できます2。

種類 役割
Import 元データを取り込む。複雑な変換は入れず、必要なら軽い列名整形やフィルタのみ
Logical Import したデータを使って、結合・集計・条件分岐などの論理を組み立てる
Final 出力に向けた最終整形。with の外側で select する

Import を分離する

データソースの取り込みを Import 用 CTE に切り出すことで、論理部分にデータソースの詳細が漏れないようにします。

通常のRDBMSでは過剰に感じることもありますが、BigQuery のようにプロジェクト名・データセット名・テーブル名で長くなる環境では効果が大きいです。

with
-- ===== Import =====
companies as (
    select * from `test_project.staging__mysql.stg_companies`
),

-- ===== Logical =====
active_companies as (
    select *
    from companies
    where is_enable = true
)

select * from active_companies

Import 部分でテーブル参照を一箇所にまとめておくと、参照先テーブルが変わったときの修正が楽になり、論理部分は「companies というテーブルがある」という前提で読めます。

Logical CTE には意図を伝える名前をつける

CTE 名は 何をしているかが分かるなら、長くなっても構いません。
tmp1 tmp2 sub などの名前は避けましょう。

-- BAD: 何をしているのか名前から読み取れない
with t1 as ( ... ),
     t2 as ( ... )
select * from t2

-- GOOD: 名前で処理が語れる
with monthly_hours as ( ... ),
     monthly_hours_with_avg as ( ... )
select * from monthly_hours_with_avg

2.3 最後の SELECT はシンプルに保つ

ロジックはすべて WITH 句の中で完結させ、最後の SELECT は select * from <最終CTE> のような単純な形にしましょう。

with
    import_orders as ( ... ),
    filter_paid_orders as ( ... ),
    aggregate_monthly as ( ... ),
    final as ( ... )

-- 最後はこの1行だけ
select * from final

利点は2つ。

  • デバッグが楽 ── 最後を select * from aggregate_monthly に差し替えれば、途中の中間結果をすぐ確認できる
  • 読み手が「結論」だけ末尾で迷わない ── ロジックは上、出力形は最後、と役割が明確

⚠️ 本番のビューやレポート用クエリでは、select * ではなく明示的な列指定を推奨します。上流テーブルにカラムが追加されたときに、意図せず出力が変わるのを防ぐためです（詳しくは第4章）。

3. 意図を明確にする

SQL は読み手にとって「結果」は分かっても「なぜそう書いたか」は分かりにくい言語です。
WHERE句に status = 3 とだけ書かれていても、3 が何を意味するのかは SQL の中には書かれていません。

書き手の意図を、コード自体や周辺で最大限伝える工夫を紹介します。

3.1 「なぜ」を伝えるコメントをつける

SQL を読めば何をしているかは分かります。コメントで書くべきは「なぜそう書いているか」です。

-- BAD: コードを読めば分かることをそのまま書いている
-- companies テーブルから status が 3 のものを取得する
select * from companies
where status = 3

-- GOOD: コードからは読み取れない「意図」を書いている
-- 公開状態（status=3）のみ集計対象にする。下書き(1)・レビュー中(2)は除外
select * from companies
where status = 3

コメントを書くべき場面の例：

  • ビジネスロジックの根拠 ── 「経理の都合で前月分も含める」「Looker Studio が NULL を扱えないため 0 で埋める」
  • 避けた書き方の理由 ── 「JOIN で書くと N+1 になるため EXISTS を使用」
  • 暫定対応である旨 ── 「TODO: マスタテーブル化したらこの CASE を撤去する」

逆に、コードから自明なことのコメントはむしろノイズです。-- ユーザーIDで結合 のようなコメントは消しましょう。

3.2 マジックナンバー・マジックストリングをなくす

先ほどの status = 3 のように、SQL中にコメントなしで現れる定数をマジックナンバーと呼びます。
コメントで救うのは最低限の対処で、できればコードで意図を語らせたいところです。

方法1: 定数をCTEに切り出す

シンプルで効果的なテクニックです。「3」が何を意味するかがコードを読むだけで分かります。

with
-- ステータス値の定義（マスタが無いためここに集約）
status_codes as (
    select
        1 as draft,
        2 as in_review,
        3 as published
),

published_companies as (
    select companies.*
    from companies
    cross join status_codes
    where companies.status = status_codes.published
)

select * from published_companies

方法2: マスタテーブルを JOIN する

ステータスマスタが既にテーブルとして存在するなら、JOIN して名前で比較するのが理想です。

with
companies as (
    select * from `project.dataset.companies`
),
company_statuses as (
    select * from `project.dataset.company_statuses`  -- code, name を持つマスタ
),

published_companies as (
    select companies.*
    from companies
    inner join company_statuses
        on companies.status = company_statuses.code
    where company_statuses.name = 'published'
)

select * from published_companies

方法3: CASE で意味を与える

集計時のラベル付けなら、CASE で名前を付けるだけでも読みやすくなります。

select
    case status
        when 1 then 'draft'
        when 2 then 'in_review'
        when 3 then 'published'
        else 'unknown'
    end as status_label,
    count(*) as company_count
from companies
group by status_label

ELSE は省略しないことをおすすめします。想定外の値が入ったときに NULL ではなく 'unknown' のような明示的な値が返るため、データの異常に気付きやすくなります。

3.3 命名でロジックを語る

SQL のリーダブルさは、識別子の名前でかなり決まります。コメントで補足しなくても、名前から処理が読めれば最高です。

CTE 名は「何をしているか」を表す

-- BAD: 名前から処理が読み取れない
with t1 as ( ... ),
     t2 as ( ... ),
     final as ( select * from t2 )

-- GOOD: 名前を読むだけで処理の流れが分かる
with import_orders as ( ... ),
     filter_paid_orders as ( ... ),
     aggregate_monthly_revenue as ( ... )

CTE 名の付け方の例：

役割 プレフィックス例
データ取り込み import_*, source_*
絞り込み filter_*, active_*, valid_*
集計 aggregate_*, monthly_*, total_*
結合・付加 enriched_*, joined_*, *_with_*
最終出力 final

テーブル別名は意味のある略称にする

-- BAD: 一文字エイリアスは深いネストで何のテーブルか分からなくなる
select e.id, d.name, c.title
from employees e
join departments d on e.dept_id = d.id
join companies c on d.company_id = c.id

-- GOOD: 短くしつつも意味が読み取れる
select
    employees.id,
    departments.name,
    companies.title
from employees
inner join departments on employees.dept_id = departments.id
inner join companies on departments.company_id = companies.id

JOIN するテーブルが少ないならエイリアスを付けず、テーブル名そのままで書くのが一番分かりやすいです。
どうしてもエイリアスが必要なら e ではなく emp のように意味が読み取れる略称にしましょう。

カラム別名は単位や種別まで含める

-- BAD: total が何の total か分からない
select sum(amount) as total from orders

-- GOOD: 何を集計したかが分かる
select sum(amount) as total_revenue_jpy from orders

特に集計列・計算列は、*_count *_amount *_rate *_jpy のように何を表す数値かを名前に含めると、後段の処理で混乱しません。

snake_case で統一する

SQL は大半のRDBMSで識別子の大文字小文字を区別しないため、慣習的に snake_case が使われます。
camelCase だと、引用符なしで書いた瞬間に camelcase 扱いされて事故ります。チームのスタイルガイドが特になければ snake_case で揃えましょう。

4. JOIN と SELECT を丁寧に書く

JOIN と SELECT は SQL の中核です。ここに暗黙の挙動を残すと、読み手は仕様書とにらめっこしながら読むことになります。
本章では「書いてあるとおりに動くSQL」にするための3つのポイントを紹介します。

4.1 暗黙の JOIN を使わない

FROM 句にカンマ区切りで複数テーブルを書く形は、暗黙の CROSS JOIN として動作します。

-- BAD: 暗黙の CROSS JOIN ＋ WHERE で結合条件
select *
from employees, departments
where employees.dept_id = departments.id

これには2つの問題があります。

  1. JOIN の種類が分からない ── INNER なのか CROSS なのか、コードからは読み取れない
  2. WHERE 句が混在する ── 結合条件と絞り込み条件が同じ WHERE に並び、レビュー時に見分けにくい

常に JOIN を明示しましょう。

-- GOOD: JOINの種類と結合条件を明確に
select *
from employees
inner join departments
    on employees.dept_id = departments.id

INNER OUTER を省略しない

多くのRDBMSで JOIN は INNER JOIN、LEFT JOIN は LEFT OUTER JOIN と解釈されますが、書く側が意図を表明する意味で省略しないことを推奨します。

-- 省略形（動作上は同じだが、INNERか書き忘れか判別しにくい）
from a join b on ...
from a left join b on ...

-- 推奨：意図が明確
from a inner join b on ...
from a left outer join b on ...

JOIN の種類を意識する

JOIN 用途
INNER JOIN 両方に存在する行だけが必要なとき
LEFT OUTER JOIN 左テーブルを起点に、右テーブルを付加したいとき（マスタ追加など）
FULL OUTER JOIN 両側で突き合わせたいとき（差分検出など）
CROSS JOIN 全組み合わせを意図的に作りたいとき（カレンダー × 拠点など）

特に LEFT JOIN を使うときは、右テーブル側のカラムを WHERE 句で条件にすると INNER JOIN と等価になってしまうことに注意です。

-- BUG: せっかく LEFT JOIN しても WHERE で右側を絞ると INNER 相当に
select *
from orders
left outer join customers on orders.customer_id = customers.id
where customers.country = 'JP'  -- 顧客がNULLの注文も落ちる

-- FIX: LEFT JOIN を活かしたいなら ON 句に書く
select *
from orders
left outer join customers
    on orders.customer_id = customers.id
    and customers.country = 'JP'

4.2 カラム名をテーブル名で修飾する

JOIN するクエリでは、全てのカラム参照をテーブル名（または別名）で修飾しましょう。

-- BAD: id が employees の id か departments の id か読み取れない
select id, name, dept_id
from employees
inner join departments on dept_id = id

-- GOOD: どのテーブルのカラムか一目で分かる
select
    employees.id,
    employees.name,
    departments.id as department_id,
    employees.dept_id
from employees
inner join departments
    on employees.dept_id = departments.id

修飾を徹底するメリット：

  • どのテーブル由来か一目で分かる ── レビュー時の認知コストが下がる
  • 後でカラムが増えても壊れない ── 上流テーブルに同名カラムが追加されても、Ambiguous column エラーで検知できる
  • IDE / フォーマッタとの相性が良い ── sqlfluff などの規約 (references.qualification) でも推奨される

USING は便利だが乱用しない

USING(id) は両テーブルの同名カラムで結合できる便利な構文ですが、結合キーの片方の出自が見えなくなるため、複数テーブルをチェーンする場面では ON の方が安全です。

-- 単純なケースなら USING でも可
from orders inner join customers using (customer_id)

-- 複雑なケースは ON で明示
from orders
inner join customers on orders.customer_id = customers.id
inner join addresses on customers.address_id = addresses.id

4.3 SELECT * の使いどころ

SELECT * は便利ですが、使ってよい場面と避けるべき場面があります。

使ってよい場面

  • CTE 内の最終 SELECT 行 ── select * from final のように、ロジックは上で完結している
  • アドホックな調査クエリ ── 一回限りの確認用クエリ
  • CTE 間のデータ受け渡し ── 列を取捨選択する必要がない場面

避けるべき場面

本番運用されるビュー・マテリアライズドビュー・dbtモデル・レポート用クエリでは、明示的な列指定を推奨します。

理由は以下の通りです。

  1. 上流テーブルにカラムが追加されると意図せず出力が変わる ── ダウンストリームの後続処理が壊れることがある
  2. 不要なカラムまで読み込まれる ── BigQuery のような列指向DBではコスト直結（スキャン量＝課金）
  3. 依存関係が読みにくい ── どの列を実際に使っているのか SQL から読み取れない
-- BAD: 本番のビューで SELECT *
create view v_active_employees as
select * from employees where is_active = true

-- GOOD: 必要な列だけ明示
create view v_active_employees as
select
    id,
    name,
    email,
    department_id,
    hired_at
from employees
where is_active = true

BigQuery / Snowflake の便利構文

「ほぼ全部欲しいけど一部だけ除きたい」という場面では、EXCEPT 構文が便利です。

-- BigQuery: パスワードハッシュ列だけ除いて全カラム取得
select * except(password_hash)
from users

-- BigQuery: 全カラムに対して同じ変換を適用
select * replace(lower(email) as email)
from users

機密カラムを誤って露出させない用途や、テスト用クエリでスナップショットを取りたいときに重宝します。

5. 高機能を活用する

データベースには、知っているSQLの組み合わせで「ゴリ押し」できてしまうがゆえに、便利な機能の存在に気付かないまま複雑化する罠があります。

代表例がウィンドウ関数です。本章では、ウィンドウ関数とその仲間である QUALIFY を中心に、SQL を一気にシンプルにする機能を紹介します。

5.1 ウィンドウ関数 ── 集計しつつ元の行を残す

ウィンドウ関数は「集計値を計算しつつ、元の行は潰さない」という、GROUP BY ではできない操作を実現します。

-- 各従業員の給与と、その所属部署の平均給与を並べる
select
    employee_id,
    department_id,
    salary,
    avg(salary) over (partition by department_id) as dept_avg_salary
from employees

OVER (partition by department_id) の部分が「ウィンドウ」を定義しています。
部署ごとに区切って、その中で平均を計算しろという指示です。GROUP BY のように行が潰れず、各従業員行に部署平均が並びます。

同じことをサブクエリで書くと…

-- BAD: 部署平均をスカラーサブクエリで計算
select
    employees.employee_id,
    employees.department_id,
    employees.salary,
    (select avg(salary)
     from employees as inner_employees
     where inner_employees.department_id = employees.department_id
    ) as dept_avg_salary
from employees

これは冒頭の悪い例と同じパターンです。相関サブクエリは読みにくく、しばしばパフォーマンスも悪い。
ウィンドウ関数で書ける処理は、ほぼ全てウィンドウ関数で書きましょう。

よく使うウィンドウ関数

関数 用途
row_number() 連番を振る（順位付け、重複除去の起点）
rank() / dense_rank() 同順位を許す順位付け
lag() / lead() 前の行・次の行の値を取得（時系列の差分計算）
first_value() / last_value() ウィンドウ内の最初・最後の値を取得
sum() / avg() / count() over (...) グループの合計・平均・件数を行に展開
sum() over (order by ...) 累積和（ランニングトータル）

例: 直近の値との差分を取る

LAG を使うと、時系列データで「前回の値との差分」を一発で計算できます。

select
    user_id,
    purchased_at,
    amount,
    amount - lag(amount) over (
        partition by user_id
        order by purchased_at
    ) as diff_from_previous
from purchases

ROW_NUMBER で順番を振って自己結合する書き方を見たことがあるかもしれませんが、LAG を知っているとそんな苦労は不要です3。

例: 重複行から最新の1行だけ残す

ROW_NUMBER の典型ユースケース。「ユーザーごとに最新の購入レコードだけ取る」など。

-- 各ユーザーの最新の購入レコードを取得
with ranked_purchases as (
    select
        purchases.*,
        row_number() over (
            partition by user_id
            order by purchased_at desc
        ) as row_num
    from purchases
)

select * from ranked_purchases where row_num = 1

5.2 QUALIFY ── ウィンドウ関数の結果でフィルタする

ウィンドウ関数の結果を WHERE で絞り込むことはできません（評価順序の都合）。
そこで、サブクエリや CTE を経由して WHERE を書くのが定番でした。

-- 従来：CTEを経由してフィルタ
with ranked_purchases as (
    select
        purchases.*,
        row_number() over (
            partition by user_id
            order by purchased_at desc
        ) as row_num
    from purchases
)
select * from ranked_purchases where row_num = 1

BigQuery / Snowflake / Teradata / DuckDB などでは、QUALIFY 句を使ってウィンドウ関数の結果を直接フィルタできます。

-- QUALIFY を使うと CTE が不要に
select *
from purchases
qualify row_number() over (
    partition by user_id
    order by purchased_at desc
) = 1

QUALIFY は SQL の評価順序的に WHERE → GROUP BY → HAVING → WINDOW → QUALIFY の位置にあるため、ウィンドウ関数の結果でフィルタできます。
HAVING がグループ化結果のフィルタなのに対し、QUALIFY はウィンドウ関数結果のフィルタ、と覚えると分かりやすいです。

💡 QUALIFY は標準SQLにはまだ含まれていません。PostgreSQL や MySQL では使えないので、移植性が必要な場合はCTE方式を選びましょう。

5.3 各DBの便利関数を眺める

ウィンドウ関数以外にも、各DBには読みやすさを劇的に向上させる関数が用意されています。
公式ドキュメントを通読する必要はありませんが、目次に目を通しておくだけでも引き出しが増えます。

よく使う「知っていると差が出る」関数

関数 用途 対応DB例
coalesce(a, b, c) 最初の非NULL値を返す 全般
nullif(a, b) a = b なら NULL を返す（ゼロ除算回避など） 全般
safe_cast(x as type) キャスト失敗時に NULL を返す BigQuery
try_cast(x as type) 同上 Snowflake / Trino
array_agg(...) グループ内の値を配列にまとめる BigQuery / PostgreSQL ほか
string_agg(x, ',') グループ内の文字列を区切り文字で連結 全般（関数名は LISTAGG 等の差あり）
struct(...) / named_struct 構造体を作る BigQuery / Snowflake
unnest(array) 配列を行に展開 BigQuery / PostgreSQL
date_trunc('month', d) 日付を月初等に丸める 全般
generate_date_array(...) 日付の連続値を生成（カレンダーマスタ作成） BigQuery
regexp_extract / regexp_replace 正規表現での抽出・置換 全般

例: COALESCE でNULLハンドリング

-- BAD: CASEで冗長に
select
    case
        when nickname is not null then nickname
        when first_name is not null then first_name
        else 'Anonymous'
    end as display_name
from users

-- GOOD: COALESCE で簡潔に
select
    coalesce(nickname, first_name, 'Anonymous') as display_name
from users

例: ARRAY_AGG でグルーピング結果を1行にまとめる

-- 各ユーザーが購入した商品IDを配列で持つ
select
    user_id,
    array_agg(product_id order by purchased_at) as product_history
from purchases
group by user_id

ドキュメントへのリンク

各DBの関数リファレンスです。「こういうことしたいけどどう書くんだろう？」と思ったら、まず関数一覧を眺めてみることをおすすめします。

「これ知らなかったけど便利だよ！」という関数があれば、ぜひコメントで教えてください。

6. スタイルを統一する

ここまで「何を書くか」のテクニックを紹介してきました。
本章では「どう見せるか」── インデント、改行、大文字小文字、キーワードの扱いといったスタイルの話です。

スタイルは「正解より一貫性」が重要です。チーム内で揃っていれば、好みは何でも構いません。

6.1 フォーマッタに任せる

人間がスペースの数や改行位置を考えるのは無駄です。フォーマッタに任せましょう。

sqlfluff

OSSで最も広く使われている SQL リンタ＆フォーマッタです。
ルールセットでスタイルを規約化でき、CI に組み込んでスタイル違反をブロックできます。

  • ローカル実行: sqlfluff format / sqlfluff lint
  • 設定ファイル: .sqlfluff または pyproject.toml の [tool.sqlfluff]
  • CI 連携: GitHub Actions のジョブで sqlfluff lint を回す

オンラインで試せるツールもあります。
👉 https://online.sqlfluff.com/

dbt プロジェクトでのフォーマット

dbt を使っている場合は、プロジェクトに sqlfluff を組み込み、PR で自動チェックするのが定番です。
dbt-checkpoint や pre-commit と組み合わせると、コミット前にローカルで弾けます。

IDE 機能を活用する

JetBrains 系（DataGrip / IntelliJ）や VS Code の SQL拡張には、自動フォーマット機能があります。
保存時フォーマットを設定しておけば、そもそも手で整える必要がなくなります。

ポイントは「最初から自動化する」ことです。一度コードベースが乱雑になると、後から一括整形するのは大きなPRになり、レビュー負荷もコンフリクトリスクも高くなります。

6.2 キーワードを省略しない

省略可能なキーワードでも、省略しない方がリーダブルです。

省略形 推奨形 補足
select foo bar select foo as bar 列のエイリアスは AS を付ける
from employees e from employees as e テーブルのエイリアスも AS を付ける（または別名なし）
a join b a inner join b INNER を明示
a left join b a left outer join b OUTER を明示
where x = 1 and y = 2 （改行して並べる） 複数条件は1条件1行で 
-- BAD: ASなし、INNERなし、複数条件が1行
select e.id id, d.name dept
from employees e join departments d on e.dept_id = d.id
where e.is_active = true and d.is_archived = false

-- GOOD: AS明示、INNER明示、条件を改行
select
    employees.id as id,
    departments.name as dept
from employees
inner join departments
    on employees.dept_id = departments.id
where employees.is_active = true
  and departments.is_archived = false

AS を付ける2つの利点：

  1. エイリアスの開始位置が明確 ── select foo bar だと「fooというカラムをbarに」なのか「fooとbarというカラム」なのか一瞬迷う
  2. ツールが解析しやすい ── 自動補完やフォーマッタの動作が安定する

6.3 行数を減らすために最適化しない

「短い＝読みやすい」とは限りません。読みやすさのための改行・インデントは積極的に入れましょう。

-- BAD: 1行に詰め込みすぎ
select coalesce(nullif(trim(name), ''), 'Anonymous') as display_name, count(distinct case when status in (1, 2, 3) then user_id end) as active_user_count from users where created_at >= '2024-01-01' and (country = 'JP' or country = 'US') group by 1

-- GOOD: 構造が見える
select
    coalesce(nullif(trim(name), ''), 'Anonymous') as display_name,
    count(distinct
        case when status in (1, 2, 3) then user_id end
    ) as active_user_count
from users
where
    created_at >= '2024-01-01'
    and country in ('JP', 'US')
group by 1

特に意識したいのは以下です。

  • SELECT の列は1行1カラム
  • WHERE の条件は1行1条件（AND を行頭に揃える派と行末に置く派があるが、チームで揃える）
  • CASE WHEN は THEN で揃えるか改行するか、規約を決める
  • OR を多用するなら IN (...) に書き換えられないか検討

6.4 コードスタイルはチームで決める

予約語を大文字にするか小文字にするか、トレイリングカンマかリーディングカンマか、好みは様々です。

観点 選択肢A 選択肢B
予約語 SELECT 大文字 select 小文字
カンマ id, トレイリング , id リーディング
インデント スペース2 スペース4
AND の位置 行頭 行末

参考になるスタイルガイド：

**重要なのは「どれを選ぶか」ではなく「チームで揃える」**ことです。
迷ったら sqlfluff のデフォルト設定や、dbt-labs/dbt-core のような大規模OSSのスタイルに合わせるのが無難です。

7. 実行基盤を整える

ここまでは1つのSQLファイル内で完結するテクニックでした。
しかし、分析業務で扱うSQLは1ファイルでは収まりきらないこともあります。
JOIN するテーブルが10個を超え、CTE が30個並び、スクロールしても全貌が掴めない── そんな状態に陥ったら、SQLの中身ではなく実行基盤を見直すタイミングです。

SQLを複数ファイルに分割する

dbt、trocco、Redash、Dataform などのツールを使うと、SQLの結果を別のSQLから参照できます。
1つの巨大なSQLを、論理的なまとまりごとに分割して管理できるのです。

例えば dbt なら、各ファイルが1つの モデル（ビュー or テーブル）になり、{{ ref('model_name') }} で他モデルを参照します。

-- models/staging/stg_attendance.sql
select * from {{ source('mysql', 'attendance') }}
where work_date >= '2024-01-01'

-- models/marts/monthly_hours_per_employee.sql
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
from {{ ref('stg_attendance') }}
group by 1, 2, 3

-- models/marts/above_average_workers.sql
select *
from {{ ref('monthly_hours_per_employee') }}
qualify monthly_hours > avg(monthly_hours) over (
    partition by work_year, work_month
)

それぞれが短く、責務が明確で、テスト可能になります。

実行基盤の主な利点

  • 責務の分離 ── ステージング層・中間層・マート層など、レイヤごとに責任範囲を分けられる
  • 再利用性 ── よく使う集計を1つのモデルにすれば、複数のレポートから参照できる
  • テスタビリティ ── dbt の tests: で unique not_null relationships などを宣言的に検証できる
  • 依存関係の可視化 ── DAG（有向グラフ）として上流下流が一目で分かる
  • 差分実行 ── 変更があったモデルだけ再実行できる

移行の判断基準

1つの巨大なSQLでつらくなってきたら、次のサインを目安に基盤導入を検討しましょう。

  • 同じ集計ロジックを複数のSQLにコピペしている
  • CTE が20個を超え、スクロールしても全体が見えない
  • 実行に数分以上かかり、デバッグするたびに待たされる
  • 結果が正しいか確認するためのテストが手作業

8. 冒頭の悪いSQLをリファクタする

お待たせしました。第1章で挙げた「読みづらい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;

このSQLは何をしているのか

順を追って読み解くと、3段階の処理に分けられます。

  1. 勤怠テーブルを 従業員 × 年 × 月で集計し、月次労働時間を出す
  2. 各月ごとの従業員の平均労働時間を相関サブクエリで計算
  3. 平均を超えた行だけ抽出する

ステップ1: CTEに分割して上から読めるようにする

サブクエリのネストをほぐし、Import → Logical → Final の順で書き直します。

with
-- ===== Import =====
attendance as (
    select * from `project.dataset.attendance`
),

-- ===== Logical =====
-- 従業員 × 月 ごとの労働時間を集計
monthly_hours_per_employee as (
    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
    from attendance
    group by employee_id, work_year, work_month
),

-- 各月の従業員平均をウィンドウ関数で各行に付加
monthly_hours_with_avg as (
    select
        monthly_hours_per_employee.*,
        avg(monthly_hours) over (
            partition by work_year, work_month
        ) as avg_monthly_hours_in_month
    from monthly_hours_per_employee
),

-- その月の平均を超えた行のみ抽出
above_average_hours as (
    select
        employee_id,
        work_year,
        work_month,
        monthly_hours
    from monthly_hours_with_avg
    where monthly_hours > avg_monthly_hours_in_month
)

-- ===== Final =====
select * from above_average_hours
order by work_year, work_month, monthly_hours desc

これだけで、処理が上から下へ素直に読めるようになりました。
適用したテクニックは次の通りです。

適用したテクニック 該当章
サブクエリ → CTE 第2章
Import / Logical / Final で役割分割 第2章
意味のあるCTE名（monthly_hours_per_employee 等） 第3章
相関サブクエリ → ウィンドウ関数 第5章
各CTEの意図をコメントで説明 第3章
extract(year from ...) の重複を CTE で1回に集約 第2章
最後の SELECT はシンプルに 第2章

ステップ2: QUALIFY でさらに簡潔に

BigQuery / Snowflake などを使っているなら、QUALIFY で中間CTEをひとつ減らせます。
ステップ1の monthly_hours_with_avg と above_average_hours を1つのCTEに統合できます。

with
-- ===== Import =====
attendance as (
    select * from `project.dataset.attendance`
),

-- ===== Logical =====
-- 従業員 × 月 ごとの労働時間を集計
monthly_hours_per_employee as (
    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
    from attendance
    group by employee_id, work_year, work_month
),

-- その月の従業員平均を超えた行のみ抽出
above_average_hours as (
    select
        employee_id,
        work_year,
        work_month,
        monthly_hours
    from monthly_hours_per_employee
    qualify monthly_hours > avg(monthly_hours) over (
        partition by work_year, work_month
    )
)

-- ===== Final =====
select * from above_average_hours
order by work_year, work_month, monthly_hours desc

ポイントは2つ。

  • QUALIFY のおかげで monthly_hours_with_avg のような中間CTEが不要になった（4個 → 3個）
  • Final は select * from ... だけに保たれている（第2章のルール通り）

Before / After の比較

観点 Before After（QUALIFY版）
行数 22行 34行
ネストの深さ サブクエリ2重 フラット
処理の流れ 内側 → 外側 上 → 下
extract(year/month from ...) の登場回数 6回 2回
相関サブクエリ あり なし
各処理の意図 コードから推測 CTE名＋コメントで明示

行数はむしろ増えています。コメントとCTE名を足したぶん長くなったからです。
それでも読み手の認知負荷は大きく下がっているはずです。
リーダブルなSQLとは、短いSQLではなく、上から順に読めて意図が伝わるSQLなのです。

おわりに

最後まで読んでいただきありがとうございました。

本記事の要点を3行でまとめると、こうです。

  1. 構造を整える ── サブクエリではなく CTE で、上から順に読めるように
  2. 意図を明確にする ── 命名・コメント・マジックナンバー対策で「なぜ」を伝える
  3. 高機能を使い倒す ── ウィンドウ関数・QUALIFY を知っているだけでSQLは劇的にシンプルになる

そして、チームで一貫したスタイルと、フォーマッタによる自動化が、これらを継続させてくれます。

「おい、逆にそれは見づらいだろ！」
「うちはこう書いている方が好き！」
「この関数も便利だよ！」

そんな多様な意見、ぜひコメントでお聞かせください。皆さんの現場のリーダブルなSQLを教えてください。

参考文献

  1. 一文が長すぎる、サブクエリのネストが深すぎる、命名が場当たり的、などで理解に時間がかかるSQL。書いた人を責める気はありません。誰しも一度は通る道です。 ↩

  2. dbt Style Guide の考え方を参考にしています。 ↩

  3. 筆者も LAG を知らずに ROW_NUMBER で自己結合する複雑なSQLを書いてしまったことがあります。便利な関数の存在を知っていることが、リーダブルなSQLへの近道です。 ↩

296
357
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
296
357

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?