窗口函数

准备数据

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

• 语法:多行函数 over (partition by 列名 [分组] order by 列名 desc | asc)

聚合函数也是窗口函数

查询所有的商品编号,价格,和类别名以及整体平均价格

SELECT id , NAME , price , category , AVG(price) OVER() FROM goods ;

查询所有的商品编号,价格,和类别名以及类别平均价格

SELECT id , NAME ,price, category , AVG(price) OVER (PARTITION BY category) FROM goods;

SELECT id , NAME , price , category , AVG(price) OVER(PARTITION BY category_id) FROM goods ;

1. 序号函数: row_number()

举例1：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。

SELECT ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) AS num,id , category , NAME ,price FROM 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;
--窗口函数 over (partition by 分组 order by 排序 )

举例2：查询 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;

2. 序号函数: rank()

举例：使用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;

举例2：使用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;

3. 序号函数: dense_rank()

举例1: 使用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;

举例2: 使用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;

4.分步函数: PERCENT_RANK()

举例1：计算 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;

写法二:

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