SQL17 平均活跃天数和月活人数
用户在牛客试卷作答区作答记录存储在表exam_record中,内容如下:
exam_record表(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分)
select month
,round(sum(cnt)/count(uid),2)
,count(uid)
from
(select month
,uid
,count(distinct t) cnt
from
(select from_unixtime(unix_timestamp(submit_time),'%Y%m') month
,uid
,substr(submit_time,1,10) t
from exam_record
where submit_time is not null and substr(submit_time,1,4)=2021)m
group by uid,month)q
group by month
order by month;
我的答案中耗时大,主要原因,对事件函数不熟,想要转换日期的格式,直接用date_format就