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

-- 按照月分组,group by month(submit_time)
-- 平均月活跃天数(avg_active_days)=活跃总次数数/uid，保留两位小数，round(字段,2)
-- 活跃总次数=交卷次数，submit_time is not null
-- 月度活跃人数mau=count( distinct uid),按照月份分组
select date_format(submit_time,'%Y%m') as month,
round((count(distinct uid,date_format(submit_time,'%y%m%d')))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where submit_time is not null and year(submit_time)=2021
group by date_format(submit_time, '%Y%m');

下面这个只对了两个测试用例，因为count(date_format(submit_time,'%y%m%d')))/count(distinct uid)这里前一个count没有对uid去重，正确的是count(distinct uid,date_format(submit_time,'%y%m%d')))/count(distinct uid)

-- 按照月分组,group by month(submit_time)
-- 平均月活跃天数(avg_active_days)=活跃总次数数/uid，保留两位小数，round(字段,2)
-- 活跃总次数=交卷次数，submit_time is not null
-- 月度活跃人数mau=count( distinct uid),按照月份分组
select date_format(submit_time,'%Y%m') as month,
round((count(date_format(submit_time,'%y%m%d')))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where submit_time is not null and year(submit_time)=2021
group by date_format(submit_time, '%Y%m');

下面一个的结果

DATE_FORMAT() 用法详解