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

1.先得到用户活跃表(用union去重)

select uid, date(in_time) dt from tb_user_log
union 
select uid,date(out_time) dt from tb_user_log

2.得到每名用户最早出现的时间

select uid,min(date(in_time)) new_time from tb_user_log
group by uid 

两表连接

(select uid, date(in_time) dt from tb_user_log
union 
select uid,date(out_time) dt from tb_user_log) t1
join 
(select uid,min(date(in_time)) new_time from tb_user_log
group by uid ) t2
on t1.uid = t2.uid

3.计算日活和新用户占比

日活（按照日期聚合记数）

count(t1.uid) 

新用户计算（当最早出现的时间等于当天，则取1，记为新用户）

sum(if(new_time=dt,1,0))

最终的代码

select dt, count(t1.uid) dau, round(sum(if(new_time=dt,1,0))/count(t1.uid),2) 
from (select uid, date(in_time) dt from tb_user_log
union 
select uid,date(out_time) dt from tb_user_log) t1
join 
(select uid,min(date(in_time)) new_time from tb_user_log
group by uid ) t2
on t1.uid = t2.uid
group by dt 
order by dt