【Athena】SQLで日付マスタを使った集計

Athenaを使ってデータの集計・レポートをする際に、アクセス数の推移など、日単位でデータをまとめることがよくあります。

group byなど集計方法はいろいろあると思いますが、私は次のような状態になってしまうことがよくあります。

#### 期待する出力

|日付|アクセス数|
|---|--:|
|9/1|5|
|9/2|8|
|9/3|0|
|9/4|0|
|9/5|5|
|9/6|7|

#### 実際の出力

|日付|アクセス数|
|---|--:|
|9/1|5|
|9/2|8|
|9/5|5|
|9/6|7|

データが存在しない日（9/3、9/4）はゼロとして集計したいのですが、データがそもそも存在しないので、レコード自体が無くなってしまうという状態です。

集計結果を元に分析する際に、「レコード無し」と「0」では結果が変わってしまうこともあります。

このようなレコード無しの日付の課題についてAthena（SQL）でどうするかを備忘を兼ねて書きます。

### 前提

月次レポートとして、前月1カ月間のサービス利用者数を日毎に集計するというタスクを想定します。

レポートでは、データが存在しない日は「利用者数0」として出力することとします。

### クエリの方針

* 集計期間の日付マスタを用意します
* 目的のデータを道なりに日毎に集計します
* 目的のデータと日付マスタをJOINします
    * JOINする際に、それぞれの日のデフォルト値を0にします

### 実際のクエリ

まず日付マスタを生成します。
今回は、集計作業をしている日を10/1として、集計期間は9/1～9/30とします。

`2022-09-01, 2022-09-02, ... , 2022-09-30`という出力が期待値となります

この処理を細かく分解すると以下の通りです。

* 前月の月初の日付を取得する…①
* 前月の月末の日付を取得する…②
* ①～②の期間を、1日単位で区切る…③
* ③の時点ではArrayになっているので、後段の処理で使うために行持ちに変換する

#### 前月の月初の日付を取得する
→`date_trunc('month', xxx)`とすると日付が切り捨てられ、date_truncしたものをtimestamp型にするとその月の1日が設定されています。
```sql
cast(date_trunc('month', date_add('month', -1, now())) as timestamp)
```
#### 前月の月末の日付を取得する
→月初の日の1日前は前月の月末日
```sql
cast(date_add('day', -1, date_trunc('month', now())) as timestamp)
```
#### 1日単位で区切る
`sequence`関数を使います。`sequence(開始日, 終了日, 間隔)`を指定すると、Arrayを返してくれます。
ドキュメント：https://prestodb.io/docs/current/functions/array.html

引数にはtimestamp型を指定する必要があります。`date_trunc`ではtimestamp with timezone型が返されるので注意です。
```sql
sequence(
    cast(date_trunc('month', date_add('month', -1, now())) as timestamp)
    , cast(date_add('day', -1, date_trunc('month', now())) as timestamp)
    , interval '1' day
)
```

#### フォーマットを調整する
`sequence`関数で日付のArrayが得られますが、timestamp型のため後段の処理で扱いずらい場合があります。
ここでは`transform`関数で任意の文字列に変換します。
あわせて、Arrayのデータを行持ちに変換します。
```sql
with dt_list as (
    select
        dt
    from (
        select
            transform(
                sequence(/*省略*/)
                , x -> format_datetime(x, 'YYYY-MM-dd')
            ) as term
    )
    -- fromで取得した日付文字列リストを行持ちに展開する
    cross join
        unnest (term) as t(dt)
),
```
#### サンプルとして集計データを用意する
今回は欠損値を埋めたいだけなので、集計データ自体はざっくり用意します。
```sql
pv_list as (
    select
        t.pv, t.dt
    from (
        select
            array [1,2,3,4,5] as pv,
            array [
                '2021-09-02',
                '2021-09-03',
                '2021-09-10',
                '2021-09-20',
                '2021-09-30'
            ] as dt
    )
    cross join
        unnest (pv, dt) as t(pv, dt)
)
```

#### 日付マスタと集計データをJOIN

日付マスタに対して集計データをLEFT JOINすることで、レコードの抜けを防ぎます。
また、`coalesce()`でデータが無い場合のデフォルト値を0とします。

```sql
with dt_list as (
    -- 日付マスタ
),
pv_list as (
    -- サンプル集計データ
)
select
    d.dt
    , coalesce(p.pv, 0)
from dt_list as d
    left join pv_list as p
        on d.dt = p.dt
order by d.dt asc
```

クエリをまとめると以下の通りです。

```sql
with dt_list as (
    select
        dt
    from (
        select
            transform(
                sequence(
                    cast(date_trunc('month', date_add('month', -1, now())) as timestamp)
                    , cast(date_add('day', -1, date_trunc('month', now())) as timestamp)
                    , interval '1' day
                ),
                x -> format_datetime(x, 'YYYY-MM-dd')
            ) as term
    )
    cross join
        unnest (term) as t(dt)
),
pv_list as (
    select
        t.pv, t.dt
    from (
        select
            array [1,2,3,4,5] as pv,
            array [
                '2021-09-02',
                '2021-09-03',
                '2021-09-10',
                '2021-09-20',
                '2021-09-30'
            ] as dt
    )
    cross join
        unnest (pv, dt) as t(pv, dt)
)
select
    d.dt,
    coalesce(p.pv, 0)
from dt_list as d
    left join pv_list as p
        on d.dt = p.dt
order by d.dt asc
```