题解 | distinct关键字后面加两个字段,可以去重组合
平均活跃天数和月活人数
https://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9
# distinct关键字后面也可以加if()函数,再结合count()使用则可以实现指定条件下的计数操作 # 注意活跃天数是各个用户的不同上线日子总数之和 select date_format(start_time, '%Y%m') month, round(count(distinct uid, if(submit_time is not null, date_format(submit_time, '%Y%m%d'), null))/count(distinct if(submit_time is not null, uid, null)), 2) avg_active_days, count(distinct if(submit_time is not null, uid, null)) mau from exam_record where date_format(start_time, '%Y') = '2021' group by date_format(start_time, '%Y%m');