#rihuo == mei tian , distinct, yonghuzongshu #xinyonghu zhanbi=dangtian xinyonghu /rihuo with tnew as ( select date(tb.in_time) as date_in_time,count(distinct tb.uid) as newnb from tb_user_log tb where not exists( select 1 from tb_user_log tbefore where tb.uid=tbefore.uid and date(tbefore.in_time)<date(tb.in_time) ) group by date(tb.in_time) ), tdate as ( select tb.uid as uid,date(tb.in_time) as in_time from tb_user_log tb union select tb.uid,date(tb.out_time) from tb_user_log tb ) select date(tb.in_time) as dt, count(distinct tb.uid) as dau,coalesce(round(avg(t.newnb)/count(distinct uid),2),0) as uv_new_ratio from tdate tb left join tnew t on date(tb.in_time) = t.date_in_time group by date(tb.in_time) order by date(tb.in_time) asc