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

这么写大家觉得可以吗，会不会显得太烦琐了

#统计每天的日活数,涉及跨天所以用Union把in_time与out_time连起来
with table1 as
(select
    uid, date_format(in_time, '%Y-%m-%d') as date
from tb_user_log
union
select
    uid, date_format(out_time, '%Y-%m-%d') as date
from tb_user_log),
table2 as 
(select  
    count(distinct uid) as active_num,
    date
from table1
group by date
),
#每天的新用户个数
##计算每个用户的第一次访问日期，以及每个日期的用户注册数
table3 as
(select 
    first_active as date,
    count(uid) as new_num
from
(select
    uid, min(date) as first_active
from table1
group by
    uid) as c
group by first_active
)##表连接
select 
    a.date as dt,
    a.active_num as dau,
    round(if(b.new_num is null, 0,b.new_num) / a.active_num, 2) as uv_new_ration
from table2 a
left join
    table3 as b
on  a.date = b.date
order by a.date asc;