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