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

with merge_user_tb as (
SELECT 
   uid,dt,ROW_NUMBER() over(PARTITION by uid ORDER BY dt) uid_num 
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)t_1)
SELECT        
   dt,
   count(*)                                 as dau,
   round(sum(if(uid_num=1,1,0))/count(*),2) as uv_new_ratio
FROM merge_user_tb
GROUP BY dt
ORDER BY dt;

-- 1.创建联合表，开始离开作为同一时间序列

-- 2.第一天出现为新用户，new_user