题解 | #平均活跃天数和月活人数#

-- 方法1 -- 一次聚合，需要进一步理解用户平均活跃天数这个指标，即所有用户当月总活跃天数/用户人数，同时懂得count函数内多字段的巧妙用法，即通过count(distinct uid,date_format(start_time,'%Y%m%d')来计算所有用户当月总活跃天数。 select date_format(start_time,'%Y%m') as month, round(count(distinct uid,date_format(start_time,'%Y%m%d'))/count(distinct uid),2) as avg_active_days, count(distinct uid) as mau from exam_record where score is not null and year(start_time)='2021' group by month order by month; -- 方法2 -- 两次聚合，思考比方法1少但是繁琐，需要两次聚合，容易出错。 select a.month,round(avg(a.day),2) as avg_active_days,count(distinct a.uid) as mau from( select uid,date_format(start_time,'%Y%m') as month,count(distinct left(start_time,10)) as day from exam_record where score is not null group by date_format(start_time,'%Y-%m'),uid ) a where left(a.month,4)='2021' group by a.month order by a.month;