首页 > 试题广场 >

平均活跃天数和月活人数

[编程题]平均活跃天数和月活人数

热度指数：160663 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

用户在牛客试卷作答区作答记录存储在表exam_record中，内容如下：

exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2021-07-02 09:01:01	2021-07-02 09:21:01	80
2	1002	9001	2021-09-05 19:01:01	2021-09-05 19:40:01	81
3	1002	9002	2021-09-02 12:01:01	(NULL)	(NULL)
4	1002	9003	2021-09-01 12:01:01	(NULL)	(NULL)
5	1002	9001	2021-07-02 19:01:01	2021-07-02 19:30:01	82
6	1002	9002	2021-07-05 18:01:01	2021-07-05 18:59:02	90
7	1003	9002	2021-07-06 12:01:01	(NULL)	(NULL)
8	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
9	1004	9003	2021-09-06 12:01:01	(NULL)	(NULL)
10	1002	9003	2021-09-01 12:01:01	2021-09-01 12:31:01	81
11	1005	9001	2021-09-01 12:01:01	2021-09-01 12:31:01	88
12	1006	9002	2021-09-02 12:11:01	2021-09-02 12:31:01	89
13	1007	9002	2020-09-02 12:11:01	2020-09-02 12:31:01	89

请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau，上面数据的示例输出如下：

month	avg_active_days	mau
202107	1.50	2
202109	1.25	4

解释：2021年7月有2人活跃，共活跃了3天（1001活跃1天，1002活跃2天），平均活跃天数1.5；2021年9月有4人活跃，共活跃了5天，平均活跃天数1.25，结果保留2位小数。

注：此处活跃指有交卷行为。

示例1

输入

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

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),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89),
(1007, 9002, '2020-09-02 12:11:01', '2020-09-02 12:31:01', 89);

输出

202107|1.50|2
202109|1.25|4

算法知识视频讲解

要成为一个厉害的人

主要难的一点是天数的计算。到底是count(distinct uid,date_format(start_time,"%Y%m%d"))
还是count(start_time)作为分子呢？？？
关键是理解题目的意思是：天数。
假设一个uid 比如1001在2021-07-06这一天有二个记录，如果是count(start_time)那么就是天数是2
但是如果是count(distinct uid,date_format(start_time,"%Y%m%d"))天数就是1了，因为只有2021-07-06只有一天存在！
所以关键是明白一天二个不同时间段是一天，还是二天。
完整代码如下：
select DATE_FORMAT(start_time,"%Y%m") as month,
round(count(distinct uid,date_format(start_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 month;

发表于 2021-10-26 14:08:07 回复(19)

Mysql

Mozart-k626

写了一些我遇到的问题跟理解，欢迎指正

疑问①：为什么要对两个字段进行去重（为啥要加uid）

count(distinct submit_time)
vs
count(distinct uid,date_format(submit_time,'%Y%m%d')
思路：如果不加uid，那用户1、用户2，两人在同一天做了卷子，这样submi_time只会被记录一次（因为用户1、2的日期一样），加了uid，是为了把不同的用户跟他的做题日期对应起来

疑问②：count后面为什么可以接2个字段
语法：count后面接2个字段，只要2个字段的结果一样，那就判断为重复记录，最后只输出一个结果，所以这里语法是可行的
误区：uid只有一个人，那要是这个人有多个记录，count里面结果不就不一样了？
思路：因此这里count后面的date_format，在格式部分具体到了天（'%d'），这样一个用户一天如果提交了2次试卷，在distinct 日期的时候，只会被算作1天，也就是说，每个用户，每天只会被记录一次（如果累计活跃了3天，那这个用户总共会有3条活跃记录），所以在count时候，Uid跟submit_time，每一天都是[1,1]，因此在语法上没问题

发表于 2022-05-09 17:41:34 回复(7)

Mysql

热爱编程昂

😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊

😊我看评论很多人在纠结 😊

😊count(distinct uid, DATE_FORMAT(start_time,'%Y%d') 😊

😊count里能不能放两个参数,最终测试结果如下: 😊

😊ORACLE: count(distinct A,B)和count(A,B) 都报错 😊

😊MYSQL: count(distinct A,B)是可以的 😊

😊 count(A,B) 报错 😊

😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊

发表于 2022-06-07 16:18:43 回复(3)

Mysql

牛客547999472号

#正确的代码

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,
COUNT(distinct uid) mau
from exam_record
where year(submit_time)=2021
GROUP by month(submit_time)

#我的代码

select DATE_FORMAT(submit_time,'%Y%m') month,
round(
count(uid)/count(distinct uid),2) avg_active_days,
COUNT(distinct uid) mau
from exam_record
where year(submit_time)=2021
GROUP by month(submit_time)

区别在 count(distinct uid, DATE_FORMAT(submit_time, '%Y%m%d'))与count(uid)

本题陷阱在于九月份有个用户同一天做了两种卷子，直接count统计的话活跃天数会多一天，即用户ID和做题日期submit_time要同时去重才能得出正确的活跃天数.

发表于 2021-11-20 22:52:54 回复(18)

Mysql

冷凡社长

看了一下讨论里面的答案，感觉有点走偏，并不是代码越少越好

建议使用我的这种方式

select date_format(day1,'%Y%m') as month,
round(count(*)/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau

from (select uid,date(submit_time) as day1 from exam_record 
      where submit_time is not null and year(submit_time) =2021 group by uid,date(submit_time) )t1
      
group by date_format(day1,'%Y%m')

发表于 2022-03-14 20:58:43 回复(8)

Mysql

牛客634241998号

本题描述就很离谱…………语文老师谁交de

发表于 2022-01-22 17:26:58 回复(4)

Mysql

小罗飞飞飞

有人一天内可能答题好几次，故计算活跃天数需要去重:
count(distinct uid,date_format(submit_time,'%Y%m%d'))
当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个
字段，而且distinct只能放到所有字段的前面。

发表于 2021-12-23 17:01:54 回复(2)

牛客803877269号

找了半天终于看到

有人一天内可能答题好几次，故计算活跃天数需要去重:

count(distinct uid,date_format(submit_time,'%Y%m%d'))

当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个

字段，而且distinct只能放到所有字段的前面。

发表于 2022-03-15 16:05:46 回复(0)

键盘盘盘盘

select
    date_format(start_time,"%Y%m") as month,
    round(count(distinct uid,date_format(start_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
    month

继续学习！

发表于 2021-12-16 17:37:32 回复(3)

Mysql

年轻人不要太气盛啊

select DATE_FORMAT(submit_time,'%Y%m') as month,round(COUNT(distinct uid,day(submit_time))/count(distinct uid),2) as avg_active_days,count(distinct uid)
FROM exam_record
WHERE submit_time is not null and YEAR(submit_time)=2021

GROUP BY MONTH(submit_time)\

这题重点需要理解平均活跃天数这个概念，加入有人一天之类做了2道题，活跃天数为1天，不是2天，所以不能用COUNT(day(submit_time)）算，得用COUNT(distinct uid,day(submit_time))算。

发表于 2021-10-27 16:09:29 回复(5)

Mysql

香煎三文鱼

select concat(substring_index(submit_time,'-',1),SUBSTRING_INDEX(substring_index(submit_time,'-',-2),'-',1)) as month,
round(count(distinct uid,day(submit_time))/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 month

要注意一个人一天可能做两套题，有两条记录，但其实只能算活跃一天，所以第二个筛选必须要distinct两个字段

发表于 2022-03-02 21:43:20 回复(2)

Mysql

李玉婷的旁听生

解决2个问题：

1、distinct多个字段去重，且按照提交天数&用户去重，精确到天

2、活跃天数：有人一天内做了2道题，活跃天数为1天，不是2天；

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

发表于 2021-12-30 16:21:49 回复(10)

Mysql

zirlin

select DATE_FORMAT(submit_time,'%Y%m') as month,
round(count(distinct concat(uid,day(submit_time)))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where year(submit_time) = '2021'
group by month;

发表于 2022-07-19 10:56:26 回复(0)

Mysql

牛客609419489号

活跃天数不是单纯天数，而是用户id+天数的组合概念

发表于 2022-04-03 18:07:04 回复(0)

Mysql

奶酪数学家

活跃天数：一个人同一天活跃算一天 ,活跃指交卷，用submit_time算所以是count(distinct substr(submit_time),1,10),uid)

发表于 2022-01-06 13:35:54 回复(0)

Mysql

a数据求职人

我不会，我太垃圾了

发表于 2024-04-09 19:48:19 回复(0)

Mysql

牛客907931943号

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

发表于 2022-05-31 16:31:17 回复(0)

Mysql

牛客959320403号

求高手帮忙看看哪里出错了

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

发表于 2022-05-28 16:00:09 回复(2)

Mysql

_Rhythm_

1.有人同一天做两次算做一次，
2.count(distinct uid,date_format(submit_time)),同时对两个字段去重然后计数.
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,
count(distinct uid) mau
from
exam_record
where
year(submit_time) = 2021
group by
month

编辑于 2022-06-07 22:18:06 回复(2)