在开启该测试之前，需要确保你的SQL版本
select version();
好的，接下来我们开始练习以及测试：
1.窗口函数分类
1.1 什么叫窗口函数
窗口函数的作用类似于在查询中对数据进行分组，不同的是，
分组操作会把分组的结果聚合成一条记录，而窗口函数是将分组的结果置于每一条数据记录中。
窗口函数可以分为静态窗口函数和动态窗口函数
1.静态窗口函数的窗口大小是固定的, 不会因为记录的不同而不同;
2.动态窗口函数的窗口大小会随着记录的不同而变化;1.2 分类
窗口函数总体上可以分为：
1.序号函数
2.分布函数
3.前后函数
4.首尾函数
5.其他函数
1.2.1 分类明细
序号函数
函数：row_number()
说明：顺序排序函数：rank()
说明：并列排序，会跳过重复的序号，如1 1 3（正常为：1 2 3）函数：dense_rank()
说明：并列排序，不会跳过重复的序号，如1 1 2 2 3 3 （正常为：1 2 3 4 5，因为它们的值一样，所以排名序号一致，不跳过）分布函数：
函数：percent_rank()
说明：等级值百分比函数：cume_dist()
说明：累积分布值
前后函数：
函数：lag(expr,n)
说明：返回当前行的前n行的expr的值函数：lead(expr,n)
说明：返回当前行的后n行的expr的值
首尾函数：
函数：first_value（expr）
说明：返回第一个expr的值函数：last_value（expr）
说明：返回最后一个expr的值
其他函数：
函数：nth_value(expr,n)
说明：返回第n个expr的值函数：ntile(n)
说明：将分区中的有序数据分为n个桶，记录桶的编号
1.2.2 语法结构
窗口函数的语法结构:
函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
或者是
函数 OVER 窗口名 … WInDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]) OVER 关键字指定窗口的范围;
1.如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，
窗口函数会基于所有满足WHERE条件的记录进行计算。
2.如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
PARTITION BY 子句: 指定窗口函数按照哪些字段进行分组, 分组后, 窗口函数可以在每个分组中分别执行;
ORDER BY 子句: 指定窗口函数按照哪些字段进行排序, 执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号;
1.3 窗口函数使用
1.3.1 数据准备
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');2.函数实操
2.1 序号函数 row_number()
概述：
序号函数是按照一定的分组规则对每一组的数据排序并创建一个序号列
row_number() - 单纯的对每一组数据编号
row_number() 对数据中的序号进行顺序显示
SELECT *,ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num
FROM goods;
查询 goods 数据表中每个商品分类下价格最高的3种商品信息
select * from
(
SELECT *,ROW_NUMBER() over(PARTITION by category order by price desc) num
from goods
)as t
where num <= 3;
2.2 排序函数 rank() 序号可以重复
rank() - 排序每一组的某一字段, 同等级同序号前后不连续
select *,RANK() over(PARTITION by category order by price desc) as 并列排序 from goods
使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
-- 并列排序.
-- 使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
select * from (
select *,RANK() over(PARTITION by category order by price desc) as 并列排序 from goods
)as t
where t.category = '女装/女士精品'
LIMIT 4;
2.3 排序函数 序号可以并列 dense_rank()
dense_rank() - 排序每一组的某一字段, 同等级同序号前后也连续
select *,DENSE_RANK() over(PARTITION by category order by price desc) as 并列排序 from goods
DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
select * from (
select *,RANK() over(PARTITION by category order by price desc) as 并列排序 from goods
)as t
where t.category = '女装/女士精品'
and t.并列排序 <= 4;
2.4 分布函数
2.4.1 percent_rank() - 等级值百分比, (rank - 1)/ (rows - 1)
percent_rank() - 等级值百分比, (rank - 1)/ (rows - 1)
-- 语法一
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,`NAME`,price,stockfrom goodswhere category_id = 1;-- 语法二
select rank() over w as r,
percent_rank() over w as pr,
id,category,`NAME`,price,stockfrom goodswhere category_id = 1 WINDOW w as (PARTITION by category_id order by price desc)
2.4.2 cume_dist() - 累积分布值, <=当前rank值的行数 / 分组内总行数
cume_dist() - 累积分布值, <=当前rank值的行数 / 分组内总行数
-- 查询goods数据表中小于或等于当前价格的比例
select *,CUME_DIST() over(PARTITION by category_id order by price desc) as cd
from goods
2.5 前后函数
2.5.1 LAG(expr, n) - 返回当前行的前n行(本组内)的expr值
LAG(expr, n) - 返回当前行的前n行(本组内)的expr值
select * ,lag(price,1) over(PARTITION by category order by price desc) as pre_price
from goods;
select *,price-pre_price as 差价 from (
select * ,lag(price,1) over(PARTITION by category order by price desc) as pre_price
from goods)as t
2.5.2 LEAD(expr, n) 返回当前行的后n行（本组）的expr值
LEAD(expr, n)
-- 查询goods数据表中后一个商品价格与当前商品价格的差值
select *,price-pre_price as 差价 from (
select * ,lead(price,1) over(PARTITION by category order by price desc) as pre_price
from goods)as t
2.6 首尾函数
2.6.1 first_value(expr) 取分组内排序后，截止到当前行，第一个值
first_value(expr) , last_value(expr)
first_value 取分组内排序后，截止到当前行，第一个值
-- first_value 取分组内排序后，截止到当前行，第一个值
SELECT *,FIRST_VALUE(price) over(PARTITION by category_id order by price desc) as firstValue
from goods;
2.6.2 last_value 取分组内排序后，截止到当前行，最后一个值
last_value ：取分组内排序后，截止到当前行，最后一个值
SELECT *,LAST_VALUE(price) over(PARTITION by category_id order by price ) as lastValue
from goods;
2.7 其他函数
2.7.1 nth_value(expr, n) 函数返回第n个expr的值
NTH_VALUE（expr,n）函数返回第n个expr的值
-- NTH_VALUE（expr,n）函数返回第n个expr的值。
SELECT *,NTH_VALUE(price,2) over(PARTITION by category_id order by price ) as nthValue
from goods;
2.7.2 ntile(n) 函数将分区中的有序数据分为n个桶，记录桶编号
NTILE（n）函数将分区中的有序数据分为n个桶，记录桶编号
-- NTILE（n）函数将分区中的有序数据分为n个桶，记录桶编号。
SELECT *,ntile(4) over(PARTITION by category_id order by price ) as num
from goods;
-- NTILE（n）函数将分区中的有序数据分为n个桶，记录桶编号。
SELECT *,ntile(3) over(PARTITION by category_id order by price ) as num
from goods;
