Timee Product Team Blog

タイミー開発者ブログ

BigQueryとLookerStudioのニッチな落とし穴についてまとめてみた

こんにちは、タイミーでデータアナリストをしているyuzukaです。 主にプロダクトの分析に携わっています。

ビジネス職からデータアナリストに転向して約1年経った私が、1年前の自分に教えてあげたい、BigQueryや LookerStudioに関する落とし穴を、いくつか挙げてみようと思います。

はじめに

弊社では、分析環境として BigQueryを採用しています。LookerStudioを使って、 BigQueryのデータを参照してダッシュボードを作ることもよくあります。

BigQueryの SQLを使った分析を進めていく中で、想定と異なるデータが出てきてしまい、原因を特定するのに苦労し、無駄な時間を費やしてしまった経験が何度もあります（実際には、そんな過程もきっと無駄ではないと信じたい）。

こちらのブログを読んでいただいたみなさまには、同じ苦労を味わっていただきたくないので、私が今までにハマってきた落とし穴をいくつか紹介します。

1. BigQueryで使える一部の記法は、LookerStudioでサポートされておらず、接続エラーになる

BigQueryでは正常に動いていたクエリが、LookerStudioを使った途端に謎のエラーになることがあります。

これは、一部の記法が LookerStudioでサポートされていないことに起因しているようです。

私が遭遇した範囲では、以下の2つの記法でエラーになることが確認できています。

  • DECLARE , CREATE

    DECLARE , CREATE を使うと、事前に変数や関数の内容を宣言できます。 DECLARE , CREATE を含むクエリを書くと、BigQueryでは正常に動きますが、LookerStudioではエラーになります。

    これを回避するには、大人しくLookerStudioのパラメータ機能を使うなどするのが良さそうです。

  • QUALIFY句

    QUALIFY句は WHERE句と異なり、Window関数の結果で絞り込めるという特徴があります。 基本的に、QUALIFY句を使ったクエリは、BigQueryでは正常に動きますが、LookerStudioではエラーになります。

    これは QUALIFY句と WHERE句を併用することで回避できるようです（なにゆえ・・・）

    （参考記事：BigQuery "QUALIFY" Function is not supported by data studio?）

    なので QUALIFY句を使うときは、なるべく習慣的に WHERE句をつけるようにしています。

SELECT
    column1
    ,ROW_NUMBER()OVER(PARTITION BY xx ORDER BY yy） AS rank
    FROM
        table
    WHERE
        true -- エラー回避のためだけに追加
    QUALIFY
        rank = 1

2. LAST_VALUEは使い方を間違えると、最後の値を返さないことがある

LAST_VALUEを使っても、なぜか最後の値が返ってこないことがあります。

これは、LAST_VALUEの処理範囲がデフォルトで「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（最初から現在までの行）」になっているためです（公式ドキュメント）。

つまり、以下のようなクエリを書いた場合、

SELECT
    LAST_VALUE(aa)OVER(PARTITION BY bb ORDER BY ymd） AS rank
    FROM
        table

① まずはymdが古い順に並び替える

② 最初から現在の行までで、ymdが最新の場所を探す → 現在の行になる

③ 現在の行のaaが返ってきてしまう

ということになっているようです。

これを回避するには、処理範囲を「ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（最初から最後の行まで）」と指定するか、以下のようにFIRST_VALUEとDESCを使う形にするのが良さそうです。

SELECT
    FIRST_VALUE(aa)OVER(PARTITION BY bb ORDER BY ymd DESC） AS rank
    FROM
        table

3. 日付の表示フォーマットでYYYY を使うと、正しい西暦が返ってこないことがある

LookerStudioなどの日付の表示フォーマットで、西暦の表示形式に「YYYY」を指定すると、正しい西暦が返ってこないことがあります。

これは、YYYYが単純な西暦ではなく、「その暦週の基準年」を返しているからでした。

簡単に言うと、「新年度の1月1日と同じ週に属する日については、新年度に属することにする」という考え方になっているそうです。

単純な西暦を出したい場合は、大文字の「YYYY」ではなく小文字の「yyyy」を使わなければならないようです。暦週の基準年を出したいケースはそうないと思うので、とりあえず「西暦は小文字」と覚えてしまうのが良さそうです。

もはや SQLの話ではないですが、当時こちらの答えに辿り着くまでにちょっぴり苦労しており、どうしても紹介したかったので最後にご紹介しました。

おわりに

ここまで、私が経験してきた BigQuery・LookerStudio のニッチな落とし穴についてまとめてみました。

今回の記事が、少しでもみなさまの業務のお役に立てれば幸いです。

（「それはニッチな落とし穴でもなんでもないよ」「他にもこんなのがあるよ」など、ご意見ご感想ありましたら、当ブログやXなどでコメントいただけますと幸いです）

分析の正確性を担保するためには、このような落とし穴を知っておくことも大事ですが、実際には、これらを理解したところで、毎回1つもミスをせず、一発で正しいクエリを書きあげることは難しいのではないかと思います。

常に自分の書いたクエリを疑いつつ、実際のデータを見て検証したり、別の指標と比較して違和感がないか確かめたり、必要に応じて他の人にクエリのレビューをお願いしたり、といった工夫の方が、個人的には大事なのかなと思っています。

We’re Hiring!

タイミーでは、一緒に働くメンバーを募集しています。

https://hrmos.co/pages/timee/jobs

カジュアル面談も実施していますので、少しでも興味を持っていただけましたら気軽にお申し込みください！

個人的にもアナリストやデータ関連職の方と繋がりたいと思っているので、よければXのフォローもよろしくお願いします。