【MySQL进阶之路 | 基础篇】MySQL新特性 : 窗口函数

1. 前言

(1). MySQL8开始支持窗口函数. 其作用类似于在查询中对数据进行分组(GROUP BY)，不同的是，分组操作会把分组的结果聚合成一条记录. 而窗口函数是将结果置于每一条数据记录中.

(2). 窗口函数还可以分为静态窗口函数和动态窗口函数.

• 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同.
• 动态窗口的窗口大小会随着记录的不同而变化.

(3). 窗口函数在整体上还可以分为序号函数，分布函数，前后函数，首尾函数和其他函数.

2. 窗口函数

(1). 语法结构

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

• OVER关键字指定窗口的范围. 如果可以省略后面的括号里的内容，则窗口包含WHERE子句符合条件的所有记录. 窗口函数会对于这些记录进行计算. 如果括号内的内容不为空，可以使用如下语法设置窗口.
• 窗口名. 可以为窗口用AS关键字设置一个别名.
• PARTITION BY : 分区，指定窗口函数按照哪些字段进行分区. 分区后，窗口函数在每个分区中分别计算.
• ORDER BY : 指定窗口函数按照哪些字段进行排序. 执行排序操作使窗口函数按照排序后的数据记录的顺序编号.
• FRAME : 为分区中的某个子集定义规则，可以用来作为滑动窗口使用.

(2). 准备工作

CREATE DATABASE mydbms4;
 
USE mydbms4;
 
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');

3. 序号函数

(1). ROW_NUMBER()函数

ROW_NUMBER函数能对数据的序号进行顺序显示.

SELECT ROW_NUMBER() OVER (PARTITION BY category_id 
ORDER BY price DESC) AS row_num, 
category,name,price,stock,upper_time
FROM goods;
 
 
1	女装/女士精品	呢绒外套	399.90	1200	2020-11-10 00:00:00
2	女装/女士精品	卫衣	89.90	1500	2020-11-10 00:00:00
3	女装/女士精品	牛仔裤	89.90	3500	2020-11-10 00:00:00
4	女装/女士精品	连衣裙	79.90	2500	2020-11-10 00:00:00
5	女装/女士精品	T恤	39.90	1000	2020-11-10 00:00:00
6	女装/女士精品	百褶裙	29.90	500	2020-11-10 00:00:00
1	户外运动	山地自行车	1399.90	2500	2020-11-10 00:00:00
2	户外运动	运动外套	799.90	500	2020-11-10 00:00:00
3	户外运动	滑板	499.90	1200	2020-11-10 00:00:00
4	户外运动	自行车	399.90	1000	2020-11-10 00:00:00
5	户外运动	骑行装备	399.90	3500	2020-11-10 00:00:00
6	户外运动	登山杖	59.90	1500	2020-11-10 00:00:00

• 由查询结果可以知道，依据category_id将表中记录分为两个分区，窗口函数分别在每个分区进行计算. 并对表中记录进行顺序显示.

(2). RANK()函数

使用RANK函数能对序号进行并列排序，并且会跳过重复序号. 如1，1，3.

SELECT RANK() OVER (PARTITION BY category_id 
ORDER BY price DESC) AS row_num, 
category,name,price,stock,upper_time
FROM goods;

(3). DENSE_RANK()函数

使用该函数进行并列排序，并且不会跳过重复序号. 比如1，1，2.

SELECT DENSE_RANK() OVER (PARTITION BY category_id 
ORDER BY price DESC) AS row_num, 
category,name,price,stock,upper_time
FROM goods;

4. 分布函数

(1). PERCENT_RANK()函数

该函数是百分比函数，其按照(rank-1)/(rows-1)进行计算.

rank是RANK()函数计算的序号，row是当前窗口的总记录数.

SELECT PERCENT_RANK() OVER (PARTITION BY category_id 
ORDER BY price DESC) AS row_num, 
category,name,price,stock,upper_time
FROM goods;

(2). CUME_DIST()函数

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

查询当前表＜或＝当前价格的比例.

SELECT CUME_DIST() OVER (PARTITION BY category_id 
ORDER BY price) AS row_num, 
category,name,price,stock,upper_time
FROM goods;

5. 前后函数

(1). LAG(expr，n)函数

该函数返回当前行的前n个行的expr的值.

SELECT LAG(price, 1) OVER (PARTITION BY category_id 
ORDER BY price) AS row_num, 
category,name,price,stock,upper_time
FROM goods;

​(2). LEAD(expr，n)函数

该函数返回当前行的后n个行的expr的值.

​6. 首尾函数

(1). FIRST_VALUE(expr)

该函数返回expr字段第一个expr的值.

SELECT FIRST_VALUE(price) OVER (PARTITION BY category_id 
ORDER BY price) AS row_num, 
category,name,price,stock,upper_time
FROM goods;

(2). LAST_VALUE(expr)函数

该函数用来返回expr字段最后一个expr的值.