mysql窗口函数：序号函数、分布函数

起步

• 简介

窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中

• 分类

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

• 格式

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

函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

1、OVER 关键字指定函数窗口的范围
      如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算
      如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口
2、窗口名：为窗口设置一个别名，用来标识窗口
3、PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行
4、ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号
5、FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用

• 前期准备

# 创建表
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');

序号函数

• ROW_NUMBER()函数

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

• 代码案例

# 查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息
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种商品信息
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_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;

# 查询 goods 数据表中每个商品分类下价格最高的3种商品信息
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;

• RANK()函数

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

• 代码案例

# 使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock FROM goods;

# 使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
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;

• DENSE_RANK()函数

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

• 代码案例

# 使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num, id, category_id, category, NAME, price, stock FROM goods;

# 使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
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;

分布函数

• 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);

• CUME_DIST()函数

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

• 代码案例

# 查询goods数据表中小于或等于当前价格的比例
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd, id, category, NAME, price FROM goods;