More than 1 year has passed since last update.

BigQueryで複数データを日ごとに集計してLookerStudioで表示する方法

Posted at 2023-09-24

はじめに

複数のデータを集計して1つのグラフで表示したい時ってありますよね？私はあります。
でも日ごとに集計するとデータが存在しない日が欠けてしまって、うまく出せないーということがあります。
昔からそんなシーンが稀に良くありましたが、ググってもバシッと見つかることが無く、無駄に時間を割いたりしていました。

ということで書いておきたいと思います。
もし同じ悩みで困っている人の助けになったら嬉しいです。

環境情報

BigQuery
- データセット名は data_set としています
Looker studio

前提

データは以下のような情報となっていることを想定しています。

customerデータ
- id, name, status, create_date, update_dateを持っている
- カスタマーが登録した場合にデータが挿入される
providerデータ
- id, name, status, create_date, update_dateを持っている
- カスタマーが登録した場合にデータが挿入される

概要
サービスにproviderとcustomerのロールがそれぞれあり、登録した場合にレコードが追加される。
サービスの状況を把握するため日ごとの登録者数を可視化したい。

BigQueryでデータを集計

withの利用

集計にあたり複数のテーブルを利用するため、それぞれ内容を解りやすくするためにwith句を利用します。構文はこんな感じです。

-- 1つ目
with [仮テーブル名] as (
-- 抽出SQL
)

--2つ目以降
, [仮テーブル名] as (
-- 抽出SQL
)

日ごとに集約するためにカレンダーデータを用意

特定の日（この例では 2020-01-01）を起点として実行した日までの日付データを網羅します。

with calendar AS (
  SELECT
    create_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 DAY)) AS create_date
)

customerとproviderを集計

それぞれ、create_date でグループ化してレコード数をカウントします。

, provider as (
  select 
    date(create_date) create_date,
    count(*) provider_count
  from `data_set.provider` 
  group by create_date
)

, customer as (
  select 
    date(create_date) create_date,
    count(*) customer_count
  from `data_set.customer` 
  group by create_date
)

日で結合

カレンダーとして抽出した calendar を軸にそれぞれの集計結果を結合します。
対象日のデータが存在しない場合は 0 となるようにするため、表結合は left outer join を使い、null の場合は 0 となるようにしておきます。

select
  calendar.create_date,
  ifnull(provider_count, 0) provider_count,
  ifnull(customer_count, 0) customer_count,
from calendar
  left outer join provider on calendar.create_date = provider.create_date
  left outer join customer on calendar.create_date = customer.create_date

これで日ごとに複数のデータの集計結果がまとまりました。