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

select date_format(submit_time,'%Y%m') month,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 year(start_time)=2021 and submit_time is not null group by month;

1、错误解法：

select date_format(submit_time,'%Y%m') month,round(count(submit_time)/count(distinct uid),2) avg_active_days,round(count(distinct uid),2) mau from exam_record where year(start_time)=2021 and submit_time is not null group by month;

正确语句：count(distinct uid, date_format(submit_time, '%y%m%d')对uid和日期的不同计数，相当于“与”，当且仅当使用distinct对uid，date_format(submit_time, '%y%m%d')这两个字段筛选出的结果集进行去重后可以使用，即只要uid与当天日期这俩都一样的情况下，只算做一天的记录

例如：

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES

(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),

(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),

(1002, 9002, '2021-09-02 12:01:01', null, null),

(1002, 9003, '2021-09-01 12:01:01', null, null),

(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),

(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),

(1003, 9002, '2021-07-06 12:01:01', null, null),

(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),

(1004, 9003, '2021-09-06 12:01:01', null, null),

(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),

(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),

(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),

(1006, 9002, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89);

预期输出：

但是实际输出却为：

错误点就在于202109月活跃天数记为5，活跃人数记为3，5/3=1.67，将同一天同一用户做了不同试卷记作了两次，实际上只能算一次，即活跃天数为4，活跃人数为3，4/3=1.33；

错误解法2：count(distinct submit_time)也不行，因为会把不同用户同一天的记录会只记作一次。

2、DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

可以使用的格式有：