顧客IDと各イベントの日付（購入日、クーポン取得日、クーポン利用日 etc）が格納されているデータをGoogle BigQueryで日付ごとの各イベントの発生件数に集計しなおすクエリの例をご紹介します。
イメージとしては以下の画像のように、左側の形式のデータから右側の形式のデータに変形します。
ダミーデータとして、以下のように「顧客ID」「問い合わせ受領日」「予約受付日」「相談日」が格納されているデータを想定します。
このデータから問い合わせ件数、予約件数、相談件数をそれぞれ日付ごとにカウントしたデータに集計していきます。
generate_date_array関数と、cross join ＋ count(case when ~) で実現可能です！
日付データを日別の件数で集計するクエリ
実際のクエリ例は以下のとおりです。
with d_array as (
select
date
from
unnest(generate_date_array('2023-09-01', '2023-09-30', interval 1 day)) as date
), event as (
select
id
,inquiry_received_date
,reserved_date
,consultation_date
from
`project.dataset.table`
)
select
d.date
,count(case when d.date = e.inquiry_received_date then e.id end) as inquiry_receive_cnt
,count(case when d.date = e.reserved_date then e.id end) as reserve_cnt
,count(case when d.date = e.consultation_date then e.id end) as consultation_cnt
from
d_array as d
cross join event as e
group by
1
order by
1
ポイントをそれぞれ解説していきます。
unnest + generate_date_arrayで日付の配列を作成
まず、最初のwith句でgenerate_date_array()をunnestでネストし、1日単位の日付の配列を生成します。
generate_date_array(‘YYYY-MM-DD’, ‘YYYY-MM-DD’, interval 1 day) で日付の起点日と終了日を指定し、1dayのintervalで日付配列を作っています。
この日付の配列をunnestし、with句内で展開します。
with d_array as (
select
date
from
unnest(generate_date_array('2023-09-01', '2023-09-30', interval 1 day)) as date
), event as (
select
id
,inquiry_received_date
,reserved_date
,consultation_date
from
`project.dataset.table`
)
次のwith句では、id、inquiry_received_date、reserved_date、consultation_dateをそれぞれselectします。
仮想テーブルをcross joinし、count + case whenで日付ごとにカウント
先のwith句で生成した仮想テーブル同士をcross joinし、総当たりで結合します。
そのうえで、count(case when 配列の日付 = イベントの日付 then id end) とし、イベント（「お問い合わせ受領日」「予約日」「相談日」）の日付が配列の日付と合致する場合に、顧客IDを返してカウントします。
select
d.date
,count(case when d.date = e.inquiry_received_date then e.id end) as inquiry_receive_cnt
,count(case when d.date = e.reserved_date then e.id end) as reserve_cnt
,count(case when d.date = e.consultation_date then e.id end) as consultation_cnt
from
d_array as d
cross join event as e
group by
1
order by
1cross joinとcount(case when ~ then ~)を組み合わせることで日付ごとのイベント発生件数をカウントして集計することができます。
実際にクエリを実行すると、以下のように「お問い合わせ受領件数」「予約件数」「相談件数」を日付ごとに集計できます！
まとめ
generate_date_array関数は使用場面は多くないものの、こういった場面で役立ちます！
cross joinも同じく使用頻度は高くはありませんが、覚えておいて損なしです。
※MySQLの場合は、以下の記事で再現が可能です（with recursive句を使います）。
