コーディング規約を定めて SQL の可読性を上げる

Da Vinci Studio データ分析チームの濱口です。
現在データ分析チームで運用されている SQL のコーディング規約について紹介します。

なぜコーディング規約を定めるのか

SQL のコーディング規約を定める主な理由として、可読性を上げるというのがあると思います。スタイルを統一せず、書き手によって異なるスタイルで書いていると以下のような弊害が発生します。

レビューしづらい

他の人が書いた別の作業の SQL というだけでも読みづらいですが、コーディングスタイルが揃っていないとさらに読みづらくなってしまいます。そうするとレビューにも時間がかかってしまい、他の作業に割ける時間が短くなってしまいます。

再利用しづらい

Da Vinci Studio の分析チームでは、過去に書いたクエリを GitHub のリポジトリに貯めており、他の人も再利用できるようにしています。しかし SQL が読みづらいとバグも発生しやすくなり、かえって作業効率を悪くしてしまいます。

どんな SQL に適用すべきか

他の人が読む可能性がある SQL は全て、定めたコーディング規約に則って書くことにしました。具体的には

他チームからの依頼の SQL
アプリケーション内で利用される SQL

などを指します。個々人のアドホックな分析に関しては、必ずしもスタイルガイドに沿わなくてもいいというルールにしました。

コーディング規約

以下で定めているコーディング規約をいくつか紹介します。
なお、今回は分析チームで主に利用されている BigQuery の利用を前提としたコーディング規約になっています。

予約語は小文字で書く

# NG
SELECT
  name,
  comment,
FROM SAMPLE.table
WHERE
  name = 'name1'

# OK
select
  name,
  comment,
from SAMPLE.table
where
  name = 'name1'

SQL の予約語は大文字で書く文化が広く浸透しており、大文字で書く人はかなり多いかと思います。しかし上記を踏まえた上でも、小文字で書く方が良いと思った理由として

予約語だけ大文字で書くのめんどくさい
予約語だけ大文字で書いても可読性は変わらない

の2点でした。可読性が変わらないのであれば、予約語だけ大文字で書くメリットはないという結論に至りました。

select 句のカンマの位置は行末

# NG
select
  name
  , comment
  , date
from SAMPLE.table

# OK
select
  name,
  comment,
  date,
from SAMPLE.table

このルールは可読性というよりは、試行錯誤の効率の観点で決められました。

行頭にカンマを書くと、一番上のカラムをコメントアウトの際にエラーになってしまう
BigQuery は select 句の最後の行にカンマがついていてもエラーにならないので、一番最後の行をコメントする際もカンマの削除が不要

の2点が主な理由です。より少ない労力で各行をコメントできる書き方を採用しました。

where 句の and と or は先頭に置く

# NG
select
  name,
  comment,
from SAMPLE.table
where
  date between date('2021-04-27') and date('2021-05-05') and
  name = 'name1' or
  comment = 'comment1'

# OK
select
  name,
  comment,
from SAMPLE.table
where
  date between date('2021-04-27') and date('2021-05-05')
  and name = 'name1'
  or comment = 'comment1'

条件が複雑で長くなっている際に and や or が行末についていると見づらいというのがありました。先頭に書いておけば、条件のかたまりの切れ目が分かりやすく良いのではという議論がありました。

サブクエリではなく、 with 句を使う

# NG
select
  name,
  sum_price
from (
  select
    name,
    sum(price) sum_price,
  from SAMPLE.table
  where
    date between date('2021-04-27') and date('2021-05-05')
  group by name
)
where
  sum_price >= 10000

# OK
with
  t_base as (
    select
      name,
      sum(price) sum_price,
    from SAMPLE.table
    where
      date between date('2021-04-27') and date('2021-05-05')
    group by name
  )
select
  name,
  sum_price
from t_base
where sum_price >= 10000

主な理由としては

一つのクエリが大きくなりすぎない
ネストが深くなりすぎない
同じクエリを何度も再利用できる

の3点が挙がりました。可読性だけでなく、再利用性の観点からも with 句を使うようにしています。

ビフォーアフター

以下、全ての規約を適用したときのビフォーアフターの一例です。

# 適用前
SELECT
  date,
  target_name,
  SUM(price) sum_price,
  AVG(price) avg_price,
FROM (
  SELECT
    date,
    CASE
      WHEN regexp_contains(name, r'regexp1') THEN 'name1'
      WHEN regexp_contains(name, r'regexp2') THEN 'name2'
    ELSE null
  END target_name,
    price,
  FROM DataBase.table1
  WHERE date BETWEEN DATE('2021-01-01') AND DATE('2021-01-31'))
WHERE target_name IS NOT NULL
GROUP BY date, target_name
GROUP BY date, target_name

# 適用後
with
  t_base as (
    select
      date,
      case
        when regexp_contains(name, r'regexp1') then 'name1'
        when regexp_contains(name, r'regexp2') then 'name2'
        else null
      end target_name,
      price,
    from DataBase.table1
    where date between date('2021-01-01') and date('2021-01-31')
  )
select
  date,
  target_name,
  sum(price) sum_price,
  avg(price) avg_price,
from t_base
where target_name is not null
group by date, target_name
order by date, target_name

紹介した規約以外にも、インデントに関する規約などが適用されており、読みやすくなりました。規約通りにかけているかのチェックは、現状は目視で確認しています。最低限レビューや後で読み返すときに困らない程度の可読性を保てたら良いので、そこまで厳密にはチェックしていません。

まとめ

分析チームで運用している SQL のコーディング規約についてまとめました。カラム名や with 句における命名規約なども定めていますが、それはまた別の記事で紹介しようと思います。
このコーディング規約はあくまでも一例で、状況によって規約の内容や運用方法は変わると思います。その時のチームの課題や状況に応じてアップデートしていきたいと思います。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大募集中です！募集職種と詳細に関しては、以下のリンクからそれぞれ確認できます。

Da Vinci Studio Blog

株式会社 Da Vinci Studio は 2023 年 7 月に株式会社 Zaim と統合し、株式会社くふう AI スタジオになりました