Snowflake：OneHotエンコーディングをunpivotで処理してみた

## 自己紹介
ちゅらデータ新卒一年目の萬前恐樹と申します。
今日は、初案件で私が書いたSQLの中で個人的に面白かったものを紹介したいと思います。
SQLは、[Snowflake](https://docs.snowflake.com/)に準拠します:sunglasses:

## やりたいこと
次のアクセスログに於いて、人別に最もアクセス頻度のある時間帯を求めたいです。

| job_id | user_id | event_date          | url        | 
| ------ | ------- | ------------------- | ---------- | 
| 10     | AAA     | 2023-12-11 00:00:00 | qiita1.com | 
| 10     | BBB     | 2023-12-11 12:00:00 | qiita2.com | 
| 20     | AAA     | 2023-12-11 00:00:00 | qiita3.com | 
| 20     | BBB     | 2023-12-11 21:00:00 | qiita3.com | 
| 20     | CCC     | 2023-12-11 21:00:00 | qiita3.com | 
| 30     | AAA     | 2023-12-11 21:00:00 | qiita2.com | 

## 実際書いたコード
まずは、インポート時に時間帯をダミー変数化します。
```sql
with access_log as (
    select
        job_id
        , user_id
        , event_date
        , extract(hour from event_date) as visit_hour
        , case
            WHEN visit_hour BETWEEN 0 AND 6 THEN '0時~6時'
            WHEN visit_hour BETWEEN 7 AND 12 THEN '7時~12時'
            WHEN visit_hour BETWEEN 13 AND 18 THEN '13時~18時'
            WHEN visit_hour BETWEEN 19 AND 23 THEN '19時~23時'
            ELSE '不明'
          end as visit_hourzone
        , iff(visit_hourzone = '0時~6時', 1, 0) as visit_hourzone_1
        , iff(visit_hourzone = '7時~12時', 1, 0) as visit_hourzone_2
        , iff(visit_hourzone = '13時~18時', 1, 0) as visit_hourzone_3
        , iff(visit_hourzone = '19時~23時', 1, 0) as visit_hourzone_4
        , iff(visit_hourzone = '不明', 1, 0) as visit_hourzone_5
    from
        access_log
)
```

次に、人別に処理してしまいます。
```sql
, aggregated_data as (
    select
        user_id
        , sum(visit_hourzone_1) as visit_hourzone_1
        , sum(visit_hourzone_2) as visit_hourzone_2
        , sum(visit_hourzone_3) as visit_hourzone_3
        , sum(visit_hourzone_4) as visit_hourzone_4
        , sum(visit_hourzone_5) as visit_hourzone_5
    from
        access_log
    group by
        user_id
```

ここで今回の主役である、unpivot君が登場します。使い方は、[Snowflakeのドキュメント](https://docs.snowflake.com/sql-reference/constructs/unpivot)がわかりやすいですが、簡単に説明すると、横に広がったデータを縦に整理する演算をしてくれて、列を行に変換し、新しい列名と値を付与できます。
qualify句を使用していますが、時間帯がダブった場合の処理を行なっていて、ここでは早い時間帯が優先されます。
```sql
, convert_colunmname_hourzone as (
    select
        user_id
        , hourzone
    from
        aggregated_data
        unpivot(count_days for hourzone in(
            visit_hourzone_1
            ,visit_hourzone_2
            ,visit_hourzone_3
            ,visit_hourzone_4
            ,visit_hourzone_5
            ))
    qualify row_number() over (partition by user_id order by count_days desc) = 1
)
```

最後にダミー変数名を元に戻して完成です:sunglasses:
```sql
, convert_dummydata as (
    select
        user_id
        , case hourzone
            when 'VISIT_HOURZONE_1' then '0時~6時'
            when 'VISIT_HOURZONE_2' then '7時~12時'
            when 'VISIT_HOURZONE_3' then '13時~18時'
            when 'VISIT_HOURZONE_4' then '19時~23時'
            when 'VISIT_HOURZONE_5' then '不明'
          end as most_frequent_access_hour_category
    from
        convert_colunmname_hourzone
```
## 結果
欲しいテーブルが得られました！！！
| user_id | most_frequent_access_hour_category | 
| ------- | ---------------------------------- | 
| AAA     | 0時~6時                            | 
| BBB     | 7時~12時                           | 
| CCC     | 19時~23時                          | 

## 最後に
いかがでしたか？（笑）
もっと良い方法があれば、コメントで教えて下さると勉強になります！

### 主な使用したSQL句
- extract
- case
- unpivot
- qualify