题解 | #月总刷题数和日均刷题数#

题目

请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况。结果按照月份升序排序。

值得注意的点：

1、这道题可以使用UNION，也可以使用WITH ROLLUP来解决 2、如果要使用with rollup，则还需要注意，需要嵌套一层子查询来做到 —— 原因未知，但是我会提供可行的、和不可行的两种with rollup解决方法

新学习到的日期函数

• LAST_DAY()函数：可用于返还日期当月的最后一天，也可以当成是返还当月的天数

解题思路

方法 1：使用UNION

步骤 1：找出2021年每个月里，用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt

• “202108”格式的月份，使用DATE_FORMAT来解决即可
• 刷题数，则使用COUNT解决（ps：由于示例中都没有score为NULL的情况，所以可以直接使用COUNT）
• 日均刷题数比较绕，需要先为每一行的日期再增加一个字段（一列）来赋上其对应的月份的天数，比如说8月是30天，这里需要先使用LAST_DAY()来返还当月最后一天，再使用DAY()来截取其天数出来
• 随后，由于是需要by月份来计算日均刷题数，会使用到GROUP BY，还需要对DAY函数的结果，再使用一次聚合函数MAX（AVG、MIN其实都可以）以避免“only_full_group_by”错误、保证是除以的是当月的天数，直接上代码会更清晰：

SELECT /* 输出“202108”的月份格式 */
       DATE_FORMAT(submit_time, "%Y%m") AS submit_month,
       /* 计算总刷题数 */
       COUNT(submit_time) AS month_q_cnt,
       /* 日均刷题数 = 总刷题数除以当月天数；当月天数，可以使用MAX函数来获取 */
       ROUND(COUNT(submit_time) / MAX(DAY(LAST_DAY(submit_time))), 3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time) = 2021
GROUP BY DATE_FORMAT(submit_time, "%Y%m")
/* 这一步，我也会直接先ORDER BY一次，后续再嵌套一层查询，进而UNION */
ORDER BY submit_month ASC

步骤 2：找出整个2021年，用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt

• 值得注意的一点是，对于整年来说，日均刷题数，直接以31天为分母即可

SELECT '2021汇总' AS submit_time,
        COUNT(submit_time) AS month_q_cnt,
        ROUND(COUNT(submit_time) / 31, 3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time) = 2021;

步骤 3：使用UNION合并两个结果

• 需要注意的是，步骤1的结果，按照我的做法，需要再嵌套一层查询，使得最终结果的上部分，一定是按照提交月份升序排序的、且2021汇总一定是在最底下的

SELECT t1.*
FROM
    (SELECT DATE_FORMAT(submit_time, "%Y%m") AS submit_month,
            COUNT(submit_time) AS month_q_cnt,
            ROUND(COUNT(submit_time) / MAX(DAY(LAST_DAY(submit_time))), 3) AS avg_day_q_cnt
     FROM practice_record
     WHERE YEAR(submit_time) = 2021
     GROUP BY DATE_FORMAT(submit_time, "%Y%m")
     ORDER BY submit_month ASC) AS t1
UNION
SELECT '2021汇总' AS submit_time,
        COUNT(submit_time) AS month_q_cnt,
        ROUND(COUNT(submit_time) / 31, 3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time) = 2021;

方法 2：使用WITH ROLLUP和GROUPING运算符

步骤 1：先找出2021年的答题记录，并输出对应的月份及其对应的天数

• 第一步不需要计算任何东西，只需要输出2021年的记录即可

SELECT question_id,
        DAY(LAST_DAY(submit_time)) as days_of_month,
        DATE_FORMAT(submit_time, "%Y%m") as y_m
FROM practice_record
WHERE year(submit_time)='2021';

步骤 2：再嵌套一层查询，使用WITH ROLLUP，直接得出“2021汇总”的结果

• 这里先使用WITH ROLLUP，但其实结果出来后，字段“y_m”中的“汇总”行其实是NULL，因此下一步其实还需要套一个GROUPING运算符来解决该问题：

SELECT y_m,
		COUNT(y_m) AS month_q_cnt,
 		ROUND(COUNT(y_m) / MAX(days_of_month), 3) AS avg_day_q_cnt
FROM
	(SELECT question_id,
        DAY(LAST_DAY(submit_time)) as days_of_month,
        DATE_FORMAT(submit_time, "%Y%m") as y_m
	FROM practice_record
	WHERE year(submit_time)='2021') AS t_month_stat
GROUP BY y_m WITH ROLLUP;

• 使用GROUPING运算符，该运算符可以直接使用，并不会影响GROUP BY y_m；当GROUPING(y_m) = 1时，代表该行就是汇总行，此时对应的值应该改为“2021汇总”
• 另外，使用WITH ROLLUP时，好像默认总是会按照聚合字段来升序，这个不确定，但这里我也没有使用ORDER BY，也通过了所有示例

SELECT CASE WHEN GROUPING(y_m) = 1 THEN '2021汇总' ELSE y_m END AS submit_month,
		COUNT(y_m) AS month_q_cnt,
 		ROUND(COUNT(y_m) / MAX(days_of_month), 3) AS avg_day_q_cnt
FROM
	(SELECT question_id,
        DAY(LAST_DAY(submit_time)) as days_of_month,
        DATE_FORMAT(submit_time, "%Y%m") as y_m
	FROM practice_record
	WHERE year(submit_time)='2021') AS t_month_stat
GROUP BY y_m WITH ROLLUP;

值得注意的是，本题目有个疑点，我暂时还未能解决 —— 若想使用GROUPING，就必须必须要通过上述的嵌套查询来解决，不能直接一次性解决

• 以下代码是不可行的：

SELECT CASE WHEN GROUPING(DATE_FORMAT(submit_time, "%Y%m")) = 1 THEN '2021汇总'
            ELSE DATE_FORMAT(submit_time, "%Y%m") END AS submit_month,
       COUNT(submit_time) AS month_q_cnt,
       ROUND(COUNT(submit_time) / MAX(DAY(LAST_DAY(submit_time))), 3) AS avg_day_q_cnt
FROM practice_record
WHERE 
	YEAR(submit_time) = 2021
GROUP BY 
	DATE_FORMAT(submit_time, "%Y%m") WITH ROLLUP