LoginSignup
10
11

More than 3 years have passed since last update.

@msdtakashi(MASUDA Takshi)

データ分析のためのSQLコード規約を考えた。

Last updated at Posted at 2020-04-18

データ分析のためのSQLコード規約を考えた理由

  • 一般的なSQLのコード規約は業務システムなどのRDBで処理することを前提に考えられており、データ分析のSQLとは違うのでデータ分析のためのSQLコード規約がなかった。
  • 書き方が比較的自由且つ、非エンジニア（マーケターやWebアナリスト）など初めてプログラミングコードを書く人も多くいるため、放っておくと非常にバラバラな書き方になってしまう。
  • テストがしづらい（ロジックが間違っていても実行結果は出てしまう）ため、コードのレビューが分析の品質を保つためには必要。
  • 分析をしながら書くと1度作ったら終わりではなく、試行錯誤を繰り返しつつ、どんどんと追記されていく。（秘伝のタレ化しやすい）
  • 人材の流動が激しく、前進者がいないことが多かったり、アドホックでとりあえず作った的なコードがバッチ運用されていることも多い。

上記のことを含めて、

あくまでもデータ分析をする上でのSQLコード規約を考えてみました。

データ分析SQLの課題とポイント

  • レビューをしやすい形式を取る。
    • 分析のコードはJOINやサブクエリを多用するが、目的と間違っていたとしても、動いてしまうため、ロジックチェックをしやすいコードを書く必要がある。
    • 引き継ぎ及び、未経験者でもレビューできるようにする。（また未経験でもレビューしやすいコードをかけるようにする）
  • バグフィックスやリファクタリング（データ確認）がしやすい形式を取る。
    • 分析クエリを書く際は逐次分析テーブルの中身を加工するので中身が確認しやすくする。
    • データの構造が変わったりすることもあるのでメンテナンスすることも意識する。
    • サービスに機能追加されると根本的にデータが構造が変わることもよくあるため。
    • 改修に手間がかからないようにする。（データウェアハウスの移行が将来あることを想定する）
      現在のところ、データ基盤はBigQuery一択なのだが今後変わる可能性もあるかもしれない。

かなり細かいことかも知れませんが、必要なのは共通認識を持ち、なぜこのような規約を作ったかを理解してもらうことが重要だと思います。基礎的や当たり前のことも多いですが、お互いに当たり前が当たり前でないことが多いのであえて細かく書いてみました。あくまで私達の規約なのですべてを合わせる必要はないかと思います。
（※今回はBigQueryを想定して書いております。）

文字に関する規約

予約語/関数は、大文字にする。

大文字にします。
SQL自体は、大文字小文字は関係ないですが、カラム名は大文字小文字の区別をしたりします。

メリットとしては、コードを見た時に何を処理をしているかというわかりやすさです。
特に非エンジニアの人もコードを書くことを想定すると予約言語や関数については大文字にしたほうがパッと見た時に明瞭になるからです。
また、サブクエリを多様することも多く、'xxxx_date','xxxxx_timestamp'と行った命名をすることもあります。その時に分析者が変数名で使ったのか関数で使ったのかわかりやすいという意味でも、予約語/関数は大文字にしておいた方がよいでしょう。

SELECT
  user_id,
  status,
  create_timestamp_utc
  MAX(create_timestamp_utc) OVER (PARTITION BY user_id ORDER BY create_timestamp_utc)
FROM 
  user_data_tbl


悪い例
select
  user_id,
  status,
  create_timestamp_utc
  max(create_timestamp_utc) over (partition by user_id order by create_timestamp_utc)
from 
  user_data_tbl

JOINのASに単文字を使わない。（変数名も）

分析を行う際には多くのテーブルをJOINします。その際に　a,b,cなどの単語だけを使うと何をつけているのかわからなくなります。
特に分析を行う場合はテーブル数が多くなるので、テーブル名を明確に示すような別名にしないとどこのテーブルからデータを抽出したのかわからなくなってしまいます。

良い例
SELECT
  user.user_id,
  user.action_id,
  action.action_name,
  item.item_name
FROM
  user_data_tbl AS user
LEFT JOIN
  action_data_tbl AS action
ON user.user_id = action.user_id
LEFT JOIN
  item_data_tbl AS item
ON action.item_id = item.item_id
WHERE
 user.status = 'paid'


悪い例
SELECT
  a.user_id,
  a.action_id,
  b.action_name,
  c.item_name
FROM
  user_data_tbl AS a
LEFT JOIN
  action_data_tbl AS b
ON a.user_id = b.user_id
LEFT JOIN
  item_data_tbl AS c
ON b.item_id = c.item_id
WHERE
 user.status = 'paid'

スネークケースで書く。

変数に関しては、必ずスネークケース（_）で文字をつないでください。
BigQueryの場合はカラム名に_しか使えません。大文字も認識しますが他のデータベースの場合ですと使えない場合もありますので、キャメルケースで統一してください。

接頭辞(prefix) / 接尾辞(suffix)をつける。

基本的に集計した結果がわかるように接頭語をつける。使った関数により決めておくと良い。
特にデータ分析では統計を使うことが多いためどの集計をしているのか決めておいた方が良い。

例)MAX(login_timestamp) AS max_login_tiemstamp

関数はBigQueryの関数で表記している。

関数 関数 Center align
max_*** MAX() MAX()の取得カラム
min_*** MIN() MAX()の取得カラム
avg_*** AVG() AVG()を使ったカラム
sum_*** SUM() aligned
***_cnt COUNT() COUNT()の取得カラム
***_dis_cnt COUNT(DISTINCT ) COUNT(DISTINCT )を使った集計
row_num_*** ROW_NUMBER() ROW_NUMBER()を使った場合のカラム
***_timestamp_jst timestamp("Asia/Tokyo") timestampをjstで保存する場合
***_timestamp_utc timestamp() timestampをutcで保存する場合（中間テーブルの処理などに合わせる。
***_unix_time 　　 unixの秒単位で保存されている場合
***_unix_milli_time unixtimeがミリ秒単位で保存されている場合

のような形で事前に決めておく。

idというカラム名は使わない。

結合する際にidの場合のみの場合に結合ミスが起きるため、他のデータベースから読み込む際に変更しています。

体裁に関する規約

1行に1カラムにする。

これもなれると無視をして、複数のカラムを使ってしまうことがありますが、基本的には1行1カラムにした方がどのカラムを使っているのかわかるのでしっかり書いた方がよいです。特にログ形式のデータの場合、user_id,content_id,item_id,status_idのように様々なIDが並ぶことや、create_timestamp,update_timestamp,delete_timestampなどの時間の記録などが並ぶことが多く、1行に1つにしないとどのカラムをつかっているのかわからなくなっていしまいます。

良い例
SELECT
  user_id,
  content_id,
  item_id,
  status_id,
  create_timestamp,
  update_timestamp,
  delete_timestamp
FROM
  user_data_tbl

悪い例
SELECT
  user_id,content_id,item_id,status_id,create_timestamp,update_timestamp,delete_timestamp
FROM
  user_data_tbl

どのカラムをつかっているのかわからないとデバックやまた、別の人が見たときに確認できなくなってしまう。

インデントは2文字（スペース）にする。

タブを使わずスペースで2文字にします。分析のクエリは階層が深く横長になりがちなので、2スペースとします。
３階層ぐらいのサブクエリを書くと顕著に差分が響いてきます。


SELECT
  user_id,
  status,
  create_timestamp_utc
FROM(
  SELECT
    *
  FROM(
    SELECT
      user_id,
      status,
      create_timestamp_utc
      MAX(create_timestamp_utc) OVER (PARTITION BY user_id ORDER BY create_timestamp_utc)
    FROM 
      user_data_tbl
    WHERE user_id >= 0

悪い例
SELECT
    user_id,
    status,
    create_timestamp_utc
FROM(
    SELECT
        *
    FROM(
        SELECT
            user_id,
            status,
            create_timestamp_utc
            MAX(create_timestamp_utc) OVER (PARTITION BY user_id ORDER BY create_timestamp_utc)
        FROM 
            user_data_tbl
        WHERE user_id >= 0
    )
)

AND,ORは改行して先頭に

こちらも条件式が複数になる且つ、追加がよく起こるので改行しておく。

良い例
SELECT
  user_id,
  status
FROM
  user_data_tbl
WHERE
 user_id IS NULL
 AND status = 'paid'
　

悪い例
SELECT
  user_id,
  status,
FROM
  user_data_tbl
WHERE
  user_id IS NULL AND status = 'paid'

SELECT単位でインデントを揃える。

JOINをすることが多いデータ分析でのSQLは、どのテーブルとどのテーブルがJOINしているのかわからなくなるので、階層の関連性を明確にするためにも、SELECT単位で揃えます。

機能的規約

INNER JOINを使わない。

少なくとも、BigQuery環境においてはコストは変わりません。NULLのデータが入ってしまうことなので、INNER JOINにより本来は削除すべきでないデータを除外してしまう危険性もあるのでLEFT JOINを利用しWHERE句を用いて丁寧に除外します。

一定の量が増えたらサブクエリをWITH句にする。

長くなりすぎるとサブクエリを終えなくなるため、一定の深さになったら、（3階層ぐらいでまとめる）WITHを使うことを推奨しています。

集計時刻のカラムを作る。

データマートを作成する際にいつ作られたデータなのか入力する。作成時刻がわかるため、データの遅延などにより間違った集計を出した時にリカバリーしたのかわかるため便利です。

SELECT
  user_id,
  status,
  CURRENT_TIMESTAMP() AS aggregate_timestamp_utc -- 作成した時刻が分かる。
FROM
  user_data_tbl

集計期間には、ファンクション変数を使う。

日時変数に関数を使い、期間を事前に変数として使います。クエリを作る際にテスト的に時間を決めることがあるが、実際の運用で行うと変数で管理されていることがある。
また複数のテーブルを使うときにも、シャーディングに変数を使うことで少ないテーブルで分析することができる。
アドホックな分析の際も変数を変えるだけで分析できて、時間変更ミスも減らすことができるため予め、変数にしておくことをおすすめします。

--term variable
create temporary function agg_start_month_date() AS ('${agg_start_month_date}');
create temporary function agg_end_month_date() AS ('${agg_end_month_date}');

--term test
-- create temporary function agg_start_month_date() AS ('2019-12-01');
-- create temporary function agg_end_month_date() AS ('2019-12-11');

SELECT
  *
FROM
  user_data_tbl
WHERE
  create_date BETWEEN agg_start_month_date() AND agg_end_month_date()

例外

可読性のためであれば、上のルールを変更しても良いかと思います。
あくまで分析のためのSQLは可読性を上げてエラーをなくすこと、分析内容を理解することなので、
行数が長くなるのであれば、上記のルールを破って行数を少なくする書き方をお勧めします。

UNIONする場合

縦型を横型に持ってくる際は、全て書くと長くなるため、同じようなクエリを書く場合は、このような書き方にしてしまっても良い。

SELECT user_id,"成績A",grad_a, FROM user_data_tbl
UNION ALL
SELECT user_id,"成績B",grad_b, FROM user_data_tbl
UNION ALL
SELECT user_id,"成績C",grad_c, FROM user_data_tbl
UNION ALL
SELECT user_id,"成績D",grad_d, FROM user_data_tbl

など・・・。

規約は、話し合って決めておくことが大事。変更することも大事。

規約を決めようとすると宗教論争や過去のやり方などでいろいろと揉めることがあるかと思いますが、正しい答えはないと思います。重要なのは可読性を上げて、分析業務を運用するために行うためなので、チームで話し合ってきめると良いかと思います。

繰り返しますが、あくまで私達のチームが使いやすいように考えました。

この規約もあくまで一例なので参考程度にしていただければと思います。

参考
https://qiita.com/yukiyoshimura/items/9103cdc88eca507e3c2c
https://qiita.com/taise/items/18c14d9b01a5dfd6d35e

10
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
11