题解 | #平均活跃天数和月活人数#
平均活跃天数和月活人数
http://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9
明确题意：
计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau
问题拆解：
- 本题主要是考察知识点：group by、date_format、substring，重点是对本题逻辑的理解！
- 如果用户1在7-2号提交了2次，算作活跃1天（本题貌似没有描述清楚......）！！所以需要先按uid,year_month_day分组去重，得到表t1
- DATE_FORMAT(submit_time, "%Y%m%d" ) 返回的是20210907这样；substring('abcdefghi',1,6) 返回的是1到6的子串abcdef；
- mau=当月不重复的uid总数
- avg_active_days = t1的记录总数 除以 mau
代码实现：
select substring(year_month_day,1,6) as month, round(count(*) / count(distinct uid),2) as avg_active_days, count(distinct uid) as mau from ( select uid, DATE_FORMAT(submit_time, "%Y%m%d" ) as year_month_day from exam_record where score is not null and year(submit_time) = '2021' group by uid, DATE_FORMAT(submit_time, "%Y%m%d" ) )t1 group by substring(year_month_day,1,6) ;
mysql> select -> uid, -> DATE_FORMAT( `submit_time`, "%Y%m%d" ) as year_month_day -> from -> exam_record where score is not null -> group by uid, DATE_FORMAT( `submit_time`, "%Y%m%d" ) order by year_month_day ; +------+----------------+ | uid | year_month_day | +------+----------------+ | 1002 | 20210702 | | 1002 | 20210705 | | 1002 | 20210901 | | 1005 | 20210901 | | 1006 | 20210901 | | 1005 | 20210902 | | 1006 | 20210902 | | 1006 | 20210903 | | 1002 | 20210905 | | 1002 | 20210907 | +------+----------------+ 10 rows in set (0.00 sec)
结果：
不足之处，欢迎指正
查看10道真题和解析