BigqueryStandardSQLの黒魔術ってなに!?記してみました!

Mizuki Kobayashi
Dec 15, 2018 · 8 min read


氷魔法 : 定数化

TEMP FUNCTION を使って何回か使いそうなものは定数化

BigqueryStandardSQLでは、クエリの中に一時的なUDF (ユーザーが定義した関数) を書くことができます。

年齢計算

クエリ内で使うタイムゾーンを定数化

例えば、PairsではUTCのまま各種データを突っ込んでいるので、

CREATE TEMP FUNCTION TIMEZONE() AS (“Asia/Tokyo”);
DATE(created_at, TIMEZONE())DATETIME(created_at, TIMEZONE())
CREATE TEMP FUNCTION TIMEZONE() AS (“Asia/Taiwan”);

読み込む範囲を定数化

Bigqueryでは、DATEでPARTITIONを切ったり、テーブルの接尾辞(SUFFIX)で日次や月次などでテーブルを分割して管理する方法が一般的です。

DateSuffix
共通の定数suffixを使ったテーブル読み込み

†† フラグを持たせる †† (闇魔術)

フラグによって読み込んでくるテーブル自体を変えたい場合もあるかもしれません。

flagを使った黒魔術
CREATE TEMP FUNCTION FLAG() AS (  SELECT COUNT(*) FROM `project_id.piyo.piyopiyo`);

ただしこんなものを世に放ってはいけない。

時魔法 : カレンダー

連続した日付をもつ仮テーブルを作ると便利

BigqueryStandardSQLでは、GENERATE_DATE_ARRAY という関数で、指定の日付間を指定の間隔で切った配列を作れます。

Calendarテーブル

値が0の日も計上する

例えば、売り上げのデータとして以下の表のようなものがある時

購買データ
Calendarを使って売り上げを綺麗に
Calendarを使った集計データ

window句を用いないで累計する

また、Calendarテーブルを使うことで、window句を用いることなく集計することができます。

ログインデータ
月初からの累計ログインユニークユーザー数

風魔法 : 集計関数

Bigquery様は大変高速に集計してくれます。

DATEが最古 / 最新の時のhogeの値を持ってくる

先ほどのユーザーのログインデータにおいて、

初回ログイン時のデバイスを取得

GROUP BY した要素ごとにTOP10を持ってくる

先ほどの売り上げデータを見たときに、Dailyで売り上げの良かった

日次での売り上げ上位3商品

Eureka Engineering

Learn about Eureka’s engineering efforts, product developments and more.

Mizuki Kobayashi

Written by

eureka,Inc. BI team / 分析 / 機械学習

Eureka Engineering

Learn about Eureka’s engineering efforts, product developments and more.