Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
22
Help us understand the problem. What is going on with this article?
@satoru_mag

便利な関数の甘い罠

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

ではまた。

22
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
satoru_mag
社内なんでも屋さん。 BigQueryとGoogleAnalytics360が大好物。 最近はがんばってpython勉強中。 GoogleDevelopersExpert(GCP)

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
22
Help us understand the problem. What is going on with this article?