#思路: #①准备一张底表tb1,每个月去重以后的活跃用户数,即字段为:年月、uid; #②基于tb1,判断新增用户(最早活跃的月份跟当前月份相等),新增记为1,非新增记为0,形成tb2; #③基于tb2,根据月份聚合,计算每个月的活跃用户数、新增数,形成tb3; #④基于tb3,用窗口函数逐月计算最大新增数、累计新增数。 select mt, a, b, max(b)over(order by mt), sum(b)over(order by mt) from( select mt, count(uid) a, sum(if_new) b from( select uid, mt, if(min(mt)over(partition by uid)=mt,1,0) if_new from( select distinct uid, date_format(start_time,'%Y%m') mt from exam_record) as tb1 ) as tb2 group by mt ) as tb3 order by mt