牛客上的SQL复健记录1

力扣上免费的刷完了，去刷牛客的。

题1.

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

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

记录.

select
    concat(year(start_time),right(100+month(start_time),2)) 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 year(start_time)="2021" and submit_time is not null
group by month

主要记录文本拼接函数CONCAT(str1,str2,...)和读取月份并将未满10的月份前添加数字0的方法RIGHT(100+month(date),2)

另外也可使用dateformat函数：DATE_FORMAT(submit_time,'%Y%m')。

题2.

描述

现有一张题目练习记录表practice_record，示例内容如下：

id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1003	8002	2021-08-01 19:38:01	80

请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：

submit_month	month_q_cnt	avg_day_q_cnt
202108	2	0.065
202109	3	0.100
2021汇总	5	0.161

解释：2021年8月共有2次刷题记录，日均刷题数为2/31=0.065（保留3位小数）；2021年9月共有3次刷题记录，日均刷题数为3/30=0.100；2021年共有5次刷题记录（年度汇总平均无实际意义，这里我们按照31天来算5/31=0.161）

牛客已经采用最新的Mysql版本，如果您运行结果出现错误：ONLY_FULL_GROUP_BY，意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

记录： 

select 
    date_format(submit_time,"%Y%m") as submit_month,
    count(id) as month_q_cnt,
    round(count(id)/day(avg(last_day(submit_time))),3) as avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by submit_month

union all

select
    "2021汇总" ,
    count(id) ,
    round(count(id)/31,3)
from practice_record
where year(submit_time)=2021

order by submit_month

 不得不说这个所谓新版是真的ex人，这题记录3点：

1.使用last_da(date)提取日期对应月份的最后一个日期后，使用day函数可以返回当月天数；

2.对于上题对应的新版本的SQL，若使用group by， 则要求select中列均出现在group by中，除非那一列套了一个聚合函数。这里除了本应就需分组的submit_month，select中还出现了列id和列submit_time,因此需要对这两组套用聚合函数，而id因为题目需要已经套用聚合函数count,因此对返回单值的列submit_time套用聚合函数avg、max、min均可；

3.对于union all 需要在最后再进行排序 否则会报错。