データ戦略室で使用しているSQLのコーディング規約をご紹介します

はじめに

こんにちは。データ戦略室の森下です。今回はデータを使う際に切っても切り離せないSQLについて、レバレジーズのデータ戦略室で作成および運用しているコーディング規約についてお話します。レバレジーズのデータ戦略室では今回ご紹介する規約を策定していますが、データ活用のフェーズや環境によって多分に変わる部分でもありますので、とある一社の運用方法として参考にしていただければ幸いです。

レバレジーズのSQL実行環境について

Google BigQueryを初めとするカラムナデータベースにデータを格納し、SQLを用いてデータを抽出することが多いです。そのため、今回ご紹介するSQLコーディング規約には、RDBに対するパフォーマンスチューニングに関連するようなことは規約に入っていません。

コーディング規約について

コーディング規約は、「必ず守ってほしい項目」と「可能であれば守ってほしい項目」に分かれています。レバレジーズでは、RDB/MAツール/システムログ情報など、多数のデータソースからデータを収集し、データ活用基盤にインポートしています。全てテーブル構造のデータになるとはいえ、全てのデータソースに対して一律の規定を策定することは現実的ではありません。また、セールスやマーケターといったデータ戦略室以外のメンバーもたくさんクエリを作成し実行する環境なので、ある程度の任意項目を設定しつつ、コードの品質が一定以上に保たれるようにしています。規約自体は全部で20項目ほどあるのですが、今回はそこから特徴的な部分を選択して簡単に説明を記載していきます。

必ず守ってほしい項目

・予約語は全て小文字にする
・予約語よりカラム名/テーブル名を1つインデントする
・select, from, where, order by, group by, having 等はインデントを揃える
・join はテーブル名と同じインデントにする
・select * は基本的に禁止
・改行前後のカンマは次の行の先頭に置く

可能であれば守ってほしい項目

・with を使用して、サブクエリの使用は避ける

[必須]予約語は全て小文字にする

SQLの書き方で最も差が出る部分ではないでしょうか。SQLキーワードや予約語のみ大文字といったスタイルも一般的かと思います。大文字にするのは視認性向上が主な理由だと思いますが、弊社のSQL実行環境にはシンタックスハイライトがありますし、インデントをちゃんと揃えることで視認性は十分確保できます。視認性に大きな差がない以上、わざわざ大文字にするのも面倒なので、データ戦略室では予約語含めて全てを小文字統一にしています。加えて、あらゆる職種がSQLを書くレバレジーズの環境では、「特定のキーワードのみ大文字」より「あらゆるキーワードを小文字」のほうが運用しやすい、というのも理由として挙げられます。

[必須]予約語よりカラム名/テーブル名を1つインデントする

[必須]select, from, where, order by, group by, having 等はインデントを揃える

これら2つは視認性向上のために設定しました。SQLは非エンジニアでも扱いやすく、実際にレバレジーズでは多くのセールスやマーケターも自身でSQLを書いてデータを抽出しています。そういった環境なこともあり、SQLコーディング規約策定前はそれぞれが読みやすいように書いていたため、インデントがバラバラで流用性が低いSQLとなっていました。インデントは意識すれば誰でも修正可能ですし、インデントが揃っていることにより可読性は大きく向上するため、インデントはやや厳し目に規約に盛り込みました。

[必須]join はテーブル名と同じインデントにする

join は from に包含される関係だと考え、from の最初のテーブルと同じインデントしています。これによって、どこからどこまでが処理に使用しているテーブル郡なのかを一目で把握することができます。SQLのデータソースとなるテーブル一覧を一目で把握するためにも、join は from よりもインデントすることで可読性が向上すると思います。

from 
  all_log_data 
  left join temp_search_log
      on all_log_data.userid = temp_search_log.userid
      and all_log_data.user_session_id = temp_search_log.user_session_id
  left join temp_activity_log
      on temp_search_log.userid = temp_activity_log.userid
      and temp_search_log.user_session_id = temp_activity_log.user_session_id
      and temp_search_log.keywords = temp_activity_log.keywords
where
  …

[必須]select * は禁止

不要な事故を防ぐために select * の使用は禁止にしています。これは、「実行しないとどのカラムを選択しているのかがわからずコードレビューがやりづらいこと」「知らず知らずのうちに不要なカラムを取得してしまうこと」が理由に挙げられます。実装時はつい楽をしがちで select * を使いがちですが、SQLも他のプログラミング言語と同じく読む時間の方が長いことから、可読性を重視してこの規約を入れています。

[必須]改行前後のカンマは次の行の先頭に置く

Pythonの一般的なコーディング規約である PEP 8 によると、「カンマは末尾につけましょう」となっていますが、SQLは前につけるように規約で規定しています。SQLにおけるselect 文は処理結果がいくつのカラム数になるのかが非常に大事な情報だと考え、前カンマにすることでカンマの数を数えるだけでカラム数を把握することができます。また、case 文や window関数を使用した際に、select 句の中で改行したりインデントしたりすると思いますが、その際に前カンマでインデントが揃っているほうが視認性が高くなります。加えて、作業中にカラム名をコピペする場合、後ろカンマだとselect 句の最後はカンマを削除する必要があるのに対し、前カンマならカラム名を変更するだけで済みます。データマート作成といった業務のなかで多くの時間をSQL作成に費やす場合、地味ですが作業のしやすさが全然違うと思います。

[任意]with を使用して、サブクエリの使用は避ける

サブクエリは複雑化すると読みづらくなるので、長いサブクエリを避けた上で可読性が向上してほしいと思っています。ただ、サブクエリを任意のid のみに絞る、といった使い方をする場合、わざわざwith句を使用するほどでもないかなと思います。このあたりはパッと見た時にどういう処理なのかが分かればいいので、あまり厳密に規約として規定するほどでもないかなと考え、任意項目にしています。

【例】

select
  name
  ,address
from
  users
where
  id in (
    select
      user_id
    from
      access_log
)

おわりに

今回はレバレジーズのデータ戦略室で運用されているSQLのコーディング規約についてお話ししました。SQLは非エンジニアでも扱いやすいことから、多くの企業で様々な職種の方に使用されているかと思います。そのため、人によって書き方が異なることが多いので、ある程度社内でSQLを扱える人が増えてきた段階で、組織の環境やユーザーのレベル感を考慮して規約を作ってみるとよいと思います。SQLコーディング規約は、現場に導入され、運用されて初めて効力を発揮するので、弊社のケースが参考になれば幸いです。