SQLメモ　日別過去30日UU

背景

業務で使えたSQLを自分のためにも記事に残しておこうと思いますので参考になる方は参考にしてください。

環境

BIGQUERY

利用するテーブル

購買のトランザクションデータ

date：購買時の日付
id：購入者のid
amount：金額

table

集計したいこと

日別にその日から過去30日のUU

例えば、2024-01-01の行には2023-12-03から2024-01-01の間に購買があった購入者のユニークな人数が入るものです。

アウトプットイメージ

コード

--日付準備
WITH date_t AS(
SELECT
    DISTINCT date
FROM
    UNNEST(GENERATE_DATE_ARRAY(DATE("2024-02-01"), CURRENT_DATE())) AS date
)
--用意した各日付にその日から過去30日のデータをJOINして集計
SELECT
    date_t.date
    , COUNT(DISTINCT id) AS UU
FROM
    date_t
LEFT JOIN
    table
ON
    date_t.date BETWEEN table.date AND DATE_ADD(table.date, INTERVAL 29 DAY)
GROUP BY
    date_t.date
ORDER BY
    date_t.date

説明

最初にWITH句で集計したい期間の日付のデータをdate_tとして準備して、その後用意した各日付にその日から過去30日のデータをJOINして集計しています。
今回のコードのポイントはON句でBETWEENを利用しているところです。
用意したdate_tテーブルの集計期間の例えば2024-02-01の行に対しては、tableテーブルのデータが日付に29日を足した日付までに2024-02-01が入っていた場合に紐づけられます。つまり2024-01-03~2024-02-01の日付のデータはその日から29日後の日付の間に2024-02-01が入るので紐づけられます。
これをdate_t.dateでGROUP BYすれば日別に当日含め過去30日のUUなどが出せました。

さいごに

以上、少しでもご参考になれば幸いです。