なぜ、分析SQLコーディングスタイルの提唱が必要か
コーディング規約は主に「保守性」「品質」を維持するために求められるルールで、その重要性については周知の通りと考えます。
一方で、SQL、特に分析SQLについては、こういった規約の模範の「答え」がまだ出ていないように見受けられます。
例えばJavascriptであれば、GoogleやAirBnBなど、うまくいっている会社のコーディング規約の転用が可能です。
しかしながら、分析SQLにはそういった事例の公開が少ないのが現状です。
そこで、BigQueryのstandardsqlを前提とし、コーディング規約の最もわかりやすい部分である「コーディングスタイル」について、本記事で提唱します。
本記事は、下記の記事を参考にしています。
BigQueryで読みやすいSQLを書くコツ - たったの３つであなたの意図はもっと伝わる。
分析SQLのコーディングスタイル(Cookpad)
BigQuery公式ドキュメント
Python コードのスタイルガイド
大文字・小文字問題
全文小文字を原則とする
SQLには予約語(キーワード)や関数を大文字で記述する文化があります。
実際に、BigQuery公式もSELECT * FROM hoge AS piyoのように、予約語は大文字です。
しかしながら、(恐れ多くも)今回大文字を「非推奨」とする提案をします。
大文字にする理由は「読みやすい」以外に存じ上げません。
昔のエディタ・コンソール環境では実際に、大文字化することで可読性が上がったのでしょうが、現在はエディタ等も進化し、シンタックスハイライトが当然になっていますので、この利点は小さいです。
一方で、コーディングする中で、いちいちキーワードを大文字化するのは、面倒です。
上記のSELECT文のように、大文字と小文字が入り乱れますので、SHIFTキーを多様することになります。
いちいち、余計なことに思考と小指のリソースが割かれますので、明確に私のチームでは非推奨とします。
例外は下記のような定数の宣言と活用で、他言語の慣習を踏襲して大文字を使うことにします。
create temporary function TARGET_DATE() as (date "2019-12-25")
;
select TARGET_DATE()
;
コメント
考え方
コードと矛盾するコメントは、コメントしないことよりタチが悪いです。
コードを変更した時は、コメントを最新にすることをいつも優先させてください。
コメントの言語に何を使うかは、チームの状況によって異なります。
自分のコードが、「直近1年の間に、日本語を理解できない人がこのプロジェクトに入ることはないだろう」と考えるときには、日本語でコメントを書いてしまって良いでしょう。
そうでない場合は、チーム内外としっかりと議論をして進めるのがよいでしょう。
英語の場合は下記のコーディングルールが参考になります。
コメントは複数の完全な文で書くべきです。
はじめの単語はそれが小文字で始まる変数名などの識別子でない限り、大文字にすべきです。
ブロックコメントは一般的にひとつかそれ以上の段落からなり、段落は複数の完全な文からできています。
そしてそれぞれの文はピリオドで終わります。
コメントが2つ以上の文からなる場合、文の終わりのピリオドの後は、二つスペースを入れるべきです。ただし、最後の文を除きます。
英語を書くときは、Strunk and White スタイルを使いましょう。
単一行(ブロック)コメントには#が便利だが、環境によっては--でもよい。
単一行として下記がサポートされています。
便利なのは1文字で書ける#ですが、下記の通り環境によってはシンタックスハイライトが機能しません。
チームで統一はされたほうがいいと思いますが、レガシーな環境や、不特定多数の環境でコードが読まれうる場合は--を使うのが無難でしょう。
# This is a single-line comment.
select book from library
;
-- This is a single-line comment.
select book from library
;
/* This is a single-line comment. */
select book from library
;
インラインコメント
インラインコメントは、文と同じ行に書くコメントです。
文とインラインコメントの間は、少なくとも二つのスペースを置くのがよいでしょう。
インラインコメントは控えめに使いましょう。
自明なことを述べている場合、インラインコメントは不要ですし、邪魔です。
次のようなことはしないでください:
select date "2019-12-25" -- 2019年の12月25日を指定する
;
しかし次のように、役に立つ場合もあります:
select date "2019-12-25" -- 2019年のクリスマスについてのみ実行する
;
複数行コメントは/* */
複数行コメントは/* */を使うのが良いでしょう。
このとき、可読性を鑑みて、行の頭には文字の高さが合うよう、スペースでインデントなどを入れるのが良いでしょう。
select book from library
/* This is a multiline comment
on two lines. */
where book = "Ulysses"
;
/* hogeに関する月初作業用クエリ。
* 作業手順 -> [document url]
*/
select book from library
;
コードのレイアウト
終了セミコロンは、最終行に単独で
複数のステートメントを含むリクエストでは、各ステートメントをセミコロンで区切る必要がありますが、最終ステートメントでのセミコロンはオプションです。(一部の対話型ツールでは、ステートメントに終了セミコロンを使用する必要があります。)
しなしながら、単一ステートメントを組み合わせてコピペし、複数ステートメントにするときや、終了セミコロンが必要なツールもありますので[要出典]、終了セミコロンは記述するほうが良いでしょう。
その場所については、明示感が出るように、最終行に単独で記述するのがおすすめです。
コピペする際に、セミコロンを含む/含まないの選択をしやすいというメリットもあります。
カンマ,や、andなどの論理演算子の位置は、文頭
最も議論がわかれるポイントですが、文頭に置くのが優秀と考えます。
- SELECT文内のカラムごとの塊がわかりやすい
- どういう関係で列挙してるのかという重要な情報を文頭で明示できる
1.SELECT文内のカラムごとの塊がわかりやすい
下記のクエリをまず見てください。(Cookpadのブログから引用しています。)
select
user_id
, user_session_id
, row_number() over (
partition by user_id, user_session_id
order by log_time
) as session_step
, log_time
, keywords
ウィンドウ関数やcase式が入ってくると、 インデントだけではselect文にカラムがいくつ書かれているのかよくわからなくなってきます。
2.どういう関係で列挙してるのかという重要な情報を文頭で明示できる
これは条件演算子に関するメリットです。
当然ですが、条件のand/orには雲泥の差があります。
語るよりも、見たほうが早いでしょう。
-- これは条件演算子を認知しにくい。
user_id is not null and
registered_at is null
-- これなら認知しやすい。
user_id is not null
and registered_at is null
改行は、意味のあるまとまりで
意味のあるまとまりで改行をしましょう。
また、1行の長さは79文字以下とするなど、ルールを設定しましょう。
:
-- 意味のあるまとまりで改行する。
select book
from library
where book = "Ulysses"
;
-- シンプルな操作で、1行の文字数が少ないときは1行でも良い。
select book from library
;
:
-- 杓子定規に、不要な改行をしない。
select
book
from
library
;
-- 複数の意味を持つ文を、むりやり1行に詰め込まない。
select book from library
where book = "Ulysses"
;
インデント
1レベルインデントするごとに、スペースを2つ使いましょう。
これはBigQuery公式リファレンスの標準的スタイルです。
行を継続する場合は、折り返された要素を縦に揃えるようにすべきです。
：
select
title
, num_of_pages
from library
;
-- 要素の縦揃え。
select
title
, if(num_of_pages > 50
, "しんどい"
, "気軽"
) as readability
from library
;
-- case文の揃え方の例1：
select
title
, case
when num_of_pages > 200 then "超しんどい"
when num_of_pages > 50 then "しんどい"
else "気軽"
end as readability
from library
;
-- case文の揃え方の例2：
select
title
, case
when num_of_pages > 200 and language = "English"
then "超しんどい"
when num_of_pages > 50
then "しんどい"
else "気軽"
end as readability
from library
;
select
calendar.date
from
calendar
left join special_holidays sh
on calendar.date = sh.date
where
calendar.week in ("sat", "sun")
or sh.date is not null
：
select
calendar.date
from
calendar
left join special_holidays sh -- NG：joinの高さはfrom句から1インデント下げる。
on calendar.date = sh.date
where
calendar.week in ("sat", "sun")
or sh.date is not null
命名規則
文字
関数・変数の名前は小文字のみにすべきです。
また、読みやすくするために、必要に応じて単語をアンダースコアで区切るべきです。
(いわゆるsnake_cakeを推奨します。)
ただし、定数については、他言語の慣習を鑑みて認知負荷を下げるためにすべて大文字でSNAKE_CASEのように書きます。
中間テーブル名
中間テーブルの名称はしっかりと付けましょう。
正規化されていないことが多いため、えてして長くなってしまいますが、それは後述エイリアス名で上手にさばきます。
エイリアス名
from句のエイリアスは、原則使わない方向で考えます。
例えば、よくある命名規則として、テーブル名に使われる単語の1文字目を連結するものがあります。
例えば、user_premium_historiesであれば、uphといったものです。
これを杓子定規に採用すると、userもuと省略することになるのですが、これはむしろ可読性を下げます。
とはいえ、最終的な各テーブルの呼称は8文字以内に収めたいところです。
踏まえて下記のような命名規則の第1原則を提案します。
第1原則
- テーブル名が8文字以下の場合は、テーブル名をそのまま使う。
- テーブル名が8文字を超える場合は、エイリアスを貼る。
次に、エイリアスの命名規則です。
特に、中間テーブルなど正規化されていないテーブルに、下記のような命名の課題があります。
- 情報量が多くテーブル名が長くなりがち
- 一般的に使われているテーブルでないので、単語1文字目連結のルールだといまいちピンとこない
そこで、次に第2, 第3原則を提案します。
第2原則
- コード閲覧者全員が、一般的に使っているテーブルについては、テーブル名に使われる単語の1文字目を連結した表記を使う。
- チーム内でも、そのテーブルについて会話するときはあえて積極的にその省略名を使う
(それにより、コンテクスト化が進むが、会話もコードも適度に圧縮される)
- ただし、コンテクスト化を避けるために第2原則は無視しても良い。
第3原則
- 一般的でないテーブルについては、当該SQL内でのコンテクストをもとに命名する
- joinの基礎になるテーブルについては"base"と命名する
- baseに情報を付加するものについては"hoge_info"と命名する
(プレミアム利用に関する情報であれば、premium_infoなど)
とくに第3原則については、他社にて運用されている例を聞いたことがない、現状「オレオレ」ルールです。
しかしながら、monthly_latest_user_premium_detailsといった中間テーブルにmlupdと命名するよりかはpremium_infoと命名するほうが、認知負荷は低いのではないでしょうか？
サブクエリ・With句
6行以上のサブクエリは避け、使う場合もサブクエリの結果を自然言語でコメントする。
BigQueryには、With句という仮想的な？テンポラリテーブルを作成する文法があります。
With句によるテンポラリテーブルの長所は下記のとおりです。
- テンポラリテーブルを予め作ることで、長いクエリを親クエリの中に書かなくて済む。
- テンポラリテーブルは命名されるので、どんなテーブルであるかコメントせずに済む。
その中、サブクエリは認知負荷が高いのでなるべく避けるべきです。
特に、サブクエリ内のサブクエリや、6行以上のサブクエリは原則的に使わず、With句を使いましょう。
5行以下の短いシンプルな記述については、むしろサブクエリで書いたほうが読みやすくなることが多いため、むしろサブクエリを推奨します。
ただし、その場合も、上記2のメリットを擬似的に再現するために、しっかりと自然言語でどんなテーブルであるかをコメントすることが認知負荷を下げるでしょう。
select id
from user
where
created_date in(
select
calendar.date
from
calendar
left join special_holidays sh
on calendar.date = sh.date
where
calendar.week in ("sat", "sun")
or sh.date is not null
)
;
select id
from user
where
created_date in(
-- 休日リスト
select date
from calendar
where calendar.is_holiday
)
;
with
holidays as (
select
calendar.date
from
calendar
left join special_holidays sh
on calendar.date = sh.date
where
calendar.week in ("sat", "sun")
or sh.date is not null
)
select id
from user
where
created_date in (
select date from holidays
)
;
文字列に含まれる引用符
文字列は基本的に二重引用符""でくくる
BigQueryでは、単一引用符'で囲まれた文字列と、二重引用符"で囲まれた文字列は同じですが、""の利用が無難です。
これは、一部の頻出関数、具体的にはformat()などにおいて、単一引用符'を活用したフォーマット指定が発生するからです。
123,456,789に見られるような、カンマ区切り整数のフォーマット表現は"%'d"です。
これは一重引用符を文字列の表現に使っているとうまく実行されません。
逆に言うと、これ以外のデメリットも無いのですが、format("%'d", hoge)したいケースはそこそこありますので、二重引用符""を使うことが無難でしょう。
最後に
上記を守った開発をすれば「保守性」「品質」の一定の向上が見られるはずです。
しかしながら、柔軟な運用も求められます。以下、Pythonコーディング規約からの引用です。
用法用量を正しく守って、本ガイドラインを活用ください。
一貫性にこだわりすぎるのは、狭い心の現れである
Guido の重要な洞察のひとつに、コードは書くよりも読まれることの方が多い、というものがあります。この文書で示すガイドラインの目的は、コードを読みやすくするとともに、Pythonで書かれた幅広いコードのスタイルを一貫させることです。PEP 20 にもあるように "可読性重要" です。
スタイルガイドは一貫性に関するものです。このスタイルガイドに合わせることは重要ですが、プロジェクトの中で一貫性を保つことはもっと重要です。一番重要なのは、特定のモジュールや関数の中で一貫性を保つことです。
しかし、一貫性を崩すべき場合があることも知っておいてください - つまり、このスタイルガイドが適用されない場合があります。疑問に思ったときは、あなたの判断を優先してください。他の例を調べ、一番良さそうなものを決めて下さい。そして、躊躇せずに質問して下さい！
特に、このPEPに準拠するためにコードの後方互換性を壊すようなことは絶対にしないで下さい！
この他に、特定のガイドラインを無視する正当な理由がいくつか考えられます:
- ガイドラインに従うとコードが読みにくくなること。このPEPに準拠したコードを読んでいた人にとっても読みにくくなったのならなおさらです。
- (多分歴史的な理由で) ガイドラインに従っていない周囲のコードと一貫性を保つため -- しかし、これは誰かの汚いコードを綺麗にするチャンスでもあります。
- 問題になっているコードが、ガイドラインが出てくるより前に書かれたもので、 それに準拠させること以外にコードを変更する理由がないとき。
- スタイルガイドで推奨されている機能をサポートしていない古いバージョンの Python と互換性を保つ必要がある場合。