GoogleCloudPlatform
GoogleBigQuery
bigquery
More than 3 years have passed since last update.


はじめに

いつの間にかJOINUNIONが使えるようになった!

さらに出来るようになったなBigQuery!!

と、いうことでBigQueryには便利な関数が用意されています。

でも使い方を誤る(つーか、あとで気づくのだが)と後で『やり直しかぁぁぁ!』となります。Oracleに慣れていると『あぁ、暗黙変換ってとっても便利だったんだなぁ。』と思うこともしばしば。まぁ、甘えすぎなんですけどね。ちゃんと指定せぃっ!

まぁ、そんなこんなで。


時間はあくまでUTC

時間や日付に関する関数が色々用意されています。

https://developers.google.com/bigquery/query-reference?hl=ja#datetimefunctions

使い始めると本当に便利です。

でね、このあたりでよく使うのがCURRENT_DATEとかCURRENT_TIMESTAMPとか。

DATE_ADDとかと組み合わせて帳票なんかで過去1週間のデータだけを毎日出すなどあるわけですよ。

そんなときにDATE_ADD(CURRENT_DATE(),-7,"DAY"))とすると・・・

2014-07-28 00:00:00 UTC

と、出てきます。だが、しかーーーし!ここで注目!!

UTC

これ!これが曲者!BigQueryで現在時間はUTCで処理されるんです!

だから、明け方にBigQuery上で集計するバッチなどは要注意!上のSQLで日付比較なんかすると前々日までのデータしか集計されません。じゃ、どうすりゃいいのってことでうちではこうやってます。

DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),+9,"HOUR"),-7,"DAY")

解説を書くまでもないかもしれませんが、+9でJSTに直して日付を足すって感じです。

昼間開発してると案外気が付かないもんです。


COUNT(DISTINCT ***)はおおよそです!

何を言っているのかよくわからないでしょ?

私自身も最初、何のことかよくわかりませんでした。

例えばうちの場合だと注文番号があって、明細番号というのがあります。こういう場合、注文番号が一意ではなくなります。で、注文番号だけがいくつあるのか集計したい場合がよくあります。そんなときにCOUNT(DISTINCT SLIP_NO)とします。

でも、これはおおよその数値が出るだけ。

レコード数にもよるのかもしれませんが、万件レベルで数値がズレます。なぜか?知りません!じゃ、どうするのでしょうか?『Google Big Query Analytics』にもありますが、例文を書いておきます。

SELECT

COUNT(SLIP_NO)
FROM
(SELECT
SLIP_NO
FROM
SLIP_TABLE
GROUP BY SLIP_NO)

一度GROUP BYでまとめてから、それをCOUNT()するってことです。


最後に

どうですか?面倒ですか?でも、少しSQLを書くのが面倒かもしれませんが、IndexをきかせるSQLを書くのに悩むよりはずっと良いかと。

すいません、今日は2つだけで。

小出しにするつもりはないんですけどね。

と、いうよりもこの2つだけが強烈にハマるんです。ほかの関数は案外素直ですよ。週とか月とかの関数って帳票作るのにすごい便利ですから。使っていくうちに『そうそう、そういうの欲しかった!』って思うことが多いです。きっとBigQuery作ってる人は、『これよく使うから関数にしちゃえ』的なノリだったんだろうなぁと思ったりします。そんな妄想を抱きながら毎日使っています。

ではまた。