【第34天】SQL进阶-SQL高级技巧-Window Funtion（SQL 小虚竹)

- 日理万妓 2024-04-24 20:45 69阅读 0赞

回城传送–》[《100天精通MYSQL从入门到就业》][100_MYSQL]

#### 文章目录 ####

*  零、前言
 *  一、练习题目
 *  二、SQL思路
 *   *   *  初始化数据
         *  什么是Window Funtion
         *  窗口函数的分类
         *  语法结构
         *   *  第一种写法：
             *  第二种写法：
         *  实战体验
         *   *  序号函数：row\_number()
             *  序号函数：rank()
             *  序号函数：dense\_rank()
             *  分布函数:percent\_rank()
             *  分布函数:cume\_dist()
             *  前后函数：LAG(expr, n)
             *  前后函数：LEAD(expr, n)
             *  首尾函数：first\_value(expr)
             *  首尾函数：last\_value(expr)
             *  其他函数：nth\_value(expr, n)
             *  其他函数：ntile(n)
     *  三、总结
     *  四、参考

## 零、前言 ##

今天是学习 **SQL** 打卡的第 **34** 天。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 **SQL** 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：**SQL高级技巧-Window Funtion**

## 一、练习题目 ##

<table> 
 <thead> 
  <tr> 
   <th>题目链接</th> 
   <th>难度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://edu.csdn.net/skill/practice/mysql-0f7b5745d0b44251a678bbd2bbee5f2e/29640?typeId=80852&amp;language=mysql02&amp;materialId=80851" rel="nofollow">工资最高的人</a></td> 
   <td>★★★☆☆</td> 
  </tr> 
 </tbody> 
</table>

## 二、SQL思路 ##

![在这里插入图片描述][797236cddd194ef8887cf03edda04c5c.png]  
![在这里插入图片描述][20e33419a62d4b18aa5e6a29617e1a74.png]

#### 初始化数据 ####

创建表

CREATE TABLE goods(
    
        id INT PRIMARY KEY AUTO_INCREMENT,
        category_id INT,
        category VARCHAR(15),
        NAME VARCHAR(30),
        price DECIMAL(10,2),
        stock INT,
        upper_time DATETIME
    );

插入数据:

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
    VALUES
    (1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
    (1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
    (1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
    (1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
    (1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
    (1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
    (2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
    (2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
    (2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
    (2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
    (2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
    (2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

#### 什么是Window Funtion ####

窗口函数特别适用于需要对分组统计结果中的每一条记录进行计算的场景。

使用窗口函数，你可以为结果集中的每一行返回特定的计算结果，而不是像普通的聚合函数那样，只针对每个分组返回一条记录。通过窗口函数，你可以同时查看原始数据以及与之相关的计算值，为数据分析提供了更丰富的信息。

#### 窗口函数的分类 ####

MySQL8.0版本起开始支持窗口函数。  
窗口函数在查询中发挥着类似分组数据的作用，但不同的是：

*  分组操作是将每个分组的结果聚合为单一的记录。
 *  窗口函数将分组的结果直接融入到查询结果集中的每一条数据记录中

窗口函数分为静态函数和动态函数

*  静态窗口函数的特点在于其窗口大小是固定的，无论处理的是哪条记录，窗口的边界都保持不变。
 *  动态窗口函数的窗口大小则会根据记录的不同而发生变化。这种变化可能是基于数据的某个字段值，或者是基于记录之间的相对位置。

#### 语法结构 ####

##### 第一种写法： #####

函数 OVER （PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC）[window_frame] as 自定义别名

字段解释：

*  函数： 有多个函数可选择
    
     *  序号函数：row\_number()顺序编号；rank()并列编号，跳过重复的；dense\_rank() 并列编号，不会跳过重复的；
     *  分布函数：percent\_rank()等级比百分比；cume\_dist()累积分布值；
     *  前后函数：LAG(expr, n) 返回当前行的前n行(本组内)的expr值（expr是指定字段）；LEAD(expr, n)返回当前行的后n行(本组内)的expr值（expr是指定字段）
     *  首尾函数：first\_value(expr) 返回指定字段，**截止到当前行**，第一行的值；last\_value(expr)返回指定字段，**截止到当前行**，最后一行的值；
     *  其他函数：nth\_value(expr, n)返回对应字段，按排序下，第n行的值；ntile(n)将结果集划分为大致相等的n个部分，并为每行数据返回一个“桶”编号，在数据分桶、排名分组等场景有用。
 *  OVER：这是定义窗口的关键字，它后面跟着圆括号，用于包含窗口的设置。
 *  PARTITION BY：这是可选的子句，用于将结果集分成不同的分区（组）。partition\_expression是一个表达式，根据它的值来划分不同的分区。窗口函数将在每个分区内独立计算。
 *  ORDER BY：按指定字段排序
 *  window\_frame：这是可选的子句，用于指定在每个分区中用于窗口函数的行范围。它决定了哪些行包含在计算中。如果未指定window\_frame，则窗口函数默认使用所有分区中的所有行。

##### 第二种写法： #####

SELECT
    	...,
    	函数 OVER 自定义的窗口名 AS 自定义的列名,
    	函数 OVER 自定义的窗口名 AS 自定义的列名2
    FROM
    	GOODS window 自定义的窗口名 as （PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC）;

如果窗口在sql中多次使用，则可以提取出来，用自定义窗口名的方式复用，简化sql。

#### 实战体验 ####

##### 序号函数：row\_number() #####

对数据进行顺序排号  
例子：查询goods表中每个商品类别对价格降序排列的商品信息。

SELECT 
    	*,
    	ROW_NUMBER() OVER (PARTITION BY CATEGORY
    ORDER BY
    	PRICE DESC) AS ROW_NUM
    FROM
    	GOODS;

我们可以看到，已经对CATEGORY字段进行区分，并对PRICE进行了降序排列，对每个CATEGORY下的商品进行顺序排序。  
![在这里插入图片描述][a8e25388396b419a88a8bfc266ef8fab.png]

##### 序号函数：rank() #####

并列编号，跳过重复的：如, 1,1,3,4 而不会是 1,2,3,4  
例子：查询goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。

SELECT 
    	*,
    	RANK() OVER (PARTITION BY CATEGORY
    ORDER BY
    	PRICE DESC) AS TOP4PRICE
    FROM 
    	GOODS
    WHERE 
    	CATEGORY = '女装/女士精品'
    LIMIT 4;

如图所示，TOP4PRICE字段的排序是1，2，2，4  
![在这里插入图片描述][611888ff143d4d0e9c82bcf0973abdad.png]

##### 序号函数：dense\_rank() #####

并列编号，不会跳过重复的；如, 1,1,2,4  
例子：查询goods表中每个商品类别对价格降序排列的商品信息。

SELECT
    	*,
    	DENSE_RANK() OVER(PARTITION BY CATEGORY
    ORDER BY
    	PRICE DESC) AS PRICE_RANK
    FROM 
    	GOODS

如图所示，可以看到PRICE\_RANK 的排序是1,2,2,3,4,5  
![在这里插入图片描述][a062970f4bef40be8c66e39f0c90bb85.png]

##### 分布函数:percent\_rank() #####

等级比百分比  
例子：查询goods 数据表中类别为“女装/女士精品”,价格的百分数排名。

SELECT
    	RANK() OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE DESC) AS R,
    	PERCENT_RANK() OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE DESC) AS PR,
    	ID,
    	CATEGORY_ID,
    	CATEGORY,
    	NAME,
    	PRICE ,
    	STOCK
    FROM
    	GOODS
    WHERE
    	CATEGORY_ID = 1;

如图可以看出，按category\_id分区数据，并按PRICE降序排列。PERCENT\_RANK()函数会计算每个商品在其类别中的百分比排名。  
![在这里插入图片描述][0e1c846161fb4f12a028d92ca9575c0f.png]

##### 分布函数:cume\_dist() #####

累积分布值  
例子：查询goods 数据表小于或等于当前价格的比例

SELECT
    	CUME_DIST() OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE ASC) AS CD,
    	ID,
    	CATEGORY,
    	NAME,
    	PRICE
    FROM
    	GOODS;

> cume\_dist()=分区中值小于或等于当前行值的行数（包括当前行自身）/分区中的总行数

百褶裙的价格是29.9,没有比它更低的价格，女装/女士精品这个分区有6个，1/6=0.16666666666666667  
![在这里插入图片描述][8b1bd5431f4e482aaa5f7e7cdfbc8c2a.png]

##### 前后函数：LAG(expr, n) #####

返回当前行的前n行(本组内)的expr值（expr是指定字段）  
例子：查询goods 数据表同一个分类下，价格降序，前一个商品价格。

SELECT
    	*,
    	LAG(PRICE, 1) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE DESC) AS PRE_PRICE
    FROM
    	GOODS;

![在这里插入图片描述][6bb8f47136d94f658c090a45f7a07d26.png]

##### 前后函数：LEAD(expr, n) #####

返回当前行的后n行(本组内)的expr值（expr是指定字段）  
例子：查询goods 数据表同一个分类下，价格降序，后一个商品价格。

SELECT
    	*,
    	LEAD(PRICE, 1) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE DESC) AS PRE_PRICE
    FROM
    	GOODS;

![在这里插入图片描述][2843a34d5735425c9e36071c77e3d05a.png]

##### 首尾函数：first\_value(expr) #####

返回指定字段，截止到当前行，第一行的值；  
例子：取每个商品对应分类下，价格降序下，返回指定字段的第一行的值。

SELECT
       *,
       FIRST_VALUE(PRICE) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
       PRICE DESC) AS FV
    FROM
       GOODS;

![在这里插入图片描述][e4e9b35cb89144bca4cb633a896ccb4c.png]

##### 首尾函数：last\_value(expr) #####

返回指定字段，截止到当前行，最后一行的值；

例子：取每个商品对应分类下，价格降序下，返回指定字段的最后一行的值。

SELECT
       *,
       LAST_VALUE(PRICE) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
       PRICE desc) AS LV
    FROM
       GOODS;

![在这里插入图片描述][e0bcb088a21d462aa245de3799cd1700.png]

##### 其他函数：nth\_value(expr, n) #####

返回对应字段，按排序下，第n行的值  
**注意：这个是截止当前行的排序**  
![在这里插入图片描述][6868500d6fc14d6380e0327f8ab385e5.png]

例子：查询每个商品，在对应分类下，价格升序排行在第2，第3行的数据

SELECT
    	*,
    	NTH_VALUE(PRICE,
    	2) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE ASC) AS SECOND_PRICE,
    	NTH_VALUE(PRICE,
    	3) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE ASC) AS THIRD_PRICE
    FROM
    	GOODS;

![在这里插入图片描述][33b08fd670a148b78929203d24423d00.png]  
第二种写法：

SELECT
    	*,
    	NTH_VALUE(PRICE,
    	2) OVER w AS SECOND_PRICE,
    	NTH_VALUE(PRICE,
    	3) OVER w AS THIRD_PRICE
    FROM
    	GOODS window w as (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE ASC);

![在这里插入图片描述][9b8f96a7b15448a78910edba38c59588.png]

##### 其他函数：ntile(n) #####

将结果集划分为大致相等的n个部分，并为每行数据返回一个“桶”编号，在数据分桶、排名分组等场景有用。  
例子：将商品价格按商品分类，分为三个等级；

SELECT
    	NTILE(3) OVER (PARTITION BY CATEGORY_ID
    ORDER BY
    	PRICE ASC) AS N,
    	ID,
    	CATEGORY,
    	NAME,
    	PRICE
    FROM
    	GOODS;

![在这里插入图片描述][b6ccd11e0a924a5ea4f3eadd6b5fb783.png]

### 三、总结 ###

本文分享了什么是Window Function，并介绍了Window Function的分类，语法结构，并以实战例子介绍每个函数如何使用Window Function。

所以，嗯，这题的答案选。。评论区大声告诉虚竹哥。

### 四、参考 ###

[MySQL进阶技能树–》SQL高级技巧–》Window Function][MySQL_SQL_Window Function]

我是虚竹哥，我们明天见~

[100_MYSQL]: https://blog.csdn.net/shi_hong_fei_hei/category_11929488.html
[797236cddd194ef8887cf03edda04c5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/d4411d3f978c4251bf69656b3c1ac7ce.png
[20e33419a62d4b18aa5e6a29617e1a74.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/5348e74b5ce142338093d8c7902ccc67.png
[a8e25388396b419a88a8bfc266ef8fab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/cee3528e5fdf4990a88bbd82ead11010.png
[611888ff143d4d0e9c82bcf0973abdad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/e5971c70529148a9a2e32e4b2d913c2c.png
[a062970f4bef40be8c66e39f0c90bb85.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/226785cd58ee4a09bfa6d769cd68ce3e.png
[0e1c846161fb4f12a028d92ca9575c0f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/47a9904eae924810918ce19d00a2b20d.png
[8b1bd5431f4e482aaa5f7e7cdfbc8c2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/7c327a6832dd4fb4a9070fd13680ee03.png
[6bb8f47136d94f658c090a45f7a07d26.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/27d3606832f44d128532387bbdbac5fa.png
[2843a34d5735425c9e36071c77e3d05a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/1fa13840d0e84310811a7c2d09b360dc.png
[e4e9b35cb89144bca4cb633a896ccb4c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/f3f8309933cc43c38f71db29152ff552.png
[e0bcb088a21d462aa245de3799cd1700.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/bc1524b02a9e40b380ac3262ba550d51.png
[6868500d6fc14d6380e0327f8ab385e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/dbb8c4f5116647a5a7a9f7b6067c951a.png
[33b08fd670a148b78929203d24423d00.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/3661e6dedb2e4d178fc8002d11bacd8b.png
[9b8f96a7b15448a78910edba38c59588.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/360409c8844b490abc7b8a60de48c409.png
[b6ccd11e0a924a5ea4f3eadd6b5fb783.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/5916f2825ebc490085a47576a682cdaf.png
[MySQL_SQL_Window Function]: https://edu.csdn.net/skill/mysql02/mysql-0f7b5745d0b44251a678bbd2bbee5f2e?typeId=80851