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

最开始我想的是用date_format函数将submit_time中的year month取出来， 将结果集左连接到主表上 然后发现其实没有必要整一个结果集外连接

SELECT
    month,
    round(count(distinct uid))/ count(distinct e_r.uid),2)  avg_active_days,
    round(count(distinct e_r.uid),2) mau
FROM
    exam_record e_r
        LEFT JOIN
    (
        SELECT uid,
               exam_id,ubmit
               id,
               date_format(submit_time, '%Y%m') month
        FROM exam_record
) mon
ON
    mon.id = e_r.id
where
    month is not null
    and year(submit_time) =2021
group by
    month;

直接将结果表示出来就行了也就是用一次就写一次date_format(submit_time,"%Y%m")

然后发现我的计算月活人数的写法是有bug的

也就是

如果一个人在同一个时间提交了两次我这种写法会把他当一个人来看待，也就是说avg算出来会有错误 所以应该是

round(COUNT(distinct uid,date_format(submit_time,'%Y%m%d'))/ count(distinct uid),2) 来计算月活人数

最终代码就是

SELECT
    date_format(submit_time,'%Y%m') mouth,
    round(COUNT(distinct uid,date_format(submit_time,'%Y%m%d'))/ count(distinct uid),2)  avg_active_days,
    round(count(distinct uid),2) mau
FROM
    exam_record
where
    submit_time is not null
    and year(submit_time) =2021
group by
    date_format(submit_time,'%Y%m');

还可以用like函数来匹配year

submit_time like '2021%'

也能有同样效果，时间以及占用可能更少