分析SQLのコーディングスタイル - クックパッド開発者ブログ

SQL、書いてますか？

こと大規模データ処理の分野においてはSQLはもはや標準インターフェイスであり、分析やらバッチやらに関わっている皆様は日々大量のSQLクエリーを生産していることと思います。

そこでちょっと気になるのが、 SQLのコーディングスタイルってどうするのが一般的なんだっけ……？という点です。イマドキはSQLなんてO/R mapperに吐かせることが多いからなのか、それともコードを広い範囲で共有することがそもそもないからか、 SQLのコーディングスタイルについて見聞きすることは他のプログラミング言語に比べるとだいぶ少なく、いまいち決定版と言えるスタイルがないなと感じています。

そんなわけで本日は、SQLのコーディングスタイルについての意識を活発化させるべく、クックパッドでわたし（青木）が使っているコーディングスタイルから特徴的な点を紹介したいと思います。特に、分析バッチで用いるような巨大なSQLのスタイルについて話します。

なお、この記事では、コーディングスタイルを網羅的に示すことはしません。いまさら「演算子の周囲には空白文字を置く」やら「コードはインデントする」のようにどうでもいい（積極的に抵抗する人がいない）スタイルについてえんえん書くのは時間の無駄でしかありませんし、基本方針がわかりにくくなるからです。意見の分かれやすい、抵抗の大きそうなところに集中して述べていきたいと思います。

サンプルコード

まず、わたしが普段採用しているコーディングスタイルをお見せします。

次のクエリーは、適当にそのへんのファイルからSQLを拾ってきて、適当にテーブル名やカラム名を変えたものです。ある程度の長さがないと事情が理解してもらえないと思うので、まあまあ長めのものにしました。なお、この記事の内容にはクエリーの意味は関係ないので、読む必要はありません。スタイルだけ見てください。

select
    user_id
    , user_session_id
    , min(log_time) as session_start_time
    , max(log_time) as session_end_time
    , count(*) as num_steps
    , max(case session_step when 1 then keywords else null end) as step1
    , max(case session_step when 2 then keywords else null end) as step2
    , max(case session_step when 3 then keywords else null end) as step3
    , max(case session_step when 4 then keywords else null end) as step4
    , max(case session_step when 5 then keywords else null end) as step5
    , max(case session_step when 6 then keywords else null end) as step6
    , max(case session_step when 7 then keywords else null end) as step7
    , max(case session_step when 8 then keywords else null end) as step8
from (
    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
    from (
        select
            user_id
            , sum(session_delta) over (
                  partition by user_id
                  order by log_time
                  rows between unbounded preceding and current row
              ) as user_session_id
            , log_time
            , keywords
        from (
            select
                user_id
                , case
                  when
                    lag(log_time) over (partition by user_id order by log_time) is null
                    or log_time > lag(log_time) over (partition by user_id order by log_time) + interval '00:30'
                  then 1 else 0 end as session_delta
                , log_time
                , trim(word1
                      ||' '|| coalesce(word2,'')
                      ||' '|| coalesce(word3,'')
                      ||' '|| coalesce(word4,'')
                      ||' '|| coalesce(word5,'')
                      ||' '|| coalesce(word6,'')
                  ) as keywords
            from
                activity.search_log
            where
                log_time between timestamp '2015-02-01 00:00:00' and timestamp '2015-02-01 01:00:00'
        )
    )
)
group by user_id, user_session_id
order by user_id, user_session_id
;

このスタイルは会社の先輩が使っていたスタイルが便利だったのでそれをベースに、ウィンドウ関数やcase式のスタイルを追加・改良したものです。ここから主な論点として、次の7点に注目したいと思います。

大文字と小文字の使い分け
カンマの位置
セミコロンの位置
select、from、whereを左右どちらに寄せるか
joinのインデント
インデント幅
標準SQLとの向き合いかた

いかにも炎上しそうな項目から順に述べていきましょう。

1. すべて小文字を使う

SQLのコーディングスタイルについて語るとき、最初にして最大最悪の障害は大文字小文字の使いかたではないでしょうか。

JavaやRubyのように比較的新しいプログラミング言語はあまり大文字を使わない傾向にあります。しかしSQLはメインフレーム全盛の時代から生き残っているだけあって、大文字アルファベット文化がいまなお大きな影響力を持っています。そのため、SQLを古くから書いている人ほど大文字をよく使うように感じています。

大文字小文字のスタイルを大きく分類すると、以下の3つに分けられるでしょう。

すべて大文字
キーワード（予約語）は大文字
すべて小文字

わたしのスタイルは3の「すべて小文字」です。

まずスタイル1ではない理由は簡単です。大昔からSQLを書いている人ならともかく、このサイトを見るような読者層にとってみると、「すべて大文字」というのは、ほぼ誰もやりたくないスタイルと言ってよいでしょう。個人的にも、正直すべて大文字のコードは読みたくありません。よってこのスタイルは最初から検討もしませんでした。

次に2の「キーワードは大文字」について。これはおそらく最大派閥ですが、わたしはこのスタイルは採用しません。

この派閥を支持する意見としては、「大文字のほうが見やすい」という主張をよく見ます。ですがこれは大変怪しい主張です。

「大文字は見やすいからキーワードのように構文上重要な役割を持つ語は大文字である」……と本当に考えるのなら、 JavaやRubyについても同じことを言うべきです。しかしJavaやRubyで予約語が小文字であることに対して文句を言っている人は見たことがありません。

なぜか。小文字で十分見やすいからです。

最近はどんな開発環境だろうともシンタックスハイライトくらいは完備しているでしょうし、わざわざ大文字にせずとも視認性は十分得られます。あえて大文字にする理由は、歴史的な理由以外にはないと思います。

「おまえは今まで使ったSQLのキーワードがいくつあるか覚えているのか？」

しかしもちろん、「キーワードだけ大文字」に見やすさという理由がなかったとしても、歴史的な継続性はあるわけです。少なくともわたしの主張する「すべて小文字」派よりは多少なりとも昔の面影を残しているぶん、古くからSQLを書いている人たちにもアピールするという利点はあるでしょう。

ですが、それでもわたしが「キーワードだけ大文字」を採用しないのは、このルールがあまりにも厄介だからです。

SQLには、他のプログラミング言語に比べて遥かに多くのキーワードがあります。 selectやfrom、join、asなどは当然として、関数名もたいていキーワードなのでcountもmaxもsumもキーワード、ウィンドウ関数のrankもrow_numberもキーワード、rowやunpreceedingもキーワード、 extract関数で使うyearやmonth、dayなどもすべてキーワードです。あまりにもキーワードが多すぎて、たぶん誰もすべてのキーワードを覚えていません。

特にuserやactionのようないかにもよく使いそうな単語がキーワードなのが最悪で、これらが現実的な確率でカラム名として使われてしまいます。そうなったときに、こいつらも大文字にするのか、クオートだけして小文字にしてしまうのか、といったしょーもないことで悩む必要が出てきます。

また、RDBMSごとの特有のキーワードというものも存在します。しかしそういったDB固有のキーワードはエディターがサポートしていないことが多く、 DB固有のキーワードだけは小文字にされてしまう……みたいな悲しい事態に陥ります。 UDF（User-Defined Function）やUDT（User-Defined Type）のような、ユーザーが独自に定義する関数や型も同様の問題を起こします。

具体的にはこんな感じの見ためになるわけです。

SELECT
    "USER"
    , COUNT(user_id)
    , some_udf("USER")
    , EXTRACT(YEAR FROM created_at)

これが、本当に、見やすいのでしょうか？

もう正直気持ち悪い面倒だわ、どれがキーワードだか覚えられんわで、いいことは何一つないように思われます。ここまでやるなら「すべて大文字で書くよ」派のほうがまだ一貫性があって楽かもしれません。

真面目に従えない使い分けルールを決めるくらいだったら、そもそも大文字小文字の使い分けをやめたほうがまだマシです。そして大文字と小文字のどちらかに揃えるとしたら、小文字のほうが幸せになれる人が多いはずです。

2. 改行前後のカンマと演算子は次の行の先頭に置く

次の話題に行きましょう。

カンマや演算子のところで改行する場合は、次の行の先頭にカンマや演算子を置きます。つまりこの部分ですね。

   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

andなどの論理演算子も次のようにすべて先頭に置きます。

where
    hst.user_id is null
    and hst.updated_at is null

このスタイルを選んだ理由は、記述しているカラムや条件のかたまりが最もわかりやすいからです。

最初のコードを見てもらえばわかるように、ウィンドウ関数やcase式が入ってくると、インデントだけではselect文にカラムがいくつ書かれているのかよくわからなくなってきます。しかし不幸にしてSQLではカラムの順序に大変大きな意味があるので、何カラムめに何の値が入っているかというのはかなり重要な情報です。そこでカンマや演算子を先頭に並べて書いておくと、それを数えるだけで項目数がすぐわかるわけです。

カンマを前に置くスタイルについては編集上の利点を挙げる人もいますが、わたしは特にそこは重視していません。あくまでカラムの数と位置のわかりやすさを重視します。

もっとも、演算子はともかくとして前カンマについては、単純に気持ち悪いと感じる人が多いでしょう。正直わたしも最初のうちは「先頭キモいな〜、これ本当に前に書くのかな〜」と思っていたのですが、ちょっと面白かったので試しに3日間書いていたら慣れました。わりと簡単に慣れるので試してみてください。

ちなみに、他のプログラミング言語でも例えばLispのマクロでは前カンマを使いますし、前例がないわけでもありません。プログラミング言語は自然言語ではないのだから、必ずしも自然言語由来の自然さにこだわる必要はないと思っています。

3. 文末のセミコロンは単独行に置く

3つめの話題。文末のセミコロンは単独で、別の行に置きます。つまり次のように書くわけです。

select ...
from ...
where ...
;

これは実はあまり大きな理由はなくて、カンマの前置に揃えているだけです。強いて言うと、psqlなどのコマンドラインクライアントにコピペするときにセミコロンだけ抜いてコピペがしやすい、という利点があります（セミコロンさえ打たなければCtrl-Cでキャンセルできるので、変な失敗が起きにくいのです）。

4. 各句のキーワードはインデントしない

4つめの話題。ちょっとあまりに面倒くさすぎて信じられないのですが、世の中には次のようにキーワードを右寄せにしたがる派閥も存在しています。

select count(*)
  from mst.users
 where user_id = 5;

わたしはsyntax off; set sw=4 ai smd ts=8 etですべてのエディタ設定が完了するオールドタイプ人類なのでさすがにこれに付き合う気はありません。 select、from、where、order byのレベルはすべてインデントを揃えます。

そもそも、この右寄せスタイルの効果があるのはそうとうに短いクエリーだけです。サブクエリーのネスト5段、1 selectごとにカラムが10個……みたいな、分析系ではよくありがちなクエリーには無用の長物でしかありません。むしろ各句の左端が揃わなくなるため、サブクエリーのネストレベルがわかりにくいという欠点が目立ちます。

そして何よりも、キーワード右寄せスタイルは「select for locking access」のとき（あるんですよ世の中にはそういう文が……）に26文字インデントしなければならないのが最悪だと思います。こんなことになるわけです。

select for locking access count(*)
                     from mst.users
                    where user_id = 5
;

カラムの右揃えは空白文字と時間の無駄です。黙って左に揃えましょう。

5. join式はfromよりもインデントする

5つめの話題はfrom句内のインデントについて。

ジョインが存在する場合のインデントも判断の分かれやすいところです。わたしが採用しているスタイルは次のように、joinの記述をfromよりもインデントします。

from
    work.weighted_segments s
    inner join source.factor_score_flg f on s.guest_user_id = f.user_id
    inner join source.factor_score_mst m using (attr_id)

一方で、次のようなインデントスタイルも根強く存在します。

from work.weighted_segments s
inner join source.factor_score_flg f on s.guest_user_id = f.user_id
inner join source.factor_score_mst m using (attr_id)

ですが、このスタイルはわたしに言わせればインデントの役割を完全に放棄しているとしか思えません。

上記の式では3つのテーブルをジョインしていますが、その場合、3つのテーブルをジョインしたリレーション全体がfrom句の値です。 1つめのテーブルだけではありません。ならば、文法の内包構造をインデントに表現しようとした場合、3つのテーブルと「inner join」はすべて「from」より下位にあるべきで、「from」と「inner join」を同列に並べる選択はありえないでしょう。そもそも「from」は句ですが、「inner join」は二項演算子です。その時点で両者は対等ではありません。

「何もインデントしない」スタイルを採用するのでない限り、 from句に含まれるコードはすべてインデントすべきです。

6. インデントとネスト、そしてwith句について

6つめの話題はようやくインデントの深さについてです。わたしのスタイルではSQLのインデントは空白4文字にします。このへんは趣味です。

強いて4スペースにする理由を挙げるなら、バッチではかなりネストの深いサブクエリーを使うことが多いので、 2スペースくらいだとすぐに見分け付かなくてきつい、くらいでしょうか。 4スペースでもよくわからなくなるときがあります。しかし8スペースまで深くすると今度は200桁くらいになってしまうことがあり、さすがに深すぎます。 SQLの場合はCで関数を分けるような気軽さではサブクエリーを分離できないので、回避しにくいことも問題です。

ちなみにサブクエリーの代替になりネストも減っていいじゃんと話題のwith句ですが、わたしは実行プラン（explain）を見るまでは絶対にwith句にはしません。プランナーを信用していないからです。基本的に、RDBMSのプランナーは新しい構文を使うほどよくしくじります。新しい構文は用心してかかるのが得策だと考えます。

またSQLバッチに関して言えば、withを使って見ためのネストだけ下げるよりは、ビューを作ったり、ワークテーブルを追加して対処したほうが見通しがよくなるだろうとも思います。 with句だろうがサブクエリーだろうが途中経過は人間には見えないので、巨大なクエリーでは開発・運用が難しいことに変わりはないからです。

7. 標準SQLのこと、忘れてください……

最後に標準について。

ことSQLに関して言う限り、ポータビリティを考えてANSI標準だけを使うなどという選択は間違いです。少なくとも、現実的ではありません。

そりゃあもちろん、O/R mapperが生成する類のこの程度のクエリーなら問題はないでしょう。

select * from entity_table where id = 230985;

そしてこれ以上に複雑なほとんどすべてのクエリーが標準をはみ出すことを覚悟しなければなりません。

……いや、それはさすがに言い過ぎでした。言葉の綾というやつでした。

ですが例えばPostgreSQLのリファレンスマニュアルから適当な構文のページを開いて、「この構文はPostgreSQL独自拡張です」のフレーズが何回登場するかを数えてみてください。インデックスを張ったとたんに、upsertをしたいと思った瞬間に、ちょっと特殊な制約を付けたいと思ったばかりに、サクッとANSI標準から踏み出すことを覚悟しなければならないのがSQLの現実です。そして、標準の範囲内ですべての処理を書くことよりは、DB固有拡張のほうが必要性が遥かに上です。標準にこだわってDB固有拡張を避けるのは悪手と言わざるをえません。

そもそもポータビリティを考えるという場合、DBを乗り換えることが前提ですが、 DBをプロジェクトの途中で変えることなどまずありません。これはむろん鶏と卵の関係にあります。SQLがまともに標準化されていないからDBを乗り換えることができず、どうせDBを乗り換えることはできないから標準なんてどうでもいいのです。たいへんウンザリします。

各種RDBMSの差がもうちょっと少なくなってもらえないかという気持ちはやまやまですが、いまのところは標準は「使えるときだけ使う」のが現実解でしょう。

おわりに

この記事では、わたしがクックパッドで使っている分析SQLのコーディングスタイルについて、7点に絞ってお話ししました。コーディングスタイルはいつの時代も戦争の引き金になるので、いま大変嫌な予感がしています。みなさんの手元ではどんなスタイルが使われているのか、はてブコメントなどでお寄せいただければ幸いです。