便利な関数の甘い罠

はじめに

いつの間にかJOINとUNIONが使えるようになった！
さらに出来るようになったな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作ってる人は、『これよく使うから関数にしちゃえ』的なノリだったんだろうなぁと思ったりします。そんな妄想を抱きながら毎日使っています。

ではまた。