稍微需要注意的点:
- 跨天活跃的数据,算作两条活跃记录。
- 拿到活跃日期,和最早的活跃日期(要开窗记录),就可以区分出是否是新用户
SQL
with t1 as ( -- t1表 uid | 活跃日期 | 最早活跃日期
SELECT
uid,DATE(in_time) dt,DATE(MIN(in_time) over(PARTITION BY uid)) dt_first
FROM
tb_user_log
UNION -- 防止出现跨天的数据,所以根据in_time和out_time UNION一下
SELECT
uid,DATE(out_time) dt,DATE(MIN(in_time) over(PARTITION BY uid)) dt_first
FROM
tb_user_log
)
SELECT
dt, COUNT(DISTINCT uid) dau , ROUND( sum(IF(dt=dt_first,1,0))/count(DISTINCT uid),2) uv_new_ratio
FROM
t1
GROUP BY
dt
ORDER BY
dt