首页 > 试题广场 >

平均活跃天数和月活人数

[编程题]平均活跃天数和月活人数
  • 热度指数：183532 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
用户在牛客试卷作答区作答记录存储在表exam_record中，内容如下：
exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau，上面数据的示例输出如下：
解释：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);

输出

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 回复(20)
Mysql
Mozart-k626头像 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号头像 牛客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号头像 牛客634241998号
本题描述就很离谱…………语文老师谁交de
发表于 2022-01-22 17:26:58 回复(6)
Mysql
小罗飞飞飞头像 小罗飞飞飞 
有人一天内可能答题好几次，故计算活跃天数需要去重:
count(distinct uid,date_format(submit_time,'%Y%m%d'))
当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个
字段，而且distinct只能放到所有字段的前面。

发表于 2021-12-23 17:01:54 回复(2)
键盘盘盘盘头像 键盘盘盘盘 
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)
牛客803877269号头像 牛客803877269号
找了半天 终于看到 
有人一天内可能答题好几次，故计算活跃天数需要去重:
count(distinct uid,date_format(submit_time,'%Y%m%d'))
当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个
字段，而且distinct只能放到所有字段的前面。
发表于 2022-03-15 16:05:46 回复(0)
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
a数据求职人头像 a数据求职人
我不会，我太垃圾了
发表于 2024-04-09 19:48:19 回复(0)
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头像 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号头像 牛客609419489号
活跃天数 不是单纯天数，而是用户id+天数的组合概念


发表于 2022-04-03 18:07:04 回复(0)
康纳酱本人头像 康纳酱本人
1、学会使用DATE_FORMAT函数
2、一人一天有多条试卷做题记录，活跃天数也只算一天，故要记得去重
发表于 2022-01-29 12:15:34 回复(0)
Mysql
奶酪数学家头像 奶酪数学家
活跃天数：一个人同一天活跃算一天 ,活跃指交卷，用submit_time算所以是count(distinct substr(submit_time),1,10),uid)
发表于 2022-01-06 13:35:54 回复(0)
Mysql
牛客855588425号头像 牛客855588425号

涨见识了，原来count()可以接两个字段
count(distinct uid,day(submit_time))
一个人可以多天练习多道题目，一天会有多个人练题。
所以需要去重，即使你一天练多个题目也只是算活跃了一天。
count()接两个字段就是每个用户练习了多少天题。
其实可以加个括号理解
count(distinct (uid,day(submit_day))
意思就是只有两个字段相同才会去重计算

发表于 2022-11-06 18:57:09 回复(0)
Mysql
西冷牛排头像 西冷牛排
原来这里活跃天数是指每天有多少个uid活跃了，比如2021-09-01这天uid1002 和uid1005都活跃了，则算做两天，所以计数时要以distinct uid， date(start_time) 两个参数为标准
select date_format(start_time,'%Y%m'), 
round(count(distinct uid,date(start_time))/ count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where year(start_time) = '2021' and score is not null
group by date_format(start_time,'%Y%m')


发表于 2022-06-30 23:17:57 回复(5)
Mysql
躺平又起来了的番石榴很爱看电影头像 躺平又起来了的番石榴很爱看电影
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)
提交观点

问题信息

难度：
327条回答 3974浏览

热门推荐

通过挑战的用户

查看代码

相关试题

平均活跃天数和月活人数

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号