テラバイト級、ペタバイト級の大規模データの分析では、JOIN句やWindow関数などを使った複雑なSQLを書けることが必須条件だと思います。そのような大規模データはGCPのBigQueryやAWSのRedshiftにデータが格納されている事が多く、SQLを自由に書けないと必要なデータを抽出することができないからです。そして、SQLの構文や文法に関する書籍やサイトは数多く存在しますが、SQLでデータ分析する際のチップスやノウハウを紹介したものはほとんど見当たらなかったので記事にしてみました。
まずはスモールデータで試す
いくらBigQueryやRedshiftが高速だと言っても、1年分のログデータとマスタデータ（例：ユーザ情報、アイテム情報）をJOIN句で結合させて…などすれば、クエリの結果が返ってくるまで数十分、最悪数時間掛かる場合があります。返ってきたデータが意図した通りのものであれば良いのですが、関数の使い方が間違っていたり、そもそもデータに不備があったりして意図した結果ではないという事はしばしばあります。その場合、クエリが返ってくるまで待っていた時間が無駄になります。そのような事態が防ぐために、まずはスモールデータで試して結果を確認するようにしましょう。先ほどの例で説明すれば、
- まず、1日分（1時間分などでもOK）のログデータを対象にクエリを実行し、結果を確認する。意図した結果でない場合、その原因を探す。SQLに問題があるのか、データに不備があるのかのどちらかの場合が多い。
- 1で結果が問題ないことを確認できれば、1年分のログデータを対象に再度クエリを実行する。
という流れになります。
テンポラリーテーブルを活用
アドホックな分析をしていると、下図のように途中までは同じ処理だけど、後半の処理だけを換えて分析したいという事がよくあります。この場合、毎回最初から最後まで処理を流すのは無駄です。
このような場合は下図のようにテンポラリテーブル（一時テーブル）を作りましょう。前半の共通処理部分は一度だけ実行すれば結果を使い回せるようになったので、全体としてのクエリの実行時間が短くなり作業効率が上がります。もしこのテンポラリテーブルが他の人の分析でも使用される可能性が高い場合、データエンジニアにお願いして誰でもアクセス可能な共通テーブルにすると良いでしょう。
JOIN句には要注意
異なるテーブルを結合させるJOIN句は分析には欠かせません。ログデータにユーザやアイテムの属性情報が入ったマスタデータを結合させるというのは、データ分析では必ず通る道です。ただ、この便利なJOIN句、気を付けておいた方が良いです。というのも、意図せずにデータが増殖（？）することがあるからです。
例えば、アイテムの履歴情報を持たせるために、アイテムIDとレコード数が1対1ではなくて1対Nの関係になっている分析用のマスタテーブルがあったとします。この場合、何も条件を指定せずにログデータにこのテーブルをOUTER JOIN句で結合させると、複数レコードを持つアイテムID（分析用のマスタテーブルの中でアイテムIDとレコード数が1対Nの関係にある）ではログがN件まで増えます。これを防ぐためには、マスターテーブルの各アイテムIDに対して、最新のタイムスタンプのレコードだけをOUTER JOIN句で結合させるというような処理が必要です。ただ、この例に限らず、JOIN句で意図せずにデータが増殖していたという事例は時々起きるので、JOIN句を使う場合は結合後のデータ件数を都度確認するようにしましょう。countを使って全体件数を確認する、また、特定のユーザIDやクッキーIDのログだけを抽出して確認するのがお薦めです。
テーブル名、カラム名に騙されない
例えば、性別や生年月日などのユーザの属性情報を分析に使いたいという場合があったとします。そして、これらの属性情報が格納されていそうなテーブルを探したところ、usersというテーブルにsex、birthdayというカラムを発見。早速、このusersというテーブルからユーザ毎の性別や生年月日を抽出したものの、一部のユーザ情報しか入っていなかった。よくよく調べるとusersというテーブルは現在は使われておらず、最新のユーザ情報はusers2というテーブルに格納されていたという笑うに笑えない事例に遭遇することがあります。
他にも、商品名を分析に利用したく、itemsというテーブルのnameというカラムのデータを抽出してきたものの何かがおかしい。詳しい人に訊くと、nameは商品の説明文が入ったカラムで、商品名はtitleというカラムに入っていることが分かったというような事例もあります。
このような事例は時々見聞きするので、テーブル名やカラム名ですぐに飛びつかないようにして、念の為データの中身を確認するようにしましょう。例えば、
- 最近登録されたユーザやアイテムのデータがちゃんと格納されているのか確認する。
- 詳しそうな人、知っていそうな人に訊く。
- Slackで専用チャネルを作って、そこで情報交換するのもあり
- 自分が知っているメタデータに関する情報は社内Wikiなどに積極的に書き残す。
などの対策を取ると良いと思います。