MySQL基础--10---MySQL8新特性----窗口函数

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

窗口函数

• MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

窗口函数分类

MySQL官方网站窗口函数的网址为https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

语法结构

举例分类讲解

创建表：

CREATETABLE goods(
id INTPRIMARYKEYAUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME);

INSERTINTO 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');

1.1 序号函数

ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

sql函数–04—ROW_NUMBER() OVER()函数用法详解

举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。

SELECT ROW_NUMBER()OVER(PARTITIONBY category_id ORDERBY price DESC)AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;

查询 goods 数据表中每个商品分类下价格最高的3种商品信息。

SELECT*FROM(SELECT ROW_NUMBER()OVER(PARTITIONBY category_id ORDERBY price DESC)AS row_num,
id, category_id, category, NAME, price, stock
FROM goods
) t
WHERE row_num <=3;

1.2 RANK()函数

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

举例：使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。

SELECT RANK()OVER(PARTITIONBY category_id ORDERBY price DESC)AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;

举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。

SELECT*FROM(SELECT RANK()OVER(PARTITIONBY category_id ORDERBY price DESC)AS row_num,
 id, category_id, category, NAME, price, stock
 FROM goods
 ) t
 WHERE category_id =1AND row_num <=4;

可以看到，使用RANK()函数得出的序号为1、2、2、4，相同价格的商品序号相同，后面的商品序号是不
连续的，跳过了重复的序号。

1.3 DENSE_RANK()函数

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

举例：使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。

SELECT*FROM(SELECT DENSE_RANK()OVER(PARTITIONBY category_id ORDERBY price DESC)AS
row_num,
id, category_id, category, NAME, price, stock
FROM goods
) t
WHERE category_id =1AND row_num <=3;

可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。

2. 分布函数

2.1 PERCENT_RANK()函数

举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。

• 写法一：

SELECT RANK()OVER(PARTITIONBY category_id ORDERBY price DESC)AS r,
PERCENT_RANK()OVER(PARTITIONBY category_id ORDERBY price DESC)AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id =1;

• 写法二：

SELECT RANK()OVER w AS r,
PERCENT_RANK()OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id =1 WINDOW w AS(PARTITIONBY category_id ORDERBY price
DESC);

2.2 CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

SELECT CUME_DIST()OVER(PARTITIONBY category_id ORDERBY price ASC)AS cd,
id, category, NAME, price
FROM goods;

3. 前后函数

3.1 LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值。

举例：查询goods数据表中前一个商品价格与当前商品价格的差值。

SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
 FROM(SELECT id, category, NAME, price,
 LAG(price,1)OVER(PARTITIONBY category_id ORDERBY price)AS pre_price
 FROM goods
 ) t;

SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
 FROM(SELECT id, category, NAME, price,LAG(price,1)OVER w AS pre_price
 FROM goods
 WINDOW w AS(PARTITIONBY category_id ORDERBY price)) t;

3.2 LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值。

举例：查询goods数据表中后一个商品价格与当前商品价格的差值。

SELECT id, category, NAME, behind_price, price,behind_price - price AS
diff_price
FROM(SELECT id, category, NAME, price,LEAD(price,1)OVER w AS behind_price
FROM goods WINDOW w AS(PARTITIONBY category_id ORDERBY price)) t;

4. 首尾函数

4.1 FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值。

举例：按照价格排序，查询第1个商品的价格信息。

SELECT id, category, NAME, price, stock,
FIRST_VALUE(price)OVER(PARTITIONBY category_id ORDERBY price)AS first_price
FROM goods ;

SELECT id, category, NAME, price, stock,FIRST_VALUE(price)OVER w AS
first_price
FROM goods WINDOW w AS(PARTITIONBY category_id ORDERBY price);

4.2 LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。

举例：按照价格排序，查询最后一个商品的价格信息。

SELECT id, category, NAME, price, stock,LAST_VALUE(price)OVER(PARTITIONBY category_id ORDERBY price)AS last_price
FROM goods  ;

SELECT id, category, NAME, price, stock,LAST_VALUE(price)OVER w AS last_price
FROM goods WINDOW w AS(PARTITIONBY category_id ORDERBY price);

5. 其他函数

5.1 NTH_VALUE(expr,n)函数

NTH_VALUE(expr,n)函数返回第n个expr的值。

举例：查询goods数据表中排名第2和第3的价格信息。

SELECT id, category, NAME, price,
NTH_VALUE(price,2)OVER w AS second_price,
NTH_VALUE(price,3)OVER w AS third_price
FROM goods WINDOW w AS(PARTITIONBY category_id ORDERBY price);

5.2 NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

举例：将goods表中的商品按照价格分为3组

SELECT NTILE(3)OVER w AS nt,
 id, category, NAME, price
 FROM goods WINDOW w AS(PARTITIONBY category_id ORDERBY price);

将goods表中的商品按照价格分为4组

SELECT NTILE(4)OVER w AS nt,
 id, category, NAME, price
 FROM goods WINDOW w AS(PARTITIONBY category_id ORDERBY price);