BigqueryStandardSQLの黒魔術ってなに！？記してみました！
この記事は eureka Advent Calendar 2018 15日目の記事です。
こんにちは。BIチーム小林です。
最近はもっぱらFortniteをメインに、スマブラをたまにやっています。
お昼は機械学習周りをメインに、分析をたまにやっています。
最近はガリガリとクエリを書くこともなくなってきたので、備忘録を兼ねて、かつて習得した黒魔術、もといStandardSQLの便利な記法を記しておきます。
ただし、主旨としては、SQLを使ってこんな便利なことができるんだぜ、こんな計算までできちゃうんだぜ、というよりは、こんなん書くのめんどくさいからこう書いちゃおうや、という記事です。
氷魔法 : 定数化
TEMP FUNCTION を使って何回か使いそうなものは定数化
BigqueryStandardSQLでは、クエリの中に一時的なUDF (ユーザーが定義した関数) を書くことができます。
これを使ってエウレカでは
などとして、
年齢計算する関数を作ったり、
ABテストのセグメント分け条件をまとめたりして活用しています。
この関数は引数無しで定数を返すものも定義できるので、これを使って擬似的に定数を宣言することができます。
クエリ内で使うタイムゾーンを定数化
例えば、PairsではUTCのまま各種データを突っ込んでいるので、
CREATE TEMP FUNCTION TIMEZONE() AS (“Asia/Tokyo”);と書いておくと、時差を考慮したいときには
DATE(created_at, TIMEZONE())DATETIME(created_at, TIMEZONE())
などと書けます。
Pairsは多国展開しているので、こうしておくとクエリを他国のものに転用するとき、
CREATE TEMP FUNCTION TIMEZONE() AS (“Asia/Taiwan”);と変えるだけで良いのでラクチンです😀
読み込む範囲を定数化
Bigqueryでは、DATEでPARTITIONを切ったり、テーブルの接尾辞(SUFFIX)で日次や月次などでテーブルを分割して管理する方法が一般的です。
BigqueryStandardSQLではワイルドカードを使ってテーブルを読み込んでWHERE句で当てはまるテーブルを絞りますが、そこも定数化しちゃいましょう。
例えば、
と書いておけば、
と書くことができます。
これによって複数のテーブルやデータセットから同様の範囲でデータを取得したいときに、なんども範囲を書かなくて済むのでミスを減らすことができます。
このように、繰り返し出てくるものを定数化することで、
クエリの修正や改良をしたいときに変更箇所が上に纏まっていてスグに編集できるので、ガンガン定数化していってしまいましょう。
†† フラグを持たせる †† (闇魔術)
フラグによって読み込んでくるテーブル自体を変えたい場合もあるかもしれません。
そんな時は例えば以下のように書けば実現できます。
上記のように FLAG()==0 つまり hoge が呼ばれないと明らかな場合、
Bigquery様は、 project_id.hoge.hogehogeテーブルをスキャンしません。
また、実は CREATE TEMP FUNCTION 内にはサブクエリを書くこともでき、例えば以下のように書けます。
CREATE TEMP FUNCTION FLAG() AS ( SELECT COUNT(*) FROM `project_id.piyo.piyopiyo`);
ただし、この場合FLAG()が返す値がテーブルの内容によって変わるため、
Bigquery様といえども、hoge, fuga, piyo全てのテーブルをスキャンします
(カラムは読み込むもののみ)
今回は敢えてあんまり使えないようなものを紹介したが、使いようによっては夢が広がりますね。
ただしこんなものを世に放ってはいけない。
時魔法 : カレンダー
連続した日付をもつ仮テーブルを作ると便利
BigqueryStandardSQLでは、GENERATE_DATE_ARRAY という関数で、指定の日付間を指定の間隔で切った配列を作れます。
これをUNNESTしてあげれば簡単にそれをテーブル化することができます。
これを使うといくつか便利なことがあります。
値が0の日も計上する
例えば、売り上げのデータとして以下の表のようなものがある時
このまま日ごとに集計しても、2018–12–12は何も売れていないので、データがないため、最終的なレコードからは抜け落ちてしまいます。
そんなときに、上記のCalendarテーブルを使って以下のように書くと、
このように売り上げが無い日もレコードを省くことなく集計できます。
データが存在しない日もレコードが作成されるおかげで、この後にwindow句を用いて、前日との正確な差分や欠損埋めを行うことが可能になります。
window句を用いないで累計する
また、Calendarテーブルを使うことで、window句を用いることなく集計することができます。
今回の売り上げのような単純に加算すれば良いデータでは恩恵を感じることができませんが、
例えば、ユーザーごとのログインデータを持っているときに、月初からの累計ログインユニークユーザー数を知りたいことがあるとしましょう。
ログインのデータは以下の表のようになっているとします。
このようなデータでは、日次で累計のユニークユーザー数を集計しようとwindow句を用いようとすると、
DISTINCT と ORDER BY は同時に用いることができないためエラーが出るか、
パワーでやろうとすると最大で日数*ユーザー数分の集計が走ってしまい、効率が悪くなります。
しかしここで、Calendarテーブルを用いると簡単に集計することが可能になります。
風魔法 : 集計関数
Bigquery様は大変高速に集計してくれます。
そのため、多少の無茶であっても集計関数に任せてしまった方が結果的に早かったり、簡単に記述できたり、良い結果をもたらす場面があります。
DATEが最古 / 最新の時のhogeの値を持ってくる
先ほどのユーザーのログインデータにおいて、
ユーザーが期間内で一番最初にログインした時のデバイスを知りたいとします。
一般に考えられる方法ですと、
◆ ユーザーごとに、最初のログイン日時を集計して取得し、元のログインデータとINNER JOINする。
◆ window句を使ってユーザーごとにログイン順で並べた一番最初の値を取ってきて、ユニークをとる。
などが考えられます。
一つ目はわざわざJOINするのが手間。二つ目は、ログインレコードごとにwindow句で集計されるため、効率が悪い & 一つにまとめる集計コストもかかります。
ここで以下のような書き方をすると、
GROUP BY した要素ごとにTOP10を持ってくる
先ほどの売り上げデータを見たときに、Dailyで売り上げの良かった
いかがでしたでしょうか？
このようにBigqueryStandardSQLは、LegacySQLとは記法が大きく変わり、window句の動作順も変わりました。
Bigqueryさんは高速でデータを並べ、集計することができるので今後の活躍が期待されています！目が離せませんね！
