Question

平均活跃天数和月活人数

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

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

思路 ：读完题先注意高亮部分；一般求天数和月活跃人数马上就要想到相关的日期函数；这一题我们同样来进行拆分，把问题细化再解决；首先求活跃人数，肯定要用到 COUNT() ，那这里首先就有一个坑，不知道大家注意了没有？用户 1002 在 9 月份做了两种不同的试卷，所以这里要注意去重，不然在统计的时候，活跃人数是错的；第二个就是要知道日期的格式化，如上表，题目要求以 202107 这种日期格式展现，要用到 DATE_FORMAT 来进行格式化。

基本用法：

DATE_FORMAT(date_value, format)

• date_value 参数是待格式化的日期或时间值。
• format 参数是指定的日期或时间格式（这个和 Java 里面的日期格式一样）。

答案 ：

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

这里多说一句, 使用 COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d')) 可以统计在 uid 列和 submit_time 列按照年份、月份和日期进行格式化后的组合值的数量。

月总刷题数和日均刷题数

描述 ：现有一张题目练习记录表 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 后面出现否则就会报错，或者这个字段出现在聚合函数里面。

思路：

看到实例数据就要马上联想到相关的函数，比如 submit_month 就要用到 DATE_FORMAT 来格式化日期。然后查出每月的刷题数量。

每月的刷题数量

SELECT MONTH ( submit_time ), COUNT( question_id )
FROM
    practice_record
GROUP BY
    MONTH (submit_time)

接着第三列这里要用到 DAY(LAST_DAY(date_value)) 函数来查找给定日期的月份中的天数。

示例代码如下：

SELECT DAY(LAST_DAY('2023-07-08')) AS days_in_month;
-- 输出：31

SELECT DAY(LAST_DAY('2023-02-01')) AS days_in_month;
-- 输出：28 (闰年中的二月份)

SELECT DAY(LAST_DAY(NOW())) AS days_in_current_month;
-- 输出：31 （当前月份的天数）

使用 LAST_DAY() 函数获取给定日期的当月最后一天，然后使用 DAY() 函数提取该日期的天数。这样就能获得指定月份的天数。

需要注意的是， LAST_DAY() 函数返回的是日期值，而 DAY() 函数用于提取日期值中的天数部分。

有了上述的分析之后，即可马上写出答案，这题复杂就复杂在处理日期上，其中的逻辑并不难。

答案 ：

SELECT DATE_FORMAT(submit_time, '%Y%m') submit_month,
       count(question_id) month_q_cnt,
       ROUND(COUNT(question_id) / DAY (LAST_DAY(submit_time)), 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
GROUP BY submit_month
UNION ALL
SELECT '2021 汇总' AS submit_month,
       count(question_id) month_q_cnt,
       ROUND(COUNT(question_id) / 31, 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
ORDER BY submit_month

在实例数据输出中因为最后一行需要得出汇总数据，所以这里要 UNION ALL 加到结果集中；别忘了最后要排序！

未完成试卷数大于 1 的有效用户（较难）

描述 ：现有试卷作答记录表 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

还有一张试卷信息表 examination_info （ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间），示例数据如下：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	SQL	easy	60	2020-02-01 10:00:00
3	9003	算法	medium	80	2020-08-02 10:00:00

请统计 2021 年每个未完成试卷作答数大于 1 的有效用户的数据（有效用户指完成试卷作答数至少为 1 且未完成数小于 5），输出用户 ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下：

uid	incomplete_cnt	complete_cnt	detail
1002	2	4	2021-09-01:算法;2021-07-02:SQL;2021-09-02:SQL;2021-09-05:SQL;2021-07-05:SQL

解释 ：2021 年的作答记录中，除了 1004，其他用户均满足有效用户定义，但只有 1002 未完成试卷数大于 1，因此只输出 1002，detail 中是 1002 作答过的试卷{日期:tag}集合，日期和 tag 间用 : 连接，多元素间用 ; 连接。

思路：

仔细读题后，分析出：首先要联表，因为后面要输出 tag ；

筛选出 2021 年的数据

SELECT *
FROM exam_record er
LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR (er.start_time)= 2021

根据 uid 进行分组，然后对每个用户进行条件进行判断，题目中要求 完成试卷数至少为 1,未完成试卷数要大于 1，小于 5

那么等会儿写 sql 的时候条件应该是： 未完成 > 1 and 已完成 >=1 and 未完成 < 5

因为最后要用到字符串的拼接，而且还要组合拼接，这个可以用 GROUP_CONCAT 函数，下面简单介绍一下该函数的用法：

基本格式：

GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, ...]]             [SEPARATOR sep])

• expr ：要连接的列或表达式。
• DISTINCT ：可选参数，用于去重。当指定了 DISTINCT ，相同的值只会出现一次。
• ORDER BY ：可选参数，用于排序连接后的值。可以选择升序 ( ASC ) 或降序 ( DESC ) 排序。
• SEPARATOR sep ：可选参数，用于设置连接后的值的分隔符。（本题要用这个参数设置 ; 号 ）

GROUP_CONCAT() 函数常用于 GROUP BY 子句中，将一组行的值连接为一个字符串，并在结果集中以聚合的形式返回。

答案 ：

SELECT a.uid,
       SUM(CASE
               WHEN a.submit_time IS NULL THEN 1
           END) AS incomplete_cnt,
       SUM(CASE
               WHEN a.submit_time IS NOT NULL THEN 1
           END) AS complete_cnt,
       GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag)
                    ORDER BY start_time SEPARATOR ";") AS detail
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE YEAR (a.start_time)= 2021
GROUP BY a.uid
HAVING incomplete_cnt > 1
AND complete_cnt >= 1
AND incomplete_cnt < 5
ORDER BY incomplete_cnt DESC

• SUM(CASE WHEN a.submit_time IS NULL THEN 1 END) 统计了每个用户未完成的记录数量。
• SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 END) 统计了每个用户已完成的记录数量。
• GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag) ORDER BY a.start_time SEPARATOR ';') 将每个用户的考试日期和标签以逗号分隔的形式连接成一个字符串，并按考试开始时间进行排序。