2. 2 窗口函数分类
MySQL从 8. 0 版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。
窗口函数可以分为静态窗口函数和动态窗口函数。
- 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
- 动态窗口函数的窗口大小会随着记录的不同而变化。
MySQL官方网站窗口函数的网址为 https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number 。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表：
2. 3 语法结构
窗口函数的语法结构是：
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
或者是：
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
- OVER 关键字指定函数窗口的范围。
- 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
- 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
- 窗口名：为窗口设置一个别名，用来标识窗口。
- PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
- ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
- FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
2. 4 分类讲解
创建表：
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');
下面针对goods表中的数据来验证每个窗口函数的功能。
1. 序号函数
1 ．ROW_NUMBER()函数
ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。
mysql> SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods; +---------+----+-------------+---------------+------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | | 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 | | 5 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 | | 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 | +---------+----+-------------+---------------+------------+---------+-------+ 12 rows in set (0.00 sec)
举例：查询 goods 数据表中每个商品分类下价格最高的 3 种商品信息。
mysql> SELECT * -> FROM ( -> SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods) t -> WHERE row_num <= 3; +---------+----+-------------+---------------+------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | +---------+----+-------------+---------------+------------+----------+-------+ 6 rows in set (0.00 sec)
在名称为“女装/女士精品”的商品类别中，有两款商品的价格为 89. 90 元，分别是卫衣和牛仔裤。两款商品的序号都应该为 2 ，而不是一个为 2 ，另一个为 3 。此时，可以使用RANK()函数和DENSE_RANK()函数解决。
2 ．RANK()函数
使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为 1 、 1 、 3 。
举例：使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
mysql> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods; +---------+----+-------------+---------------+------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | | 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 | | 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 | | 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 | +---------+----+-------------+---------------+------------+---------+-------+ 12 rows in set (0.00 sec)
举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的 4 款商品信息。
mysql> SELECT * -> FROM( -> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods) t -> WHERE category_id = 1 AND row_num <= 4; +---------+----+-------------+---------------+----------+--------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+----------+--------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | +---------+----+-------------+---------------+----------+--------+-------+ 4 rows in set (0.00 sec)
可以看到，使用RANK()函数得出的序号为 1 、 2 、 2 、 4 ，相同价格的商品序号相同，后面的商品序号是不连续的，跳过了重复的序号。
3 ．DENSE_RANK()函数
DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为 1 、 1 、 2 。
举例：使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
mysql> SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods; +---------+----+-------------+---------------+------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 4 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 5 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | | 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 | | 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 | | 5 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 | +---------+----+-------------+---------------+------------+---------+-------+ 12 rows in set (0.00 sec)
举例：使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的 4 款商品信息。
mysql> SELECT * -> FROM( -> SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods) t -> WHERE category_id = 1 AND row_num <= 3; +---------+----+-------------+---------------+----------+--------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+----------+--------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | +---------+----+-------------+---------------+----------+--------+-------+ 4 rows in set (0.00 sec)
可以看到，使用DENSE_RANK()函数得出的行号为 1 、 2 、 2 、 3 ，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。
2. 分布函数
1 ．PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
(rank - 1) / (rows - 1)
其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。
举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
#写法一： 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; #写法二： mysql> 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 (PARTITION BY category_id ORDER BY price DESC); +---+-----+----+-------------+---------------+----------+--------+-------+ | r | pr | id | category_id | category | NAME | price | stock | +---+-----+----+-------------+---------------+----------+--------+-------+ | 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | +---+-----+----+-------------+---------------+----------+--------+-------+ 6 rows in set (0.00 sec)
2 ．CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
举例：查询goods数据表中小于或等于当前价格的比例。
mysql> SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd, -> id, category, NAME, price -> FROM goods; +---------------------+----+---------------+------------+---------+ | cd | id | category | NAME | price | +---------------------+----+---------------+------------+---------+ | 0.16666666666666666 | 5 | 女装/女士精品 | 百褶裙 | 29.90 | | 0.3333333333333333 | 1 | 女装/女士精品 | T恤 | 39.90 | | 0.5 | 2 | 女装/女士精品 | 连衣裙 | 79.90 | | 0.8333333333333334 | 3 | 女装/女士精品 | 卫衣 | 89.90 | | 0.8333333333333334 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | | 1 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | | 0.16666666666666666 | 9 | 户外运动 | 登山杖 | 59.90 | | 0.5 | 7 | 户外运动 | 自行车 | 399.90 | | 0.5 | 10 | 户外运动 | 骑行装备 | 399.90 | | 0.6666666666666666 | 12 | 户外运动 | 滑板 | 499.90 | | 0.8333333333333334 | 11 | 户外运动 | 运动外套 | 799.90 | | 1 | 8 | 户外运动 | 山地自行车 | 1399.90 | +---------------------+----+---------------+------------+---------+ 12 rows in set (0.00 sec)
