不好意思,我真的不知道MySQL的窗口函数...

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 不好意思,我真的不知道MySQL的窗口函数...

  点击上方 **好好学java **,选择 **星标 **公众号


重磅资讯、干货,第一时间送达
今日推荐:推荐 8 个常用 Spring Boot 项目个人原创+1博客:点击前往,查看更多

来源:知乎


转自:大数据分析挖掘和Python机器学习
Mysql从8.0版本开始,也和Sql Server、Oracle一样支持在查询中使用窗口函数。


本文将根据官方文档,通过实例介绍窗口函数并举例分组排序函数的使用。



本文用到的学生分数表格和语句如下:




DROP TABLE IF EXISTS Marks;CREATE TABLE Marks(学生 VARCHAR(10),  
 科目 CHAR(2), 
 分数 INT);
 insert into Marks values
 ('赵四','语文',88),('赵四','数学',48),('赵四','英语',75),
 ('张三','语文',30),('张三','数学',75),('张三','英语',75),
 ('王五','语文',90),('王五','数学',94),('王五','英语',70),
 ('李四','语文',82),('李四','数学',69),('李四','英语',90);
 
 SELECT * FROM Marks;

首先创建了如下班级分数表格为例:

窗口函数可以大体分为两大类,第一类是能够作为窗口函数的聚合函数:SUM、AVG、COUNT、MAX、MIN,第二类是以RANK、DENSE_RANK、ROW_NUMBER为代表的专用窗口函数。为了便于理解窗口函数,首先以聚合函数sum()为例,下面分别使用窗口函数和聚合函数展示每个学生的成绩总分:

附上我历时三个月总结的 Java 面试 + Java 后端技术学习指南,这是本人这几年及春招的总结,目前,已经拿到了大厂offer,拿去不谢!

下载方式

1. 首先扫描下方二维码

2. 后台回复「Java面试」即可获取


-- 作为窗口函数
SELECT 学生,科目,分数,
       SUM(分数) OVER (PARTITION BY 学生) AS '总分' 
        FROM Marks;



-- 与直接使用sum()聚合函数得到的结果一样
SELECT 学生,SUM(分数) AS '总分' 
 FROM Marks
 GROUP BY 学生;

在这个例子中sum()函数作为窗口函数,通过对‘学生’分区后,加总各个科目的分数得到总分,得分结果与sum()聚合函数的结果一致,但结果中保留了每一行的信息,出现了重复的总分行。

因为,所有窗口函数的执行在JOIN, WHERE, GROUP BY, HAVING的结果集之后,在ORDER BY, LIMIT, SELECT DISTINCT之前。当PARTITION BY执行时GROUP BY的聚合过程已经完成了,因此不会再产生数据聚合。

窗口函数的语法


window_function_name(expression) 
    OVER (
       [partition_defintion]
        [order_definition]
       [frame_definition]
    )

首先需要指定窗口函数的函数名,也就是在上个例子中用的sum(),之后的OVER子句中即使没有内容,括号也需要保留,窗口由[partition_defintion],[order_definition],[frame_definition]确定,任何一个都不是必须的。

1.partition_defintio 窗口分区


PARTITION BY expr [, expr] ...

根据表达式的计算结果来进行分区(列名也是一种表达式)。在例子中”PARTITION BY 学生”对学生列的值分区。

2. order_definition 窗口排序


ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

为分区内的行的排列顺序。以下可以实现对学生的科目分数降序排列,并得出学生的科目最高分


SELECT 学生,科目,分数,
       MAX(分数) OVER (PARTITION BY 学生 ORDER BY 分数 DESC) AS '最高分' 
        FROM Marks;

3. frame_definition 窗口框架


frame_clause:
    frame_units frame_extentframe_units:
    {ROWS | RANGE}frame_extent:
    {frame_start | frame_between}frame_between:
    BETWEEN frame_start AND frame_endframe_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING}

窗口框架的作用对分区进一步细分,frame_unit有两种,分别是ROWS和RANGE,ROWS通过指定当前行之前或之后的固定数目的行来限制分区中的行,RANGE按照排序列的当前值,根据相同值来确定分区中的行。以下通过计算当前行的前两行的平均值计算分数的移动平均分数。


SELECT 学生,科目,分数,
       AVG(分数) OVER (PARTITION BY 学生
           ORDER BY 分数 ASC
                       ROWS 2 preceding) AS moving_avg
     FROM Marks;

下面我们使用RANGE对每个分区内从第一行到当前行计算平均值,可以看到由于RANGE根据当前值来确定行,张三的第二行就已经出现了三门的均分,对于三门分数不同的李四,滑动平均值得结果没有变化。




SELECT 学生,科目,分数,
       AVG(分数) OVER (PARTITION BY 学生
                       ORDER BY 分数 ASC
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AVGFROM Marks;

frame_extent指定帧的起止点,在其中也可以只用frame_start(结束位置就默认为当前行)和frame_between指定起点和终点

frame_start和frame_end可以是以下几种:

  • CURRENT ROW: 当前行- UNBOUNDED PRECEDING: 区间的第一行- UNBOUNDED FOLLOWING:区间的最后一行- N PRECEDING: 当前行之前的N行,可以是数字,也可以是一个能计算出数字的表达式- N FOLLOWING:当前行之后的N行,可以是数字,也可以是一个能计算出数字的表达式
    UNBOUNDED PRECEDING: 区间的第一行

N PRECEDING: 当前行之前的N行,可以是数字,也可以是一个能计算出数字的表达式

如果没指定帧的话,默认的frame取决于ORDER BY。

  • 如果有ORDER BY,SQL会默认帧是区间内从第一行(UNBOUNDED PRECEDING)到当前行(CURRENTROW)
    ```

SELECT 学生,科目,分数,
MAX(分数) OVER (PARTITION BY 学生
ORDER BY 分数 ASC) AS ‘最高分’ FROM Marks;– 结果相同SELECT 学生,科目,分数,
MAX(分数) OVER (PARTITION BY 学生
ORDER BY 分数 ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ‘最高分’ FROM Marks;



<img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9SNWljMWljeU5CTmQ2V1A0cGNUOE5LVzlDdGJDRWJlQmt6Q2dLRkFDa2lheDlteDdHUjZzenFaeDVFOVlObWgxVXZoVU45WlJpY1FUb3Yyc1YyMDEyWkNGaGcvNjQw?x-oss-process=image/format,png">
- 如果没有ORDER BY,SQL会默认帧是区间内从第一行(UNBOUNDED PRECEDING)到最后一行(UNBOUNDED FOLLOWING)

SELECT 学生,科目,分数,
MAX(分数) OVER (PARTITION BY 学生) AS ‘最高分’
FROM Marks;


   -- 结果相同
   SELECT 学生,科目,分数,
   MAX(分数) OVER (PARTITION BY 学生
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS '最高分'  
   FROM Marks;


<img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9SNWljMWljeU5CTmQ2V1A0cGNUOE5LVzlDdGJDRWJlQmt6OTVSSVpObUpTR2JsRG1LWkRoQ1dYSVVNYmljaWFKRHJ3eEIySms4RDMyaGFVdjREYlVham9VekEvNjQw?x-oss-process=image/format,png">

日常我们更常用的是在窗口函数中使用排序函数:
- ROW_NUMBER: 函数名即是排序方法,也就是输出结果集分区的行号(例如:1,2,3,4,5...)- RANK: 返回结果集的分区内数据进行跳跃排序。也就是为相同数值的行输出相同排序结果,对于下一行不同的数据将返回行号(例如:1,1,3,4...- DENSE_RANK: 返回结果集分区中每行的连续排名,排名值没有间断。行排名等于该行之前不同排名值的数量加一(例如:1,1,2,3,4...- NTILE: 将有序分区中的数据分发到指定数目的组中。以本文数据为例,将60-90分的分数等分为4组,即第1组为[90, 82.5),第2组为[82.5, 75),第3组为[75, 67.5),第4组为[67.5, 60]
RANK: 返回结果集的分区内数据进行跳跃排序。也就是为相同数值的行输出相同排序结果,对于下一行不同的数据将返回行号(例如:1,1,3,4...)

NTILE: 将有序分区中的数据分发到指定数目的组中。以本文数据为例,将60-90分的分数等分为4组,即第1组为[90, 82.5),第2组为[82.5, 75),第3组为[75, 67.5),第4组为[67.5, 60]

SELECT 学生,科目,分数 
,ROW_NUMBER() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS “Row_Number”
,RANK() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS ‘Rank’
,DENSE_RANK() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS “Dense_Rank”
,NTILE(4) OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS ‘Quartile’
FROM Marks;

```

最后,再附上我历时三个月总结的 Java 面试 + Java 后端技术学习指南,这是本人这几年及春招的总结,目前,已经拿到了大厂offer,拿去不谢!

下载方式

1. 首先扫描下方二维码

2. 后台回复「Java面试」即可获取

原文地址:https://sihai.blog.csdn.net/article/details/109465512

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 不好意思,我真的不知道MySQL的窗口函数...


 上一篇
推荐 8 个常用 Spring Boot  项目 推荐 8 个常用 Spring Boot 项目
 点击上方 **好好学java **,选择 **星标 **公众号 重磅资讯、干货,第一时间送达 今日推荐:为什么程序员都不喜欢使用switch,而是大量的 if……else if ?个人原创+1博客:点击前往,查看更多 前言:****
2021-04-04
下一篇 
阿里面试官鬼得很,问我为什么他们阿里要禁用Executors创建线程池? 阿里面试官鬼得很,问我为什么他们阿里要禁用Executors创建线程池?
  点击上方 **好好学java **,选择 **星标 **公众号 重磅资讯、干货,第一时间送达 今日推荐:推荐 8 个常用 Spring Boot 项目个人原创+1博客:点击前往,查看更多 作者:何甜甜在吗 链接:https://
2021-04-04