mysql 分组排序_数据分析Mysql笔记

这一关的主要内容是SQL的几个高级功能

一、窗口函数

使用窗口函数的基本方法如下：

select 字段，

<窗口函数> over (partition by 分组列名

order by 排序列名）as <给这波操作出来的列一个新列名>

其中，窗口函数可以为

1.专用窗口函数：rank、dense_rank、row_number

使用方法一样，功能区别是，当排序中出现并列名次时，rank函数会按照并列排名，并占用并列排名名次之后的位置，比如，有三个第五名，则rank排序会这样排：

5、5、5、8，这样前面出现的并列名次并不会影响后面人的真实名次；

dense_rank会在顺延并列名词之后的名词，同样的实例中，它会这样排序：

5、5、5、6，所有排序元素的名词都是顺序连贯的；

row_number排序中没有并列名词，按顺序连贯不重复的排序：

5、6、7、8；

练习题：

select 

Top N 问题练习题，原表如下：

关于Top N问题的几个例子

• 分组取每组最大值

select *
from score as a
where 成绩=(select max(成绩)
from score as b
where a.课程号=b.课程号) 

• 分组取每组最小值

和取最大值一样，只是把函数MAX换成MIN

• 按姓名分组，取每组最大的N条记录

select *
from (select *,row_number() over (partition by 姓名 order by 成绩 desc) as ranking
from `科目表`)
where ranking<=2

由于这里我用的Mysql版本为8.0.11，还不支持row_number 这样的窗口函数，最新版本的Mysql已经可以支持了，所以没有运行出来。以后装上最新版本的Mysql后还是需要学下版本更新内容

2.聚合函数作为窗口函数：sum、count、average、max、min

select *,count(成绩) over (order by 姓名) as count_current,sum(成绩) over(order by 姓名) as sum_current,
avg(成绩) over (order by 姓名) as avg_current,max(成绩) over (order by 姓名) as current_max,
min(成绩) over (order by 姓名) as current_min
from 科目表

观察发现，使用聚合函数作为窗口函数套用的功能是，使聚合函数的原油功能增加了当前累计功能，即不再是只对所有行运算，而是每一行的前面所有行。

练习题：现有“成绩表”，记录了每个学生各科的成绩。表内容如下：

问题：查找单科成绩高于该科目平均成绩的学生名单

由于是在单科成绩内作比较，所以要用分组，但是如果使用group by分组，则每组只返回一行数据，不符合题目要求，所以应该用partition 窗口函数分组，同时，考虑到sql运行顺序是先from where 再是select,故需要加一个关联子查询，代码如下：

select *
from(select *,avg(成绩) over(partition by 科目 order by 成绩) as avg_current
from `科目表`)
where 成绩>avg_current

聚合窗口函数中，可以使用row 行数 preceding 做当前行向前指定特定行数的移动聚合，

比如， avg （）over (order by __ row 3 preceding )的意思是对每一行和它的前3行进行取平均值计算，这相当于实现了移动平均法，在某些业务场景中，非常有用。

窗口函数有以下功能：

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名

窗口函数使用场景：

1. Top N问题
2. 组内排名
3. 组内比较