MySQL——窗口函数

川长思鸟来 2022-09-14 09:27 302阅读 0赞

#### 语法 ####

`函数名称([字段名]) OVER([PARTITION BY <分组字段>][ORDER BY <排序字段> [DESC]] [<FRAME字句>])`

*  PARTITION BY字句：按照指定字段进行分区，两个分区由边界分割，即所谓的"窗口"。如果没有指定，那么它把整个结果集作为一个分组。窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
 *  ORDER BY子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和PARTITION BY子句配合使用，也可以单独使用。默认为升序排列。
 *  FRAME子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
 *  ROWS语法：
    
     *  ROWS BETWEEN (`expr` 为指定数值) UNBOUNDED PRECEDING边界是分区中的第一行 / N PRECEDING边界是当前行减去expr的值 / expr FOLLOWING边界是当前行加上expr的值 / CURRENT ROW边界是当前行 AND UNBOUNDED FOLLOWING边界是分区中的最后一行 / expr PRECEDING / expr FOLLOWING / CURRENT ROW
     *  ROWS UNBOUNDED PRECEDING / expr PRECEDING / expr FOLLOWING / CURRENT ROW
     *  默认：ROWS BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_20_color_FFFFFF_t_70_g_se_x_16]

#### 分类 ####

按照函数功能不同，MySQL支持的窗口函数分为如下几类：

*  序号函数
    
     *  row\_number()连续的序号（不管有没有重复，1.2.3.4.5.6）
     *  rank()连续间断的序号（重复给相同序号，后面会跳过重复的序号，1.2.3.3.3.6.7）
     *  dense\_rank()连续不断的序号（重复的会给相同的序号，1.2.3.3.3.4.5）
 *  分布函数：percent\_rank() / cume\_dist()
 *  前后函数：lag() / lead()
 *  头尾函数：first\_value() / last\_value()
 *  其他函数：nth\_value() / nfile()

<table> 
 <thead> 
  <tr> 
   <th align="center">名称</th> 
   <th align="center">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">CUME_DIST()</td> 
   <td align="center">计算一组值中的一个值的累计分布</td> 
  </tr> 
  <tr> 
   <td align="center">DENSE_RANK()</td> 
   <td align="center">根据ORDER BY子句分区中的每一行分配一个等级。它将相同的等级分配给具有等同值的行。如果两行或更多行具有相同的排名，则排名值序列中将没有间隔</td> 
  </tr> 
  <tr> 
   <td align="center">FIRST_VALUE()</td> 
   <td align="center">返回相对于窗口框架第一行指定表达式的值</td> 
  </tr> 
  <tr> 
   <td align="center">LAG()</td> 
   <td align="center">返回分区中当前行之前的第N行的值。如果不存在前一行，则返回NULL</td> 
  </tr> 
  <tr> 
   <td align="center">LAST_VALUE()</td> 
   <td align="center">返回相对于窗口框架最后一行指定表达式的值</td> 
  </tr> 
  <tr> 
   <td align="center">LEAD()</td> 
   <td align="center">返回分区中当前行之后的第N行的值。如果不存在后续行，则返回NULL</td> 
  </tr> 
  <tr> 
   <td align="center">NTH_VALUE()</td> 
   <td align="center">从从窗口框架的第N行返回参数值</td> 
  </tr> 
  <tr> 
   <td align="center">NTILE()</td> 
   <td align="center">将每个窗口分区的行分配到指定数量的排名中</td> 
  </tr> 
  <tr> 
   <td align="center">PERCENT_RANK()</td> 
   <td align="center">计算分区或结果中行的百分数等级</td> 
  </tr> 
  <tr> 
   <td align="center">RANK()</td> 
   <td align="center">与DENSE_RANK()函数相似，不同指出在于当两行或更多行具有相同等级时，等级值序列中存在间隔</td> 
  </tr> 
  <tr> 
   <td align="center">ROW_NUMBER()</td> 
   <td align="center">为分区中的每一行分配一个顺序整数</td> 
  </tr> 
 </tbody> 
</table>

#### 窗口函数与普通聚合函数的区别 ####

*  聚合函数是讲多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
 *  SQL标准中允许所有聚合函数作为窗口函数。聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

#### 示例 ####

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_14_color_FFFFFF_t_70_g_se_x_16]

##### 1. 根据省份分区求各市gdp并排序 #####

SELECT
    	prov_name,
    	city_name,
    	gdp,
    	ROW_NUMBER ( ) OVER ( PARTITION BY prov_name ORDER BY gdp ) AS ranking 
    FROM
    	test_windows

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_18_color_FFFFFF_t_70_g_se_x_16]

SELECT
    	prov_name,
    	city_name,
    	gdp,
    	RANK ( ) OVER ( PARTITION BY prov_name ORDER BY gdp ) AS ranking 
    FROM
    	test_windows

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_19_color_FFFFFF_t_70_g_se_x_16]

SELECT
    	prov_name,
    	city_name,
    	gdp,
    	DENSE_RANK ( ) OVER ( PARTITION BY prov_name ORDER BY gdp ) AS ranking 
    FROM
    	test_windows

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_19_color_FFFFFF_t_70_g_se_x_16 1]

##### 2. 根据省份分区统计各市gdp并累计求和 #####

SELECT
    	prov_name,
    	city_name,
    	gdp,
    	SUM( gdp ) over ( PARTITION BY prov_name ORDER BY gdp ) AS 'ranking' 
    FROM
    	test_windows

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_18_color_FFFFFF_t_70_g_se_x_16 1]

##### 3. lag()和lead()函数 #####

参数：

1.  表达式或字段
2.  偏移量
3.  控制赋值，默认为NULL

SELECT
    	prov_name,
    	city_name,
    	gdp,
    	lag ( gdp, 1 , '无') over ( PARTITION BY prov_name ORDER BY gdp ) AS 'ranking' 
    FROM
    	test_windows

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_17_color_FFFFFF_t_70_g_se_x_16]

SELECT
    	prov_name,
    	city_name,
    	gdp,
    	lead ( gdp, 1, '无') over ( PARTITION BY prov_name ORDER BY gdp ) AS 'ranking' 
    FROM
    	test_windows

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_17_color_FFFFFF_t_70_g_se_x_16 1]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/9272e65d40c34d5db73c8b036b990d87.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_14_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/cce560912d16431ebefb98215f207e92.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/39758e3e071e4a47ab5522aaff9995d3.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/c6b3629fe01b4cc8b610d4462a24fd81.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_19_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/8750ea9780764c5ea16d62bf15b54b75.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_18_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/d7f581f266d54a8bb20cf367a8030759.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/0ad39a1d2ab64da3a4cba79f1dd86b48.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZC05aOw5a2Q5aSc5q2M_size_17_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/df823b9b0de944a4ad29f4921465dcf6.png