MySQL8新特性-窗口函数

MySQL8开始支持窗口函数。在之前的版本中已存在的大部分集合函数在MySQL8中也可以作为窗口函数使用。

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.03 sec)

现有需求：计算这个网站在每个城市的销售总额、在全国的销售总额，每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率。

实现方式一：如果我们使用分组和聚合函数来做，需要分几步来做

第一步：创建临时表a，记录全国的销售总额

​mysql> CREATE TEMPORARY TABLE a
    -> SELECT SUM(sales_value) AS sales_value
    -> FROM sales;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM a;
+-------------+
| sales_value |
+-------------+
|          70 |
+-------------+
1 row in set (0.00 sec)

第二步：创建临时表b，记录每个城市的销售总额

mysql> CREATE TEMPORARY TABLE b
    -> SELECT city,SUM(sales_value) AS sales_value
    -> FROM sales
    -> GROUP BY city;
Query OK, 2 rows affected (0.03 sec)
mysql> SELECT * FROM b;
+------+-------------+
| city | sales_value |
+------+-------------+
| 北京 |          30 |
| 上海 |          40 |
+------+-------------+
2 rows in set (0.00 sec)

第三步：多表连接

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 |
+------+------+----------+----------+--------+----------+--------+

实现方式二：使用窗口函数

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、窗口函数的分类

窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

窗口函数可以分为静态窗口函数和动态窗口函数

• 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同

• 动态窗口函数的窗口大小会随着记录的不同而变化。

窗口函数总体上可以分为序号函数、分布函数、前后函数、收尾函数和其他函数，如下图：

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 字句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

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

4.1序号函数

• row_number()

  需求一：查询goods表中每个商品分类下价格降序排列的各个商品信息

  SELECT 
      *,
      row_number() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
  FROM goods; 

  显示结果：可以看到，当价格相同时，排序不会重复

  

  需求二：查询goods表中每个商品分类下价格最高的3种商品信息

  其实就是将上一个需求做子查询，再用where字句筛选

  SELECT *
  FROM (
      SELECT 
          *,
          row_number() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
      FROM goods
  ) t
  WHERE row_num<=3;

  结果如下：

  

• rank()

  需求：使用rank()函数获取goods表中各类别的价格从高到低排序的各商品信息

  SELECT 
      *,
      rank() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
  FROM goods;

  结果如下：价格相同时，排名会重复，排名会有缺失 1、2、2、4

  

• dense_rank()

  需求：使用dense_rank()函数获取goods表中个类别的价格从高到低排序的各商品信息

  SELECT 
      *,
      dense_rank() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
  FROM goods;

  结果如下：价格相同时，排名会重复，排名不会缺失1、2、2、3

  

4.2分布函数

• 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
  FROM goods
  WHERE category_id=1;

  写法二：

  SELECT
      rank() over w AS r,
      percent_rank() over w AS pr,
      id,category_id,category,NAME,price
  FROM goods
  WHERE category_id=1
  window w AS (PARTITION BY category_id ORDER BY price DESC);

  结果如下：

  

• CUME_DIST()函数

  CUME_DIST()函数主要用于查询小于或等于某个值的比例。

  需求：查询goods表中小于或等于当前价格的比例。

  SELECT 
      cume_dist() over(PARTITION BY category_id ORDER BY price ASC) AS cd,
      category_id,category,NAME,price
  FROM goods;

  结果如下：

  

4.3前后函数

• LAG(expr,n)函数

  LAG(expr,n)函数返回当前行的前n行的expr的值，如果当前行前面没有行，则返回NULL

  需求：查询goods数据表中前一个商品价格与当前商品价格的差值。

  SELECT id,category,NAME,price,price_pr,price-price_pr AS 差值
  FROM
  (SELECT
  id,category,NAME,price,LAG(price,1) over(PARTITION BY category_id ORDER BY price) AS price_pr
  FROM goods) t;

  结果如下：

  

• LEAD(expr,n)函数

  LEAD(expr,n)函数返回当前行的后n行的expr的值，如果当前行后面没有行，则返回NULL

  需求：查询goods数据表中后一个商品价格与当前商品价格的差值

  SELECT id,category,NAME,price,price_behind,price-price_behind AS 差值
  FROM
  (SELECT
  id,category,NAME,price,LEAD(price,1) over(PARTITION BY category_id ORDER BY price DESC) AS price_behind
  FROM goods) t;

  结果如下：

  

4.4 首尾函数

• FIRST_VALUE(expr)函数

• LAST_VALUE(expr)函数

这里以FIRST_VALUE(expr)举例

需求：获取goods表中各类别的价格从高到低排序的各商品信息，查询每个类别的第一个商品价格

​SELECT
*,
first_value(price) over(PARTITION BY category_id ORDER BY price DESC) AS price_hight
FROM goods;

结果如下：

4.5其他函数

• NTH_VALUE(expr,n)函数

  NTH_VALUE(expr,n)函数返回第n个expr的值

  需求：查询goods表中，价格排名第2和第3的商品的价格信息

  SELECT id,category,NAME,price,
  nth_value(price,2) over(PARTITION BY category_id ORDER BY price) AS second_price,
  nth_value(price,3) over(PARTITION BY category_id ORDER BY price) AS third_price
  FROM goods;;

  结果如下：

  

• NTILE(n)函数

  NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

  需求：将goods表中的商品按照价格分为3组

  SELECT
  NTILE(3) over(PARTITION BY category_id ORDER BY price) AS nt,
  id,category,NAME,price,stock,upper_time
  FROM goods;

  结果如下：

  

4.6小结

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原数据表的基础上进行统计和排序非常有用。