题解 | 每天的日活数及新用户占比

# 找到每天的新用户t1 根据uid分组，得到每个uid的最早活跃时间
select uid,min(date_format(in_time,'%Y-%m-%d')) as zuizao 
from tb_user_log
group by uid
# 找到每天活跃的用户t2 根据union合并去重
select uid,date_format(in_time,'%Y-%m-%d') as dt from tb_user_log
union select uid,date_format(out_time,'%Y-%m-%d') as dt from tb_user_log
# 用表t2 左链接 t1 然后按照t2.dt分类，那么count(dictinct t2.uid)是那天的所有活跃,count(dictinct t1.uid)是那天的新增人数
select t2.dt,count(distinct t2.uid) as dau,round(count(distinct t1.uid)/count(distinct t2.uid),2) as uv_new_ratio
from (select uid,date_format(in_time,'%Y-%m-%d') as dt from tb_user_log
union select uid,date_format(out_time,'%Y-%m-%d') as dt from tb_user_log) as t2 left join (select uid,min(date_format(in_time,'%Y-%m-%d')) as zuizao 
from tb_user_log
group by uid) as t1 on t2.dt=t1.zuizao
group by t2.dt
order by t2.dt asc