BiqQueryでGENERATE_DATE_ARRAY関数を使用して日ごとの売上データを集計する

背景

商品ごとの売上記録を日ごとに集計し、ダッシュボードやスプレッドシートへ日付が過去のデータから順に出力する。
ここで、商品の購入数を日ごとに集計する際、 過去の日付の購入数データから順にダッシュボードやスプレッドシートへ出力する。
しかし、購入実績がない日付のデータも購入数0として表示させたくても、購入履歴のある日付ごとのみで GROUP BY 句により集約されるため、購入のない日付が飛んだ状態で表示されてしまう。

例）
商品テーブルのイメージ
商品Aについて、時系列に注文された購入情報が格納されているが、購入された日付の情報のみ格納されている。

ダッシュボード等へのグラフ出力イメージ
上記テーブルから日付（order_dt）ごとに GROUP BY 句で集計した場合、注文のない日付は注文点数0としたいが、2022/06/02、2022/06/06の商品Aの注文がないため、以下のグラフの2022/06/02、2022/06/06の日付データが飛んだ状態となってしまう。

目的

集計対象の商品の注文がない日付でも購入数なしデータとして情報を持たせ、ダッシュボード、あるいはスプレッドシートへ表示させるため、そこで GENERATE_DATE_ARRAY 関数を使用し、集計の期間に必要な全日付を取得して売上記録を日付ごとにソートする。

説明

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

・start_date : 開始日を入れる
・end_date : 終了日を入れる
・INTERVAL : INT64_exprはINT64、date_partはDAY、WEEK、MONTH、QUARTER または YEAR を指定する

対応方法

連続した日付データを取得するため、ここで GENERATE_DATE_ARRAY 関数を用いる。
Step1）
GENERATE_DATE_ARRAY 関数を用いて、集計に必要な期間の基準となる日付（以下、基準日）を抽出する。

SELECT
    GenarateDate
FROM
    UNNEST(GENERATE_DATE_ARRAY('2022-06-01', '2022-06-10')) AS GenarateDate

Step2)
対象期間の売上データを日付ごとに集計し、Step1で抽出した基準日へ購入日付を結合キーとして外部結合する。

--データの抽出の対象となる期間を日付ごとに全て取得
WITH GENARATE_TIME AS (
SELECT
    GenarateDate
FROM
    UNNEST(GENERATE_DATE_ARRAY('2022-06-01', '2022-06-10')) AS GenarateDate
)

SELECT
    GenarateDate
    ,GoodsID
    ,GoodsName
    ,DailyQuantity
FROM 
    GENARATE_TIME
    LEFT JOIN (
        SELECT
            OrderDate
            ,GoodsID
            ,GoodsName
            ,SUM(Quantity) AS DailyQuantity
        FROM
            `test_dataset.samle_order_table` order
        WHERE
            OrderDate BETWEEN '2022-06-01' AND '2022-06-10'
        GROUP BY
            OrderDate
            ,GoodsID
            ,GoodsName
        ) ON GenarateDate = OrderDate
;

クエリの実行結果

クエリの実行結果としては、2022/06/01～2022/06/10の期間で日付ごとに商品Aの購入数の合計を集計するというものである。

「GENERATE_DATE_ARRAY 関数による基準日の使用なし」と「GENERATE_DATE_ARRAY 関数による基準日の使用あり」を比較すると、なしの場合は商品Aの購入のなかった 2022/06/02、2022/06/05～2022/06/06、2022/06/08～2022/06/09は、レコード自体が存在しない。

それに対し、ありの場合は購入のなかった日でも連続した日付でデータを取得できている。購入数が NULL となっているが、気になるようであればクエリで0に更新するか、データポータルへ表示するのであればデータポータル上でも編集が可能となるが、本稿のテーマ外であるため省略。

GENERATE_DATE_ARRAY関数による基準日の使用なし

GENERATE_DATE_ARRAY関数による基準日の使用あり

参考

GENERATE_DATE_ARRAY 関数の使用規約については以下のリンクを参照
https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions?hl=ja#generate_date_array