#构造了三个临时表 with cte as ( select *,min(login_date)over(partition by uid) as min_date from user_login_tb ), cte1 as( select min_date as dt,count(distinct uid) as new_num from cte group by dt ), cte2 as ( select login_date as dt,count(distinct uid) as total_user_num from user_login_tb group by dt ) select dt,total_user_num, concat(round(ifnull(new_num/total_user_num,0)*100,1),'%') as new_user_rate from cte2 left join cte1 using(dt) order by dt