题解 143 | #每份试卷每月作答数和截止当月的作答总数#

【场景】：截止到本行数据，统计数据结果、出每一行数据，对整体统计数据的影响

【分类】：聚合窗口函数

分析思路

（1）统计截止当月的作答总数

• [使用]：count(*) over(partition by exam_id order by date_format(start_time,'%Y%m'))

（2）输出每份试卷每月作答数和截止当月的作答总数

• [使用]：group by exam_id,start_month

或者直接使用

sum(count(*)) over(partition by exam_id order by date_format(start_time,'%Y%m'))

最终结果

select 查询结果 [试卷ID;月份;每月作答数;截止当月的作答总数]
from 从哪张表中查询数据[exam_record]
group by 分组条件 [用户ID;月份]
order by 对查询结果排序 [试卷ID、月份升序];

扩展

前往查看： MySQL 窗口函数

求解代码

方法一

with子句

with
    main as(
        #统计截止当月的作答总数
        select
            exam_id,
            date_format(start_time,'%Y%m') as start_month,
            count(*) over(partition by exam_id order by date_format(start_time,'%Y%m')) as cum_exam_cnt
        from exam_record
    )
    
#输出每份试卷每月作答数和截止当月的作答总数
select distinct
    exam_id,
    start_month,
    count(start_month) as month_cnt,
    cum_exam_cnt
from main
group by exam_id,start_month

方法二

sum(count(*)) 专用窗口函数

#输出每份试卷每月作答数和截止当月的作答总数
select 
    exam_id,
    date_format(start_time,'%Y%m') as start_month,
    count(*) as month_cnt,
    sum(count(*)) over(partition by exam_id order by date_format(start_time,'%Y%m')) as cum_exam_cnt
from exam_record 
group by exam_id,start_month