2.1 使用窗口函数前后对比
假设我现在有这样一个数据表，它显示了某购物网站在每个城市每个区的销售额：
CREATE TABLE sales(id INT PRIMARY KEY AUTO_INCREMENT,city VARCHAR(15),county VARCHAR(15),sales_value DECIMAL);INSERT INTO sales(city,county,sales_value)VALUES('北京','海淀',10.00),('北京','朝阳',20.00),('上海','黄埔',30.00),('上海','长宁',10.00);
查询：
mysql> SELECT * FROM sales;+----+------+--------+-------------+| id | city | county | sales_value |+----+------+--------+-------------+| 1 | 北京 | 海淀 | 10 || 2 | 北京 | 朝阳 | 20 || 3 | 上海 | 黄埔 | 30 || 4 | 上海 | 长宁 | 10 |+----+------+--------+-------------+4 rows in set (0.00 sec)
需求：现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率。
如果用分组和聚合函数，就需要分好几步来计算。
第一步，计算总销售金额，并存入临时表 a：
CREATE TEMPORARY TABLE a -- 创建临时表SELECT SUM(sales_value) AS sales_value -- 计算总计金额FROM sales;
查看一下临时表 a ：
mysql> SELECT * FROM a;+-------------+| sales_value |+-------------+| 70 |+-------------+1 row in set (0.00 sec)
第二步，计算每个城市的销售总额并存入临时表 b：
CREATE TEMPORARY TABLE b -- 创建临时表SELECT city,SUM(sales_value) AS sales_value -- 计算城市销售合计FROM salesGROUP BY city;
查看临时表 b ：
mysql> SELECT * FROM b;+------+-------------+| city | sales_value |+------+-------------+| 北京 | 30 || 上海 | 40 |+------+-------------+2 rows in set (0.00 sec)
第三步，计算各区的销售占所在城市的总计金额的比例，和占全部销售总计金额的比例。我们可以通过下面的连接查询获得需要的结果：
mysql> SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额,-> b.sales_value AS 市销售额,s.sales_value/b.sales_value AS 市比率,-> a.sales_value AS 总销售额,s.sales_value/a.sales_value AS 总比率-> FROM sales s-> JOIN b ON (s.city=b.city) -- 连接市统计结果临时表-> JOIN a -- 连接总计金额临时表-> ORDER BY s.city,s.county;+------+------+----------+----------+--------+----------+--------+| 城市 | 区 | 区销售额 | 市销售额 | 市比率 | 总销售额 | 总比率 |+------+------+----------+----------+--------+----------+--------+| 上海 | 长宁 | 10 | 40 | 0.2500 | 70 | 0.1429 || 上海 | 黄埔 | 30 | 40 | 0.7500 | 70 | 0.4286 || 北京 | 朝阳 | 20 | 30 | 0.6667 | 70 | 0.2857 || 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |+------+------+----------+----------+--------+----------+--------+4 rows in set (0.00 sec)
结果显示：市销售金额、市销售占比、总销售金额、总销售占比都计算出来了。
同样的查询，如果用窗口函数，就简单多了。我们可以用下面的代码来实现：
mysql> SELECT city AS 城市,county AS 区,sales_value AS 区销售额,-> SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 计算市销售额-> sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,-> SUM(sales_value) OVER() AS 总销售额, -- 计算总销售额-> sales_value/SUM(sales_value) OVER() AS 总比率-> FROM sales-> ORDER BY city,county;+------+------+----------+----------+--------+----------+--------+| 城市 | 区 | 区销售额 | 市销售额 | 市比率 | 总销售额 | 总比率 |+------+------+----------+----------+--------+----------+--------+| 上海 | 长宁 | 10 | 40 | 0.2500 | 70 | 0.1429 || 上海 | 黄埔 | 30 | 40 | 0.7500 | 70 | 0.4286 || 北京 | 朝阳 | 20 | 30 | 0.6667 | 70 | 0.2857 || 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |+------+------+----------+-----------+--------+----------+--------+4 rows in set (0.00 sec)
结果显示，我们得到了与上面那种查询同样的结果。
使用窗口函数，只用了一步就完成了查询。而且，由于没有用到临时表，执行的效率也更高了。很显然，在这种需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。
2.2 窗口函数分类
MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。
窗口函数可以分为静态窗口函数和动态窗口函数。
- 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
- 动态窗口函数的窗口大小会随着记录的不同而变化。
MySQL官方网站窗口函数的网址为https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表：
2.3 语法结构
窗口函数的语法结构是：
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
或者是：
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
- OVER 关键字指定函数窗口的范围。
- 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
- 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
- 窗口名：为窗口设置一个别名，用来标识窗口。
- PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
- ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
- FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
2.4 分类讲解
创建表：
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');
下面针对goods表中的数据来验证每个窗口函数的功能。
1. 序号函数
1．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;+---------+----+-------------+---------------+------------+---------+-------+| 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种商品信息。
mysql> 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_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 || 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 || 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 || 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |+---------+----+-------------+---------------+------------+----------+-------+6 rows in set (0.00 sec)
在名称为“女装/女士精品”的商品类别中，有两款商品的价格为89.90元，分别是卫衣和牛仔裤。两款商品的序号都应该为2，而不是一个为2，另一个为3。此时，可以使用RANK()函数和DENSE_RANK()函数解决。
2．RANK()函数
使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。
举例：使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
mysql> SELECT RANK() 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 || 2 | 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 || 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 || 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |+---------+----+-------------+---------------+------------+---------+-------+12 rows in set (0.00 sec)
举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
mysql> 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;+---------+----+-------------+---------------+----------+--------+-------+| row_num | id | category_id | category | NAME | price | stock |+---------+----+-------------+---------------+----------+--------+-------+| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 || 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 || 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 || 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |+---------+----+-------------+---------------+----------+--------+-------+4 rows in set (0.00 sec)
可以看到，使用RANK()函数得出的序号为1、2、2、4，相同价格的商品序号相同，后面的商品序号是不连续的，跳过了重复的序号。
3．DENSE_RANK()函数
DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。
举例：使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
mysql> SELECT DENSE_RANK() 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 || 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 || 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 || 4 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 || 5 | 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 || 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 || 5 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |+---------+----+-------------+---------------+------------+---------+-------+12 rows in set (0.00 sec)
举例：使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
mysql> 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;+---------+----+-------------+---------------+----------+--------+-------+| row_num | id | category_id | category | NAME | price | stock |+---------+----+-------------+---------------+----------+--------+-------+| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 || 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 || 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 || 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |+---------+----+-------------+---------------+----------+--------+-------+4 rows in set (0.00 sec)
可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。
2. 分布函数
1．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, stockFROM goodsWHERE 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);+---+-----+----+-------------+---------------+----------+--------+-------+| r | pr | id | category_id | category | NAME | price | stock |+---+-----+----+-------------+---------------+----------+--------+-------+| 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 || 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 || 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 || 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 || 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 || 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |+---+-----+----+-------------+---------------+----------+--------+-------+6 rows in set (0.00 sec)
2．CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
举例：查询goods数据表中小于或等于当前价格的比例。
mysql> SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,-> id, category, NAME, price-> FROM goods;+---------------------+----+---------------+------------+---------+| cd | id | category | NAME | price |+---------------------+----+---------------+------------+---------+| 0.16666666666666666 | 5 | 女装/女士精品 | 百褶裙 | 29.90 || 0.3333333333333333 | 1 | 女装/女士精品 | T恤 | 39.90 || 0.5 | 2 | 女装/女士精品 | 连衣裙 | 79.90 || 0.8333333333333334 | 3 | 女装/女士精品 | 卫衣 | 89.90 || 0.8333333333333334 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 || 1 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 || 0.16666666666666666 | 9 | 户外运动 | 登山杖 | 59.90 || 0.5 | 7 | 户外运动 | 自行车 | 399.90 || 0.5 | 10 | 户外运动 | 骑行装备 | 399.90 || 0.6666666666666666 | 12 | 户外运动 | 滑板 | 499.90 || 0.8333333333333334 | 11 | 户外运动 | 运动外套 | 799.90 || 1 | 8 | 户外运动 | 山地自行车 | 1399.90 |+---------------------+----+---------------+------------+---------+12 rows in set (0.00 sec)
3. 前后函数
1．LAG(expr,n)函数
LAG(expr,n)函数返回当前行的前n行的expr的值。
举例：查询goods数据表中前一个商品价格与当前商品价格的差值。
mysql> SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price-> FROM (-> SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price-> FROM goods-> WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;+----+---------------+------------+---------+-----------+------------+| id | category | NAME | price | pre_price | diff_price |+----+---------------+------------+---------+-----------+------------+| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL || 1 | 女装/女士精品 | T恤 | 39.90 | 29.90 | 10.00 || 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 40.00 || 3 | 女装/女士精品 | 卫衣 | 89.90 | 79.90 | 10.00 || 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 89.90 | 0.00 || 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 89.90 | 310.00 || 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL || 7 | 户外运动 | 自行车 | 399.90 | 59.90 | 340.00 || 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 0.00 || 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 100.00 || 11 | 户外运动 | 运动外套 | 799.90 | 499.90 | 300.00 || 8 | 户外运动 | 山地自行车 | 1399.90 | 799.90 | 600.00 |+----+---------------+------------+---------+-----------+------------+12 rows in set (0.00 sec)
2．LEAD(expr,n)函数
LEAD(expr,n)函数返回当前行的后n行的expr的值。
举例：查询goods数据表中后一个商品价格与当前商品价格的差值。
mysql> SELECT id, category, NAME, behind_price, price,behind_price - price AS diff_price-> FROM(-> SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;+----+---------------+------------+--------------+---------+------------+| id | category | NAME | behind_price | price | diff_price |+----+---------------+------------+--------------+---------+------------+| 5 | 女装/女士精品 | 百褶裙 | 39.90 | 29.90 | 10.00 || 1 | 女装/女士精品 | T恤 | 79.90 | 39.90 | 40.00 || 2 | 女装/女士精品 | 连衣裙 | 89.90 | 79.90 | 10.00 || 3 | 女装/女士精品 | 卫衣 | 89.90 | 89.90 | 0.00 || 4 | 女装/女士精品 | 牛仔裤 | 399.90 | 89.90 | 310.00 || 6 | 女装/女士精品 | 呢绒外套 | NULL | 399.90 | NULL || 9 | 户外运动 | 登山杖 | 399.90 | 59.90 | 340.00 || 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 0.00 || 10 | 户外运动 | 骑行装备 | 499.90 | 399.90 | 100.00 || 12 | 户外运动 | 滑板 | 799.90 | 499.90 | 300.00 || 11 | 户外运动 | 运动外套 | 1399.90 | 799.90 | 600.00 || 8 | 户外运动 | 山地自行车 | NULL | 1399.90 | NULL |+----+---------------+------------+--------------+---------+------------+12 rows in set (0.00 sec)
4. 首尾函数
1．FIRST_VALUE(expr)函数
FIRST_VALUE(expr)函数返回第一个expr的值。
举例：按照价格排序，查询第1个商品的价格信息。
mysql> SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_price-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);+----+---------------+------------+---------+-------+-------------+| id | category | NAME | price | stock | first_price |+----+---------------+------------+---------+-------+-------------+| 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 || 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 29.90 || 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 29.90 || 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 29.90 || 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 29.90 || 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 29.90 || 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 || 7 | 户外运动 | 自行车 | 399.90 | 1000 | 59.90 || 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 59.90 || 12 | 户外运动 | 滑板 | 499.90 | 1200 | 59.90 || 11 | 户外运动 | 运动外套 | 799.90 | 500 | 59.90 || 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 59.90 |+----+---------------+------------+---------+-------+-------------+12 rows in set (0.00 sec)
2．LAST_VALUE(expr)函数
LAST_VALUE(expr)函数返回最后一个expr的值。
举例：按照价格排序，查询最后一个商品的价格信息。
mysql> SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);+----+---------------+------------+---------+-------+------------+| id | category | NAME | price | stock | last_price |+----+---------------+------------+---------+-------+------------+| 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 || 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 39.90 || 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 79.90 || 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 89.90 || 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 89.90 || 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 399.90 || 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 || 7 | 户外运动 | 自行车 | 399.90 | 1000 | 399.90 || 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 399.90 || 12 | 户外运动 | 滑板 | 499.90 | 1200 | 499.90 || 11 | 户外运动 | 运动外套 | 799.90 | 500 | 799.90 || 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 1399.90 |+----+---------------+------------+---------+-------+------------+12 rows in set (0.00 sec)
5. 其他函数
1．NTH_VALUE(expr,n)函数
NTH_VALUE(expr,n)函数返回第n个expr的值。
举例：查询goods数据表中排名第2和第3的价格信息。
mysql> SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,-> NTH_VALUE(price,3) OVER w AS third_price-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);+----+---------------+------------+---------+--------------+-------------+| id | category | NAME | price | second_price | third_price |+----+---------------+------------+---------+--------------+-------------+| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL || 1 | 女装/女士精品 | T恤 | 39.90 | 39.90 | NULL || 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 79.90 || 3 | 女装/女士精品 | 卫衣 | 89.90 | 39.90 | 79.90 || 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 39.90 | 79.90 || 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 39.90 | 79.90 || 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL || 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 399.90 || 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 399.90 || 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 399.90 || 11 | 户外运动 | 运动外套 | 799.90 | 399.90 | 399.90 || 8 | 户外运动 | 山地自行车 | 1399.90 | 399.90 | 399.90 |+----+---------------+------------+---------+--------------+-------------+12 rows in set (0.00 sec)
2．NTILE(n)函数
NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。
举例：将goods表中的商品按照价格分为3组。
mysql> SELECT NTILE(3) OVER w AS nt,id, category, NAME, price-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);+----+----+---------------+------------+---------+| nt | id | category | NAME | price |+----+----+---------------+------------+---------+| 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 || 1 | 1 | 女装/女士精品 | T恤 | 39.90 || 2 | 2 | 女装/女士精品 | 连衣裙 | 79.90 || 2 | 3 | 女装/女士精品 | 卫衣 | 89.90 || 3 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 || 3 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 || 1 | 9 | 户外运动 | 登山杖 | 59.90 || 1 | 7 | 户外运动 | 自行车 | 399.90 || 2 | 10 | 户外运动 | 骑行装备 | 399.90 || 2 | 12 | 户外运动 | 滑板 | 499.90 || 3 | 11 | 户外运动 | 运动外套 | 799.90 || 3 | 8 | 户外运动 | 山地自行车 | 1399.90 |+----+----+---------------+------------+---------+12 rows in set (0.00 sec)
2.5 小 结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。
若有收获，就点个赞吧
0 人点赞
