自己紹介
ちゅらデータ新卒一年目の萬前恐樹と申します。
今日は、初案件で私が書いたSQLの中で個人的に面白かったものを紹介したいと思います。
SQLは、Snowflakeに準拠します
やりたいこと
次のアクセスログに於いて、人別に最もアクセス頻度のある時間帯を求めたいです。
| 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 |
実際書いたコード
まずは、インポート時に時間帯をダミー変数化します。
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
)
次に、人別に処理してしまいます。
, 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のドキュメントがわかりやすいですが、簡単に説明すると、横に広がったデータを縦に整理する演算をしてくれて、列を行に変換し、新しい列名と値を付与できます。
qualify句を使用していますが、時間帯がダブった場合の処理を行なっていて、ここでは早い時間帯が優先されます。
, 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
)
最後にダミー変数名を元に戻して完成です
, 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