mysql 窗口函数

语法结构

窗口函数的语法结构:

1. 函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
2. 函数 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;