今回の課題
新規ユーザー、リピーター、カムバックユーザーを分けてカウントするためのクエリを作成する段階で、
下記の問題が発生したためメモ
LAGウィンドウ関数を使用して、
一か月前の日付をlast_monthカラムに格納しようとすると、
1カ月前ではなく、1行前の日付が格納されてしまうなどの不具合が発生した。
date_sub関数を使用して、一か月前の日付を表現しようとしたが、
WITH句でdate_formatで日付を変換していることにより、
文字列型で日付データが生成されてしまっているため、その方法は使えなかった。
NG例
使用テーブル
usersテーブル
| user_id | register_date |
|---|---|
| 001 | 2022-12-01 |
| 002 | 2022-12-02 |
| 003 | 2022-12-10 |
| 004 | 2022-12-11 |
| 005 | 2022-12-25 |
action_logテーブル
| user_id | stamp |
|---|---|
| 001 | 2022-12-03 00:00:00 |
| 002 | 2022-12-03 00:00:00 |
| 004 | 2022-12-13 00:00:00 |
| 003 | 2022-12-15 00:00:00 |
| 004 | 2023-01-16 00:00:00 |
| 003 | 2023-01-17 00:00:00 |
| 005 | 2023-01-25 00:00:00 |
| 003 | 2023-02-26 00:00:00 |
| 002 | 2023-03-27 00:00:00 |
| 001 | 2023-03-31 00:00:00 |
使用クエリ
/* ログテーブルとユーザーマスタを結合。日付を月ベースに変換する。 */
with monthly_action_users as (
select
al.user_id
, date_format(a.stamp, "%Y-%m") as action_month
, date_format(u.register_date, "%Y-%m") as register_month
from
action_log as l
left join users as u
on al.user_id = ms.user_id
)
/* LAGウィンドウ関数で、1行前のaction_monthを格納するlast_monthカラムを生成 */
, monthly_last_action_users as (
select
action_month
, lag(action_month) over(partition by user_id order by action_month) as last_month
, register_month
, user_id
from
monthly_action_users
)
select
*
from
monthly_last_action_users
;
出力テーブル
| action_month | last_month | register_month | user_id |
|---|---|---|---|
| 2022-12 | NULL | 2022-12 | 001 |
| 2022-12 | NULL | 2022-12 | 002 |
| 2022-12 | NULL | 2022-12 | 004 |
| 2022-12 | NULL | 2022-12 | 003 |
| 2023-01 | 2022-12 | 2022-12 | 004 |
| 2023-01 | 2022-12 | 2022-12 | 003 |
| 2023-01 | NULL | 2022-12 | 005 |
| 2023-02 | 2023-01 | 2022-12 | 003 |
| 2023-03 | 2022-12 | 2022-12 | 002 |
| 2023-03 | 2022-12 | 2022-12 | 001 |
この出力結果のように、LAGウィンドウ関数を使用するだけでは、
last_monthカラムで、前月の日付を取得することができなかった。
成功例
結論から言うと、日付データがtimestamp型の段階で、
一ヵ月前の日付を取得して、date_format関数で月ベースの日付を取得する方法を選べばよかった。
/* 日付データを月ベースに変換する。last_monthカラムはtimestamp型の段階で一ヵ月前の日付を取得しておく */
with monthly_user_action as (
select
distinct u.user_id
, date_format(u.register_date, "%Y-%m") as register_month
, date_format(l.stamp, "%Y-%m") as action_month
, date_format(date_sub(date(timestamp(l.stamp)), interval 1 month), "%Y-%m") as last_month
from
users as u
left join action_log as l
on u.user_id = l.user_id
)
select
*
from
monthly_user_action
このクエリを使えば、last_monthカラムに、action_monthの一ヵ月前の日付を格納することができた。
新規ユーザー、リピーター、カムバックユーザーを分けてカウントする。
下記のクエリを使えば、実現できる。
■補足
・リピーター = 前月も利用しているユーザー
・カムバックユーザー = 前月は利用していないが、前々月以前に利用しているユーザー
/* 日付データを月ベースに変換する。last_monthカラムはtimestamp型の段階で一ヵ月前の日付を取得しておく */
with monthly_user_action as (
select
distinct u.user_id
, date_format(u.register_date, "%Y-%m") as register_month
, date_format(l.stamp, "%Y-%m") as action_month
, date_format(date_sub(date(timestamp(l.stamp)), interval 1 month), "%Y-%m") as last_month
from
users as u
left join action_log as l
on u.user_id = l.user_id
),
/* case式でaction_monthをlast_monthやregister_monthを比較して場合分けをする */
monthly_user_with_type as (
select
action_month
, user_id
, case
when register_month = action_month then 'new_user'
when last_month = lag(action_month) over(partition by user_id order by action_month) then 'repeat_user'
else 'come_back_user'
end as user
, last_month
from
monthly_user_action
)
select
action_month
, count(user_id) as mau
, count(case when user = 'new_user' then 1 end) as new_users
, count(case when user = 'repeat_user' then 1 end) as repeat_users
, count(case when user = 'come_back_user' then 1 end) as come_back_users
from
monthly_user_with_type
group by
action_month
出力テーブル
| action_month | mau | new_users | repeat_users | come_back_users |
|---|---|---|---|---|
| 2022-12 | 4 | 4 | 0 | 0 |
| 2023-03 | 2 | 0 | 0 | 2 |
| 2023-01 | 3 | 0 | 2 | 1 |
| 2023-02 | 1 | 0 | 1 | 0 |
以上です。