语法结构
窗口函数的语法结构:
- 函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
- 函数 OVER 窗口名 … WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
模拟数据
创建表
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');
函数介绍
1. 序号函数
1.1 row_number()
单纯的对每一组数据编号
-- 对查询结果的序号进行显示
SELECT
*,
ROW_NUMBER() OVER () AS row_num
FROM goods;
-- 每个商品分类下的价格，降序展示
select *,ROW_NUMBER() OVER(PARTITION BY category ORDER BY price desc) as row_num from goods
-- 查询每个商品分类下，价格最高的三种商品信息
select * from
(
select *,ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) as top3Price from goods
) as t
where top3Price<= 3
1.2 rank()
排序每一组的某一字段, 同等级同序号前后不连续
-- 使用RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息
select *,RANK() OVER(PARTITION BY category ORDER BY price DESC) as rank_num from goods
-- 使用RANK()函数获取goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息
select * from goods where category='女装/女士精品' order by price desc limit 4;
select *,RANK() OVER(partition by category order by price desc) as top4Price from goods where category='女装/女士精品' limit 4
1.3 dense_rank()
排序每一组的某一字段, 同等级同序号前后也连续
SELECT *,DENSE_RANK() OVER (partition by category ORDER BY price desc) as price_rank from goods;
2. 分布函数
2.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_id,category,name,price,stock from goods where category_id=1;
2.2 cume_dist()
累计分布值, <=当前rank值的行数 / 分组内总行数
select CUME_DIST() over(partition by category_id ORDER BY price asc) as cd,id,category,name,price from goods;
3. 前后函数
3.1 LAG(expr, n)
返回当前行的前n行(本组内)的expr值
-- 前一个商品的价格
select *,LAG(price,1) over (partition by category_id order by price desc) as prePrice from goods;
3.2 LEAD(expr,n)
返回当前行的后n行(本组)的expr值
select id,category,name,price,lead(price,1) over w as behindPrice from goods WINDOW w as (partition by category_id order by price)
4. 首位函数
4.1 first_value(expr)
取分组内排序后，截止到当前行，第一个值
select id,name,category,price,FIRST_VALUE(price) over (partition by category_id ORDER BY price desc) as firstVal from goods;
4.2 last_value(expr)
取分组内排序后，截止到当前行，最后一个值
默认帧规范
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
这意味着框架从第一行开始，到结果集的当前行结束。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
这表示框架从第一行开始，到结果集的最后一行结束。
select id,name,category,price,LAST_VALUE(price) over (order by price RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as lastValue from goods;
select id,name,category,price,LAST_VALUE(price) over (partition by category_id order by price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lastValue from goods;
5. 其他函数
5.1 nth_value(expr, n)
返回第n个expr的值
-- 查询goods中排名第二和排名第三的价格信息
select id,category,name,price,NTH_VALUE(price,2) over w as secondPrice,NTH_VALUE(price,3) over w as thirdPrice from goods window w as (partition by category_id ORDER BY price)
5.2 ntile(n)
将分区中的有序数据分为n个桶，记录桶编号。
-- 将goods表中的商品按照价格分为3组。
select ntile(3) over (partition by category_id ORDER BY price),id,category,name,price from goods;