本文主要是介绍【牛客】SQL143 每份试卷每月作答数和截止当月的作答总数,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
描述
现有试卷作答记录表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
(selectdistinct exam_id,date_format(start_time,'%Y%m') as start_month,count(1) over(partition by exam_id,left(start_time,7)) as month_cntfromexam_record
)t
order by exam_id,start_month
这篇关于【牛客】SQL143 每份试卷每月作答数和截止当月的作答总数的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!