BigQueryで異なる粒度の集計をまとめて実施する【Sexy Tech for You #11】

データサイエンティストの阿部です。この1年間、毎週土曜日に8時間副業していました。結果、週休二日制のありがたみがわかりました。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第11話を配信しました。例えばDAUとMAUを一括集計するなど、同一テーブルから粒度が異なる集計結果をまとめて得たいケースの対応方法について、3パターン紹介しました。以下のような集計結果がほしいイメージです。

集計粒度	日付	値
daily	2020-01-01	50
daily	2020-01-02	30
monthly	2020-01-01	80

3パターンいずれも同じ結果を得られますが、下記のようなメリット・デメリットがあると私は考えます。

動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube

BigQuery クエリ

BigQueryの一般公開データセットbigquery-public-data.chicago_taxi_trips.taxi_tripsで、DailyとMonthlyの件数をカウントする場合の例です。

パターン1：UNIONを用いる

SELECT
    "daily" AS aggry_type,
    DATE(trip_start_timestamp) AS dt,
    COUNT(1) AS cnt
FROM
    `bigquery-public-data.chicago_taxi_trips.taxi_trips` 
GROUP BY
    1,2

UNION ALL

SELECT
    "monthly" AS aggr_type,
    DATE_TRUNC(DATE(trip_start_timestamp), MONTH) AS dt,
    COUNT(1) AS cnt
FROM
    `bigquery-public-data.chicago_taxi_trips.taxi_trips`
GROUP BY
    1,2

パターン2：ROLLUPを用いる

SELECT
    CASE 
        WHEN day IS NOT NULL THEN "daily"
        WHEN month IS NOT NULL THEN "monthly"
        ELSE NULL END AS aggr_type
    ,COALESCE(day, month) AS dt
    ,cnt
FROM(
SELECT
    DATE(trip_start_timestamp) AS day,
    DATE_TRUNC(DATE(trip_start_timestamp), MONTH) AS month,
    COUNT(1) AS cnt
FROM
    `bigquery-public-data.chicago_taxi_trips.taxi_trips`
GROUP BY
    ROLLUP(month, day)
)
WHERE
    month IS NOT NULL 
ORDER BY
    1,2

パターン3：CONCATとSPLITを用いる [オススメ]

WITH tmp AS (
SELECT
    CONCAT(dt, "#", DATE_TRUNC(dt, MONTH)) AS dt,
    CONCAT("daily", "#", "monthly") AS aggr_type
FROM(
SELECT
    DATE(trip_start_timestamp) AS dt
FROM
    `bigquery-public-data.chicago_taxi_trips.taxi_trips`
))

SELECT
    SPLIT(aggr_type,"#")[SAFE_OFFSET(offset)] AS aggr_type,
    dt,
    COUNT(1) AS cnt
FROM
    tmp,UNNEST(SPLIT(dt,"#")) AS dt WITH OFFSET AS offset
GROUP BY
    1,2

補足

ヤプリでは、アプリごとにユーザーの起動サイクルや用途が異なるため、複数粒度に対応できるテーブルを作成するケースによく遭遇します。その中で得た知見を今回まとめてみました。

パターン2で包含関係にない粒度同士の場合は、正確な値を得られません。今回の例だと、週や曜日をROLLUPに追加しても期待した結果にならないので気をつけましょう（クエリ自体は実行できます）。

パターン3でCONCATする際の接続詞は、元の値に含まれない文字であれば何でも大丈夫ですが、ここでは#としています。また本例でのaggr_typeのように、集計粒度と連動する値を作成したい場合は、集計粒度のカラムと同様にCONCATしたあとにOFFSETで値を取得することができます。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。