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

借着这一题聊聊开窗函数和group by的区别

（1）grou by 函数的使用——以本题为例

首先我们看看题目的实例结果。哦，它是想让我们输出用户每月答题数分布表。

我们观察到，每个用户对应多个不重复的月份，每个月份我们需要统计其答题情况。这个时候BOSS就想了，我拿到了12行数据，如何分配任务呢？——小李团队统计9001用户的，小王团队统计9002用户的......再把他们的统计结果放在一起就行了。那么小李又有4名员工分别负责统计9001用户2020年1/2/3/5月的答题数；同样的，小王也有很多员工负责统计。这样的就分了两次团队——1.BOSS的CEO团队（小王和小李）；2.小王/小李作为CEO，分别又有自己的员工。所以一定是group by exam_id,start_month。这样就容易理解了吧

这样的工作有什么好处呢？不同的员工分管不同的任务，层层汇报给自己的上级。我们拿到的信息不会重复，原表格“减肥成功”从而增强了数据的可读性。

上代码，看看是不是我说的这样

    select
        exam_id,
        date_format(start_time,'%Y%m') as start_month,
        count(*) as month_cnt 
    from
        exam_record
    group by exam_id,date_format(start_time,'%Y%m')

（2）开窗函数的使用

2.1.1 开窗函数简介

排序开窗函数

rank() over (partition by XX order by XX)——排序结果实例：1，2，2，4

dense_rank() over (partition by XX order by XX)——排序结果实例：1，2，2，3

row_number() over (partition by XX order by XX)——排序结果实例：1，2，3，4

其中，partition by 用于分区，order by 用于对分区后的结果在其区域内排序。

注意！这三个排序开窗函数（）里严禁写任何内容！！！

ntile( n ) over (partition by XX order by XX)——可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号，序号从1到n连续。（如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。）

其他开窗函数

　　count(X) over(partition by … order by …)：求分组后的总数。

　　max(X) over(partition by … order by …)：求分组后的最大值。

　　min(X) over(partition by … order by …)：求分组后的最小值。

　　avg(X) over(partition by … order by …)：求分组后的平均值。

　　lag(X,n,0) over(partition by … order by …)：取值向后偏移n行，若有null变为0（相当于Excel中在第一行前插入n个空行）

　　lead(X,n,0) over(partition by … order by …)：取值向前偏移n行

2.1.2 开窗函数与group by 的区别——以本题为例

这一道题我们如果用开窗函数会得到什么结果呢？

    select
        exam_id,
        date_format(start_time,'%Y%m') as start_month,
        count(exam_id) over (partition by exam_id, date_format(start_time,'%Y%m'))as month_cnt 
    from
        exam_record

震惊！开窗聚合函数后，不跟group by 竟然不报错

发现没有，开窗函数分区但是不改变原表格的行数！显然这张表不是我们想要的，只能用group by 咯。

但是第二步要计算“截止当月的作答总数”，这显然不能改变原来表格的行数呀，这个时候我们就要用开窗函数啦~

有的小伙伴就问了——哎我开窗之后再用一下聚合函数怎么样呢?

话不多说，上代码！

    select
        exam_id,
        date_format(start_time,'%Y%m') as start_month,
        count(exam_id) over (partition by exam_id, date_format(start_time,'%Y%m'))as month_cnt 
    from
        exam_record
    group by exam_id,date_format(start_time,'%Y%m')

怎么会这样呢？原因是SQL执行顺序的问题，先执行了group by，表格已经成功”瘦身“啦~