描述
现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
id | uid | exam_id | start_time | submit_time | score |
1 | 1001 | 9001 | 2020-01-01 09:01:01 | 2020-01-01 09:21:59 | 90 |
2 | 1002 | 9001 | 2020-01-20 10:01:01 | 2020-01-20 10:10:01 | 89 |
3 | 1002 | 9001 | 2020-02-01 12:11:01 | 2020-02-01 12:31:01 | 83 |
4 | 1003 | 9001 | 2020-03-01 19:01:01 | 2020-03-01 19:30:01 | 75 |
5 | 1004 | 9001 | 2020-03-01 12:01:01 | 2020-03-01 12:11:01 | 60 |
6 | 1003 | 9001 | 2020-03-01 12:01:01 | 2020-03-01 12:41:01 | 90 |
7 | 1002 | 9001 | 2020-05-02 19:01:01 | 2020-05-02 19:32:00 | 90 |
8 | 1001 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:59:01 | 69 |
9 | 1004 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:20:01 | 99 |
10 | 1003 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:31:01 | 68 |
11 | 1001 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:43:01 | 81 |
12 | 1001 | 9002 | 2020-03-02 12:11:01 | (NULL) | (NULL) |
请输出每份试卷每月作答数和截止当月的作答总数。
由示例数据结果输出如下:
exam_id | start_month | month_cnt | cum_exam_cnt |
9001 | 202001 | 2 | 2 |
9001 | 202002 | 1 | 3 |
9001 | 202003 | 3 | 6 |
9001 | 202005 | 1 | 7 |
9002 | 202001 | 1 | 1 |
9002 | 202002 | 3 | 4 |
9002 | 202003 | 1 | 5 |
解释:试卷9001在202001、202002、202003、202005共4个月有被作答记录,每个月被作答数分别为2、1、3、1,截止当月累积作答总数为2、3、6、7。
select
*,sum(month_cnt) over(partition by exam_id order by start_month) as cum_exam_cnt
from
(
select
distinct exam_id,
date_format(start_time,'%Y%m') as start_month,
count(1) over(partition by exam_id,left(start_time,7)) as month_cnt
from
exam_record
)t
order by exam_id,start_month