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的值.
