Posted at
LivesenseDay 13

SQLスタイルガイド

More than 3 years have passed since last update.


はじめに

本文書はSQLのスタイルガイドです。

PythonやRubyのようなプログラミング言語には有名なスタイルガイドがあり、共通のレイアウトルールに沿ったインデントや命名規則に則ったコードが生み出されています。

一方、SQLには知名度のある統一されたスタイルガイドがありません。

そのため、思いのままに書かれたSQLが散見されます。

もちろん、SQLを使ってアドホックな分析を行う場合は、必ずしもルールに沿う必要はなく、効率よく書いても良いと思います。

しかし、Webサービスやバッチの中に組み込むようなもの、つまり自分以外の誰かに読まれるSQLは、多くのプログラミング言語同様に何らかのスタイルガイドに沿うことで多くのメリットを享受できると思います。


  • クエリが構造化され、可読性が高まる

  • バグの発見や修正が容易になる

  • 改行位置やインデントなどのフォーマットの悩みが解消される

  • スタイルガイドを共有することで、他人のSQLが読みやすくなる

スタイルは、しばしば好みの問題とぶつかります。

今回提示するスタイルガイドを試してみて、取捨選択しながら、自分にあったスタイルを模索する一助となれたら幸いです。

なお本文書は、SQLのなかでもSELECT文を主な対象として記載しています。


ツールによるフォーマット

スタイルガイドは、それだけを読み込んで実践することが難しく、

ツールによる補助が必要になると思います。

このスタイルガイドにおおよそ沿ったフォーマット・ツールは、0xDBE(JetBrains)です。

メニューから、Code -> Reformat Codeを指定することでSQLを整えてくれます。

ただし、CASE文などの一部の構文のインデントは、今回のスタイルガイドと異なります。

SELECT

CASE
WHEN DATE(created_at) = DATE(NOW())
THEN 'today'
WHEN DATE(created_at) = DATE(NOW()) - INTERVAL 1 day
THEN 'yesterday'
ELSE 'other_day'
END AS date_category,
COUNT(*) AS post_count
FROM
microposts
;


フォーマット

SQLを読みやすくするためのインデントや記法に関するガイドです。


予約語/関数は大文字にする

予約語: SQLで予め使うことが決まっている言葉です

ex) SELECT, FROM, WHERE

予約語/関数を大文字にすることで、SQLで決められている構文なのか、

テーブル名、カラム名など、ユーザが指定した項目なのかが一目瞭然です。

シンタックスハイライトがない環境で特に効果を発揮します。

-- 良い例

SELECT
COUNT(*) AS sushi_post_count
FROM
microposts
WHERE
content LIKE '%:sushi:%'
;

-- 悪い例
select
count(*) as sushi_post_count
from
microposts
where
content like '%:sushi:%'
;

SQLは、ANSI及びISO/IECによって標準規格が決められており、予約語も定義されています。

(仕様書は有料でダウンロードできます。)

しかし、原状ではデータベースによって実装状況が異なっていたり、独自関数によって予約語が追加がされていたりします。

予約語かどうか不明な場合は、各データベースの仕様を確認してみてください。


2スペースでインデントを揃える

インデントには様々な宗派があると思いますが、

半角スペース2つでのインデントを推奨します。

SQLは、Pythonのようにインデントでブロックを示しているわけではありません、

構文構造がわかればよいため半角スペース2つで十分だと思います。

-- 良い例

SELECT
COUNT(*) AS post_count
FROM
posts
WHERE
created_at >= '2015-12-01'
;

-- 悪い例
SELECT
COUNT(*) AS post_count
FROM
posts
WHERE
created_at >= '2015-12-01'
;


SQLの特定の予約語の後は改行する

全ての予約語ではなく、SQLの構文構造を決める予約語を対象とします。

基本的には以下の予約語です

SELECT

FROM
WHERE
GROUP BY
ORDER BY

SELECT句やWHERE句の中で利用される関数(COUNTやBETWEENなど)については、

同じ予約語でも改行の必要はありません。 改行が増えすぎて逆に読みづらくなります。

-- 良い例

SELECT
DATE(created_at) AS created_date,
COUNT(*) AS post_count
FROM
posts
WHERE
created_at BETWEEN '2015-12-01' AND '2015-12-10'
GROUP BY
created_date
;

-- 悪い例
SELECT DATE(created_at) AS created_date,
COUNT(*)
AS post_count
FROM posts
WHERE created_at BETWEEN
'2015-12-01' AND
'2015-12-10'
GROUP BY created_date
;


1カラム1行にする

SELECT、WHERE、WHEREで指定するカラムや条件式は、

1行に1つまでとします。

1行に詰め込み過ぎると、ASがどこにかかっているのか、関数のカッコがどこに対応しているのかが読みづらくなります。

-- 良い例

SELECT
DATE(created_at) AS created_date,
user_id,
COUNT(*) AS post_count
FROM
posts
WHERE
created_at BETWEEN '2015-12-01' AND '2015-12-10'
GROUP BY
created_date,
user_id
;

-- 悪い例
SELECT
DATE(created_at) AS created_date, user_id, COUNT(*) AS post_count
FROM
posts
WHERE
created_at BETWEEN '2015-12-01' AND '2015-12-10'
GROUP BY
created_date, user_id
;


条件のAND / ORは先頭に置く

AND / ORが先頭にあると、今読んでいる行がANDとORのどちらなのかが、

ぱっと見でわかるため先頭とします。

-- 良い例

SELECT
COUNT(*) AS user_count
FROM
user
WHERE
created_at >= '2015-12-01'
AND status IN ('signup', 'available')
AND admin IS NULL
;

-- 悪い例
SELECT
COUNT(*) AS user_count
FROM
user
WHERE
created_at >= '2015-12-01' AND
status IN ('signup', 'available') AND
admin IS NULL
;


サブクエリはWITHを使って分割する

ネストの深いSQLは理解しにくいので、できるだけネストを浅く保ちます。

SQLでは特にサブクエリでネストが深くなりがちです。

WITHによってサブクエリ部分を取り出すことができます。

WITHはカンマで区切って、複数のクエリを書くことが可能です。

なお、MySQLにはWITHが実装されていません。

WITH user_post_count AS (

SELECT
user_id,
COUNT(*) post_count
FROM
microposts
GROUP BY
user_id
)

SELECT
post_count,
COUNT(*) AS freq
FROM
user_post_count
GROUP BY
post_count
;


サブクエリは字下げする

サブクエリのインデントを下げることで、

どこからどこまでがサブクエリになっているかを判別しやすくなります。

ただし、ネストは深くなってしまうため、サブクエリはできるだけWITHを使って書くことを推奨します。

-- 良い例

SELECT
post_count,
COUNT(*) AS freq
FROM
(
SELECT
user_id,
COUNT(*) post_count
FROM
microposts
GROUP BY
user_id
)
GROUP BY
post_count
;

-- 悪い例
SELECT
post_count,
COUNT(*) AS freq
FROM
(
SELECT
user_id,
COUNT(*) post_count
FROM
microposts
GROUP BY
user_id
)
GROUP BY
post_count
;


CASE文は各予約語の前で字下げして改行する

CASEとENDをカッコ()に見立てて、それぞれ字下げを行います。

どこからどこまでがCASE文なのか、条件式はどこまでかをわかりやすくするために、改行・字下げをします。

CASE

WHEN hoge
THEN 'hoge'
WHEN piyo
THEN 'piyo'
ELSE 'other'
END

-- 良い例

SELECT
CASE
WHEN DATE(created_at) = DATE(NOW())
THEN 'today'
WHEN DATE(created_at) = DATE(NOW()) - INTERVAL 1 day
THEN 'yesterday'
ELSE 'other_days'
END AS created_date_category,
COUNT(*) AS post_count
FROM
microposts
;

-- 悪い例
SELECT
CASE
WHEN DATE(created_at) = DATE(NOW()) THEN 'today'
WHEN DATE(created_at) = DATE(NOW()) - INTERVAL 1 day THEN 'yesterday'
ELSE 'other_days'
END AS created_date_category,
COUNT(*) AS post_count
FROM
microposts
;


JOIN句は字下げをして、ONは更に字下げをする

JOIN句を字下げせず、ONだけ字下げするようにします。

JOINの書き方は様々な流派があるようです。

-- 良い例

SELECT
COUNT(*) AS post_count
FROM
microposts AS m
INNER JOIN users AS u
ON m.user_id = u.id
WHERE
u.status IN ('signup', 'available')
;

-- 悪い例
SELECT
COUNT(*) AS post_count
FROM
microposts AS m
INNER JOIN users AS u
ON m.user_id = u.id
WHERE
u.status IN ('signup', 'available')
;


セミコロン( ; )は最終行に置く

複数のSQLを並べて書く場合に効力を発揮します。

どこで SQLが終わっているかがわかりやすくなります。

-- 良い例

SELECT
COUNT(*) AS sushi_beer_post_count
FROM
microposts
WHERE
DATE(created_at) = DATE(UTC_TIMESTAMP())
AND content REGEXP(':(sushi|beer):')
;

-- 悪い例
SELECT
COUNT(*) AS sushi_beer_post_count
FROM
microposts
WHERE
DATE(created_at) = DATE(UTC_TIMESTAMP())
AND content REGEXP(':(sushi|beer):');


命名規約

SQLではASを使って別の名前をつけることができます。

その際の命名ルールです。


統一した接頭辞(prefix) / 接尾辞(suffix)を使う


接頭辞例

接頭辞
利用時

min_
MIN()の取得カラム

max_
MAX()の取得カラム


接尾辞例

接尾辞
利用時

_id
primary keyなどのidカラム

_status
フラグなどの何らかのステータス

_total
SUM()の取得カラム

_num
数値を含むカラム

_name
何かの名前を意味するカラム

_seq
連続した値を含むカラム

_date
日付に関するカラム

_count
COUNT()の取得カラム

_size
大きさを表すカラム

_addr
住所など

参考:

http://www.sqlstyle.guide/#uniform-suffixes


SELECT文でCASE, COUNTなどの関数を使ったカラムは別名をつける

別名を付けないと、カラム名が「COUNT(*)」等になっていて、

何をカウントしたかがわからなくなっていたりします。

-- 良い例

SELECT
DATE(created_at) AS created_date,
COUNT(DISTINCT user_id) AS uniq_user_count,
COUNT(*) AS post_count
FROM
microposts
GROUP BY
created_date
;

-- 悪い例
SELECT
DATE(created_at),
COUNT(DISTINCT user_id),
COUNT(*)
FROM
microposts
GROUP BY
DATE(created_at)
;


サブクエリにはASで名前をつける


機械的な命名方法


  1. 名前は、テーブル名の単語から、頭文字をそれぞれとる

  2. 名前がかぶってしまう場合は、数字を後ろにつける

多くのテーブルをJOINする際に、A,Bのようにアルファベット順で名前をつけているSQLを見かけますが、どのテーブルのJOINなのかがわかりません。

とはいえ、システムが大きくなるほどuser_microposts_relationテーブルのように、テーブル名が長くなって、SQLが冗長になります。

2つの問題を解消するために、上記のような機械的な名前の付け方をすることで、問題が緩和されます。

-- 良い例

SELECT
COUNT(*) AS post_count
FROM
microposts AS m
INNER JOIN users AS u
ON m.user_id = u.id
WHERE
u.status IN ('signup', 'available')
;

-- 悪い例
SELECT
COUNT(*) AS post_count
FROM
microposts AS A
INNER JOIN users AS B
ON A.user_id = B.id
WHERE
B.status IN ('signup', 'available')
;


参考リンク