题外笔记

在解题过程中也发现一个细节问题，不难，但是对我个人很有帮助，大家也可以在日后额外留意一下：

若在SELECT子句中为特定字段创建了别名（alias），同时还需要对该字段再SELECT并且进行操作，请不要在第二次（或任意一次）SELECT中使用该字段的别名——这么说很拗口晦涩，直接上示例：

# 只是一个sample
SELECT exam_id,
       DATE_FORMAT(start_time, '%Y%m') AS start_month,
       
       COUNT(start_time) AS month_cnt,   # 以此行为示例
       month_cnt + 1                      # 直接使用别名"month_cnt"来进行创建新的计算字段
       
FROM exam_record
GROUP BY exam_id, DATE_FORMAT(start_time, '%Y%m')

上述操作会得到报错：SQL_ERROR_INFO: "Unknown column 'month_cnt' in 'field list'"——第六行找不到字段“month_cnt”，要使其可行，只能够完完整整再写一次month_cnt原本的代码，即

SELECT exam_id,
       DATE_FORMAT(start_time, '%Y%m') AS start_month,
       
       COUNT(start_time) AS month_cnt,
       COUNT(start_time) + 1
       
FROM exam_record
GROUP BY exam_id, DATE_FORMAT(start_time, '%Y%m')

正式解题思路

本题主要考核使用开窗函数来计算累计数值

我一开始的思路也比较常规：

1）首先是查询出每一个科目在每一个月份的作答次数（注意：只要字段start_time存在，无论最终有没有提交——即，submit_time是否为NULL——都无关紧要；因此直接对start_time进行COUNT()）

SELECT exam_id,
       DATE_FORMAT(start_time, '%Y%m') AS start_month,
       COUNT(start_time) AS month_cnt
FROM exam_record
GROUP BY exam_id, DATE_FORMAT(start_time, '%Y%m') #这里没有使用别名start_month而是完整代码

2）基于上述结果表，再进行一次聚合窗口函数，记住，在这里是分考试（或考试id）且by月份去计算累计值，因此必须是PARTITION BY 和 ORDER BY 一同使用：

SELECT t.exam_id, t.start_month, t.month_cnt,
       SUM(t.month_cnt) OVER (PARTITION BY t.exam_id ORDER BY t.start_month ASC) AS cum_exam_cnt
FROM
(
    SELECT exam_id,
           DATE_FORMAT(start_time, '%Y%m') AS start_month,
           COUNT(start_time) AS month_cnt
    FROM exam_record
    GROUP BY exam_id, DATE_FORMAT(start_time, '%Y%m')
) AS t;

但是，又发现了更加便捷的解题方法，有以下思路：

1. 首先是优化我的原答案——减少嵌套层数/子查询层数

方法很简单，但是我认为也很精妙，很需要熟练度，参考@盐咸咸的答案，能够发现，“SUM(t.month_cnt)”其实就是对子查询中的“COUNT(start_time) AS month_cnt”进行加和SUM。

因此，可以优化我的最终答案——直接使用SUM(COUNT(start_time))，在第一层子查询中就直接创建一个新的计算字段cum_exam_cnt，直接上代码：

SELECT exam_id,
       DATE_FORMAT(start_time, '%Y%m') AS start_month,
       COUNT(start_time) AS month_cnt,
       
       # 直接对原来的month_cnt进行嵌套，可以简单理解为，对month_cnt这个字段进行操作
       SUM(COUNT(start_time)) OVER (PARTITION BY exam_id ORDER BY DATE_FORMAT(start_time, '%Y%m') ASC) AS cum_exam_cnt
       
FROM exam_record
GROUP BY exam_id, DATE_FORMAT(start_time, '%Y%m')

该解题思路和另外一道题目“每类试卷得分前3名”（链接）有异曲同工之妙。

2. 第二个解题思路更加清晰简洁，但是我认为很需要熟练度才能过一气呵成

待更新...

先上代码，随后再进行解析

SELECT DISTINCT 
    exam_id,
    DATE_FORMAT(start_time,'%Y%m') AS start_month,
    COUNT(start_time) OVER(PARTITION BY exam_id,DATE_FORMAT(start_time,'%Y%m')) AS month_cnt,#每份试卷每月作答数
    COUNT(start_time) OVER(PARTITION BY exam_id ORDER BY DATE_FORMAT(start_time,'%Y%m')) AS cum_exam_cnt#截止当月的作答总数
FROM exam_record
ORDER BY exam_id,start_month

不得不说，真的牛