BigQueryで読みやすいSQLを書くコツ - たったの３つであなたの意図はもっと伝わる。

こんばんわサーバサイドエンジニアのjonnyです。

弊社では分析のためにBigQueryを使っています。エンジニアだけでなく、企画職の人間もクエリを叩いて日々分析をする環境が整っています。

データを見る場所の分散を避けるため、分析対象のデータは全てBigQueryに集約しています。 RDS（Aurora)上のデータはdigdag+embulkによって日時バッチで連携しており、WebサーバのアクセスログやFirebaseのイベントデータはストリーミングインサートされています。

データの可視化については、昔ながらのGoogleSpreadSheet+GoogleAppScriptから、最近ではre:dashや一部Tableauを使用しています。

分析を促進するための工夫として、SQLをレビューするSlackチャンネルが合ったり、社内勉強会を行ったりしています。

しかしながら、非エンジニアが書くクエリはしばしば非効率であったり、読みにくく再利用しづらいことがあります。

なので、SQLレビュー時に気をつけている観点や、よく指摘する事項、使い回しやすいスニペット（クエリの断片）を幾つか紹介したいと思います。

中にはTimersならではの事情にフォーカスした物もありますが、広く色んな人が使える情報もあると思いますので、参考にしてみてください。

前提事項：LegacySQLではなく、StandardSQLを使おう
1. StandardSQLの注意点
2. 日付の関数を使いこなそう
  1. おまけ：９日〆に対応する
見やすいクエリの書き方
1. インデントをつけよう
2. コメントを書こう
3. WITH句を使ってサブクエリを減らそう

前提事項： StandardSQLを使おう

見やすいクエリの書き方をお伝えする前に、前提条件があります。 BigQueryでクエリを書くときは、必ずStandardSQLを使ってください。

昔からBigQueryを使っている会社だと、LegacySQLとStandardSQLが混在しているのではないでしょうか。新しくクエリを勉強しようとした場合、どちらを学べばいいか迷うことと思います。こちらはStandardSQL一択です。今から古いLegacySQLを使う必要は一切ありません。 MySQLやPostgresqlなどで使用されている標準SQLが標準語だとすると、StandardSQLは関西弁、LegacySQLは古ラテン語位の違いがあります。LegacySQLはいずれサポートされなくなるでしょう。 StandardSQLを学んでおけば、MySQLを見る必要があった場合も少ないコストで移行できるというメリットもあります。

LegacySQL→StandardSQLの大きな違い

関数は全然違います。リファレンスはこちらを参照ください。中でも注意が必要なのが日付別テーブルをまとめて参照する際の関数です。 TABLE_DATE_RANGE()が使えなくなり、_TABLE_SUFFIXを使う必要があります。こちらは大きく使い方が違い、前者はFROM句に、後者はWHERE句に書きます。

select 
    * 
from 
    table_date_range(sample.access_log_, timestamp('2017-12-01'), timestamp('2018-02-01');

select 
    * 
from 
    `sample.access_log_*`
where 
    _table_suffix between '20171201' and '20180201';

日付関数の使い方

StandardSQLを使うと、豊富な日付操作の関数が利用可能になります。

具体的な例として、弊社でよくあるシーンを解説します。月の締日が９日なので、月単位でサマリをする場合

where  
    _table_suffix 
    between 
        format_date("%y%m10",   -- ④毎月10日に丸める
            date_add(
                date_add(
                    current_date() -- ①現在日付を取得する
                    , interval -9 day
                ) -- ②締日（9日）分ずらす
                , interval -1 month
            ) -- ③前月を求める
        ) -- A: 前月の10日
    and 
        format_date("%y%m10", 
            date_add(
                current_date()
                , interval -9 day
            )
        ) -- B: 今月の10日

これを使うと、例えば今日が2018-01-20だとすると、between '20171210' and '20180110' になります。

現在日付を求めます。currentdate()なので日を求めます。他にも時間まで求められるcurrenttimestamp()などもあります。
date_add関数は日付の増減をする関数です。 interval ± n (hour|day|week|month)と言った形で増減の量と単位を指定できます。
こちらも同じです。date_add関数は日付型を返すので、さらにdate_addで計算することが出来ます。
date_format関数は日付型を指定したフォーマットの文字列にコンバートする関数です。
_table_suffixは文字列型なので、比較するためにデータ型を合わせるのと同時にフォーマットで日付部分を10日に固定しています。

日付関数を用いると、うるう年などの計算をこちらで気にする必要がなくなります。エンジニアにとっては常識かもしれませんが、知らずに数値計算で頑張っている例を見かけたので念のため紹介しました。

2. 見やすいクエリの書き方

クエリは書いて終わりではありません。恐らく殆どのケースで別の誰かが、レビューをしたり、再利用することになるでしょう。クエリを書くときは読む人のことを考えることが大切です。一番必要なのは愛です。その上で、読みやすいクエリを書くコツを３つ紹介します。

1. インデントをつけよう

人間はコンピュータじゃありません。ぱっと見て構造がわかりやすいようにするため、インデントはとても重要です。私はCookpadさんのテックブログで紹介されている分析SQLのコーディングスタイルを参考にしています。意味のある単位でまとまりを作りましょう。

2. コメントを書こう

Webシステムの開発は品質よりスピードが重視されることがあります。十分にきれいな設計がなされないまま作られたDBは運用されていく中で多様なデータが入ってしまいました。現在のテーブル名が中に入っているデータを適切に表していない、ということはよくある話です。また、テーブル名が長すぎてクエリが読みづらい、ということもあります。そういうときは別名（alias)をつけてあげましょう。カラム名やテーブル名の後ろに as を書くだけです。その上で、自分の意図をシンプルな英語名で表現するのが難しい場合、コメントを書いてあげましょう。

3. WITH句を使ってサブクエリを減らそう

往々にして分析用クエリは肥大化しやすく、とても読みづらいものになります。可読性を下げる原因の一つに、サブクエリの多用があります。サブクエリとは

select * from hoge  inner join  ( select * from fugafuga);

みたいなやつですね。これぐらいシンプルであれば気になりませんが、サブクエリの中で更にサブクエリがあったり、WHERE句やGROUP BY等で条件が増えると途端に複雑になります。このサブクエリを外に出すことで、ネスト（階層構造）を少なくするために役立つのがWITH句です。

例を紹介します。

※ 実際のクエリからテーブル名やカラム名を適当にマスクしましたので、あまり意味は気にしないでください。広告系のイベントログと、firebaseでイベントログをかけ合わせて流入媒体別の画面表示数を取得するみたいな用途です。

SELECT
fb.screen,
CASE
WHEN ad.network_name IN ( "Organic",  "Google Organic Search", "Yahoo! Organic Search") THEN "オーガニック"
ELSE "広告" END AS cate,
COUNT(fb.id)
FROM (
SELECT
COALESCE(idfa, idfv) as id,
screen
FROM
TABLE_DATE_RANGE(firebase_event_, TIMESTAMP('2017-09-10'), TIMESTAMP('2017-10-10'))
WHERE
screen IN ("HogeViewController", "FugaViewController")
AND DATE_ADD(event_time, 9, "HOUR") >= TIMESTAMP("2017-09-10")
AND DATE_ADD(event_time, 9, "HOUR") < TIMESTAMP("2017-10-10")
GROUP BY
id,
screen) AS fb
JOIN (
SELECT
COALESCE(idfa, idfv) as id,
network_name,
MIN(date)
FROM
TABLE_DATE_RANGE(ad_event, TIMESTAMP('2017-09-09'), TIMESTAMP('2017-10-10'))
GROUP BY
id,
network_name ) AS ad
ON
(fb.id = ad.id)
WHERE
fb.id IS NOT NULL
GROUP BY
fb.screen,
cate
ORDER BY
fb.screen,
cate

with fb_event as( /* firebaseの画面表示イベント */
    select
        coalesce(idfa, idfv) as id
        , screen
    from
        `firebase_event_*`
    where
        _table_suffix between '20170910' and  '20171010'
        and screen in ("HogeViewController", "FugaViewController")
        and event_time between timestamp("2017-09-10 00:00:00+09") and timestamp("2017-10-10 00:00:00+09")
    group by
        id
        , screen
),
ad_category as( /* ID毎の流入媒体 */
    select
        coalesce(idfa, idfv) as id
        , case 
            when ad.network_name in ( "Organic",  "Google Organic Search", "Yahoo! Organic Search") then "オーガニック"
            else "広告" end as category
    from
        `ad_event`
    where
        _table_suffix between '20170910' and  '20171010'
    group by
        id
        , category
    )

/* 流入媒体別の画面表示数 */
select
    fe.screen -- 表示画面名
    , ac.category -- 広告カテゴリ
    , count( fe.id ) as count  -- 画面表示回数
from
    ad_category as ac
    inner join fb_event as fe using(id)
where
  fi.id is not null
group by
  fi.screen
  , ac.category
order by
  1, 
  2

どうでしょうか。(※LegacySQL→StandardSQLに変更したため使っている関数が一部違います)

行数は増えているものの、後者のクエリのほうがわかりやすくありませんか？

前者のクエリはサブクエリを使っているため、何がしたいのかを把握するために読む量が多いです。しかし、後者のクエリはWITH句とメインのクエリで意味がわかれています。メインのクエリを読んで大意を把握した上で、詳細が知りたい場合はサブクエリに相当する個別テーブルを参照すればよいのです。画面スクロールせず全体が俯瞰できるためasでつける別名も短くて済みます。

インデントをつける
コメントを書く
with区でサブクエリを外に追い出して、階層を浅くする

ちょっとした工夫でクエリは読みやすく出来ます。親切なクエリで、あなたの意図を明確に伝えましょう。

積極採用中!!

子育て家族アプリFamm、カップル専用アプリPairyを運営するTimers inc. では、現在エンジニアを積極採用中！急成長中のサービスの技術の話を少しでも聞いてみたい方、スタートアップで働きたい方など、是非お気軽にご連絡ください！採用HP : http://timers-inc.com/engineerings