1. 窗口函数概念和语法
窗口函数对一组查询行执行类似聚合的操作 。然而,聚合操作将查询行分组到单个结果行,而窗口函数为每个查询行产生一个结果:
- 函数求值发生的行称为当前行
- 与发生函数求值的当前行相关的查询行组成了当前行的窗口
SELECTmanufacturer, product, profit,SUM(profit) OVER() AS total_profit,SUM(profit) OVER(PARTITION BY manufacturer) AS manufacturer_profitFROM sales;
![MySQL 窗口函数](http://img.zhejianglong.com/231018/0436252525-0.png)
文章插图
查询中的每个窗口操作都通过包含一个 OVER 子句来表示,该子句指定如何将查询行划分为组以供窗口函数处理:
- 第一个 OVER 子句是空的,它将整个查询行集视为一个分区 。窗口函数因此产生一个全局和,但对每一行都这样做 。
- 第二个 OVER 子句按 manufacturer 划分行,产生每个分区(每个manufacturer)的总和 。该函数为每个分区行生成此总和 。
查询结果行由 FROM 子句确定,在 WHERE、GROUP BY 和 HAVING 处理之后,窗口执行发生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前 。
OVER子句被允许用于许多聚合函数 , 因此 , 这些聚合函数可以用作窗口函数或非窗口函数,具体取决于是否存在 OVER 子句:
AVG()BIT_AND()BIT_OR()BIT_XOR()COUNT()JSON_ARRAYAGG()JSON_OBJECTAGG()MAX()MIN()STDDEV_POP(), STDDEV(), STD()STDDEV_SAMP()SUM()VAR_POP(), VARIANCE()VAR_SAMP()
MySQL还支持只能作为窗口函数使用的非聚合函数 。对于这些,OVER子句是必须的CUME_DIST()DENSE_RANK()FIRST_VALUE()LAG()LAST_VALUE()LEAD()NTH_VALUE()NTILE()PERCENT_RANK()RANK()ROW_NUMBER()
ROW_NUMBER() 它生成其分区内每一行的行号 。默认情况下,分区行是无序的,行编号是不确定的 。若要对分区行进行排序 , 请在窗口定义中包含一个ORDER BY子句 。下面的示例中,查询使用无序分区和有序分区(row_num1和row_num2列)来说明省略和包含ORDER BY之间的区别:SELECTmanufacturer, product, profit,ROW_NUMBER() OVER(PARTITION BY manufacturer) AS row_num1,ROW_NUMBER() OVER(PARTITION BY manufacturer ORDER BY profit) AS row_num2FROM sales;
![MySQL 窗口函数](http://img.zhejianglong.com/231018/043625G60-1.png)
文章插图
如前所述,要使用窗口函数(或将聚合函数视为窗口函数),需要在函数调用后包含OVER子句 。OVER子句有两种形式:
over_clause:{OVER (window_spec) | OVER window_name}
这两种形式都定义了窗口函数应该如何处理查询行 。它们的区别在于窗口是直接在OVER子句中定义的,还是通过对查询中其他地方定义的命名窗口的引用提供的:- 在第一种情况下,窗口规范直接出现在 OVER 子句中的括号之间 。
- 在第二种情况下,window_name 是由查询中其他地方的 WINDOW 子句定义的窗口规范的名称 。
window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]
如果 OVER() 为空,则窗口由所有查询行组成 , 窗口函数使用所有行计算结果 。否则,括号中的子句决定了使用哪些查询行来计算函数结果,以及它们是如何分区和排序的:- window_name: 由查询中其他地方的window子句定义的窗口的名称 。如果window_name单独出现在OVER子句中,则它完全定义了窗口 。如果分区、排序或分帧子句也给出了,它们会修改被命名窗口的解释 。
- partition_clause: PARTITION BY 子句指示如何将查询行分组 。给定行的窗口函数结果基于包含该行的分区的行 。如果省略 PARTITION BY , 则有一个由所有查询行组成的分区 。
partition_clause:PARTITION BY expr [, expr] ...
- order_clause: ORDER BY 子句指示如何对每个分区中的行进行排序 。根据 ORDER BY 子句相等的分区行被视为对等 。如果省略 ORDER BY,则分区行是无序的,没有隐含的处理顺序,并且所有分区行都是对等的 。
order_clause:ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
窗口定义中的 ORDER BY 适用于各个分区 。要将结果集作为一个整体进行排序,请在查询顶层包含 ORDER BY 。