SQL之date_format(),round()

最新推荐文章于 2024-06-22 22:10:29 发布

安利“一个处女座的程序员”

最新推荐文章于 2024-06-22 22:10:29 发布

阅读量210

点赞数

分类专栏： SQL 文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/weixin_48272780/article/details/129152050

版权

SQL 专栏收录该内容

29 篇文章 4 订阅

订阅专栏

用户在牛客试卷作答区作答记录存储在表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

select date_format(submit_time,'%Y%m') as month
,round((count(distinct uid,date_format(submit_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 date_format(submit_time,'%Y%m')

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