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

select t7.dt,t7.dau,ifnull(round(t5.new_user_cnt/t7.dau,2),0) as uv_new_ratio 
from (
# 查询每天新用户的数量new_user_cnt  
select t4.dt,count(distinct t4.uid) as new_user_cnt from (
select t3.uid,row_number() over(partition by t3.uid order by t3.dt) as rank_date
,t3.dt  
    from (
# 由于存在跨天登录的情况，所以将in_time和out_time用union拼接起来，生成登录记录的全表
select distinct t1.uid,date_format(t1.in_time,'%Y-%m-%d') as dt 
from tb_user_log as t1 
union 
select distinct t2.uid,DATE_FORMAT(t2.out_time,'%Y-%m-%d') as dt 
from tb_user_log as t2 ) t3 ) t4 
where t4.rank_date = 1 group by t4.dt ) t5 
right join 
(
# 查询每天用户数dau
select distinct t6.dt, count(t6.uid) over(partition by t6.dt) as dau 
from  (
# 由于存在跨天登录的情况，所以将in_time和out_time用union拼接起来，生成登录记录的全表
select distinct t1.uid,date_format(t1.in_time,'%Y-%m-%d') as dt 
from tb_user_log as t1 
union 
select distinct t2.uid,DATE_FORMAT(t2.out_time,'%Y-%m-%d') as dt 
from tb_user_log as t2 ) t6 ) t7 on t5.dt=t7.dt 
order by t7.dt