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

jaruuu(Ryosuke Hamaguchi)

2022年2月7日 10:19

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

コーディング規約を定める理由

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

レビューしづらい

他の人が書いた別の作業の SQL というだけでも、読みづらいものです。その上コーディングスタイルが揃っていないと、さらに理解に時間がかかります。

結果、他の作業に割ける時間が短くなるというデメリットが生じてしまいました。

再利用しづらい

分析チームでは過去に書いたクエリを 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

紹介した規約以外にも、インデントに関する規約などを適用しています。

規約通りに書けているかは、現状は目視で確認しています。最低限、レビューや後で読み返すときに困らない程度の可読性を保てたら良いので、そこまで厳密にはチェックしていません。

まとめ

以上、Zaim の分析チームで運用している SQL のコーディング規約についてまとめました。カラム名や with 句の命名規約なども定めていますが、今回は割愛しています。

このコーディング規約はあくまでも一例で、状況によって規約の内容や運用方法は変わるでしょう。その時のチームの課題や状況に応じてアップデートしていきたいと思います。

最後に

他のチームと協力しながら働ける Zaim では、一緒に働けるエンジニアを募集しています！

仕事を通じて色々なスキルを得たいという方は、ぜひ一度カジュアルにオンラインでお話しましょう。ご連絡、お待ちしてます。