Athenaで連続した日付のデータを生成して集計データと突き合わせる

BigQueryで連続した日付のデータを生成して集計データと突き合わせる - notebook

swfz.hatenablog.com

これのAthenaバージョン

サンプルデータの生成などは下記あたりを参考にして生成した

docs.aws.amazon.com

WITH report_sample_array AS(
    SELECT ARRAY [ CAST(
            ROW('2021-10-01', 'hoge', 100) AS ROW(date DATE, item VARCHAR, sales INTEGER)
        ),
        CAST(
            ROW('2021-10-03', 'hoge', 150) AS ROW(date DATE, item VARCHAR, sales INTEGER)
        ),
        CAST(
            ROW('2021-10-05', 'hoge', 100) AS ROW(date DATE, item VARCHAR, sales INTEGER)
        ) ] AS report_sample
),
report_sample AS (
    SELECT report.date,
        report.item,
        report.sales
    FROM report_sample_array,
        UNNEST(report_sample) AS t(report)
),
date_list_array AS (
    SELECT (
            SELECT SEQUENCE(
                    CAST('2021-10-01' AS DATE),
                    CAST('2021-10-10' AS DATE),
                    INTERVAL '1' DAY
                )
        ) AS per_day
),
date_list AS (
    SELECT date
    FROM date_list_array
        CROSS JOIN UNNEST(per_day) AS t(date)
)
SELECT date_list.date AS date,
    COALESCE(report_sample.sales, 0) AS sales
FROM date_list
    LEFT JOIN report_sample ON(date_list.date = report_sample.date)
ORDER BY date

結果

date	sales
2021-10-01 00:00:00.000	100
2021-10-02 00:00:00.000	0
2021-10-03 00:00:00.000	150
2021-10-04 00:00:00.000	0
2021-10-05 00:00:00.000	100
2021-10-06 00:00:00.000	0
2021-10-07 00:00:00.000	0
2021-10-08 00:00:00.000	0
2021-10-09 00:00:00.000	0
2021-10-10 00:00:00.000	0

流れ

WITH句でreport_sampleとdate_listを生成
- report_sample
  - report_sample_arrayからUNNESTを用いて実際のレポートデータを想定した形式にしてSELECTした結果
    - t(hogehoge)と書くことでクエリ内でUNNESTした列に名前を付けている
  - report_sample_arrayではサンプル用のデータを生成
- date_list
  - date_list_arrayからUNNESTを用いて1行1日に変換
  - date_list_arrayではsequenceを使って連続した日付の配列を生成
最後に生成した2つのデータをLEFT JOINで結合