MySQL8.0新特性之窗口函数学习

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL8.0新特性之窗口函数学习

0cf4e5cf88e54831b55937d4b89946ab.pngMySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而窗口函数是将结果置于每一条数据记录中。


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


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

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

MySQL官方网站窗口函数的网址为https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number


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


需求:现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售

额中的比率,以及占总销售额中的比率。

如果用分组和聚合函数,就需要分好几步来计算。


第一步,计算总销售金额,并存入临时表 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 sales
group 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】窗口函数的分类和语法结构

① 函数分类

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数

函数分类 函数 函数说明
序号函数 ROW_NUMBER() 顺序排序
序号函数 RANK() 并列排序,会跳过重复的序号,比如序号为1、1、3
序号函数 DENSE_RANK() 并列排序,不会跳过重复的序号,比如序号为1、1、2
分布函数 PERCENT_RANK() 等级值百分比
分布函数 CUME_DIST() 累积分布值
前后函数 LAG(expr,n) 返回当前行的前n行的expr的值
前后函数 LEAD(expr,n) 返回当前行的后n行的expr的值
首尾函数 FIRST_VALUE(expr) 返回第一个expr的值
首尾函数 LAST_VALUE(expr) 返回最后一个expr的值
其他函数 NTH_VALUE(expr,n) 返回第n个expr的值
其他函数 NTILE(n) 将分区中的有序数据分为n个桶,记录桶编号


② 语法结构

窗口函数的语法结构是:

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

【3】窗口函数实践

创建表:

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.ROW_NUMBER()函数


ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

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


c6892ddc6d2d479e88f61f1241a7ff5b.png


举例:查询 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 ) as t
 where row_num<=3



0cf4e5cf88e54831b55937d4b89946ab.png


在名称为“女装/女士精品”的商品类别中,有两款商品的价格为89.90元,分别是卫衣和牛仔裤。两款商品的序号都应该为2,而不是一个为2,另一个为3。此时,可以使用RANK()函数和DENSE_RANK()函数解决。


2.RANK()函数

使用RANK()函数能够对序号进行并列排序,并且会跳过重复的序号,比如序号为1、1、3。

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


079739733f774865855908e936e00140.png


举例:使用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 ) as t
 where category_id=1 and  row_num<=4


bc7063ee7f3042eeaf10a1e64164d462.png

可以看到,使用RANK()函数得出的序号为1、2、2、4,相同价格的商品序号相同,后面的商品序号是不连续的,跳过了重复的序号。

3.DENSE_RANK()函数

DENSE_RANK()函数对序号进行并列排序,并且不会跳过重复的序号,比如序号为1、1、2。

举例:使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。

b391b0abcc5640769484811dfa9d8573.png
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;

举例:使用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 ) as t
 where category_id=1 and  row_num<=3

a9ae212d72474fca8dd2eecead60d9c3.png


可以看到,使用DENSE_RANK()函数得出的行号为1、2、2、3,相同价格的商品序号相同,后面的商品序号是连续的,并且没有跳过重复的序号。

② 分布函数

1.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, 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);



43c8a6d0a21448f38f066c4ba22e1b77.png


2.CUME_DIST()函数

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

举例:查询goods数据表中小于或等于当前价格的比例。

SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
 id, category, NAME, price
 FROM goods;

8c1950394dc54641aa2bbb61589049aa.png

③ 前后函数

1.LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值。


举例:查询goods数据表中前一个商品价格与当前商品价格的差值。

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;

3c22739310b747e789bce96b15b3bac5.png


2.LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值。

举例:查询goods数据表中后一个商品价格与当前商品价格的差值。

43ebaf0a823246c28f07afc60db02126.png
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;


④ 首尾函数

1.FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值。

举例:按照价格排序,查询第1个商品的价格信息。

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



05d04510035b401ca72069b80b852a4b.png


2.LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。

举例:按照价格排序,查询最后一个商品的价格信息。

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




68e5d5ee02c9483abfe9718efb77c71e.png


可能价格举例看不出效果,我们可以这样按照价格排序,查询最后一个商品的库存信息

SELECT id, category, NAME, price, stock,LAST_VALUE(stock) OVER w AS last_stock
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

cf5b7b219c4e4bb38096d55c21284d44.png

⑤ 其他函数

1.NTH_VALUE(expr,n)函数

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

举例:查询goods数据表中排名第2和第3的价格信息。

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


c806d4e4d21a48db89365da8d47659ea.png


2.NTILE(n)函数

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

举例:将goods表中的商品按照价格分为3组。

SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods 
WINDOW w AS (PARTITION BY category_id ORDER BY price);

a0aa357bd1174ff89866617e8d6a88d0.png



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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
MySQL8.0索引新特性
MySQL8.0索引新特性
20 0
|
30天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
1天前
|
SQL 关系型数据库 MySQL
MySQL单表操作学习DDL_DML_DQL语句
MySQL单表操作学习DDL_DML_DQL语句
7 0
|
1天前
|
SQL 关系型数据库 MySQL
MySQL学习必备SQL_DDL_DML_DQL
MySQL学习必备SQL_DDL_DML_DQL
2 0
|
2天前
|
SQL JSON 关系型数据库
MySQL 8.0新特性?
【6月更文挑战第14天】MySQL 8.0新特性?
13 1
|
2天前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】2.6-InnoDB关键特性
【MySQL技术内幕】2.6-InnoDB关键特性
8 0
|
3天前
|
关系型数据库 MySQL
MYSQL 窗口函数
MYSQL 窗口函数
|
7天前
|
关系型数据库 MySQL 数据库
MySQL 8.0 新特性之不可见主键
【6月更文挑战第9天】MySQL 8.0 引入了不可见主键特性,提供更灵活的数据库管理方式。不可见主键能减少业务逻辑干扰,提高数据安全性和隐私,同时在某些场景下更适用。示例展示了如何创建和使用不可见主键,但需要注意它可能带来的理解和调试难题。此特性增加了设计和管理数据库的选项,适用于对数据隐私有高要求的场景。随着技术发展,不断学习和探索新特性将提升数据库性能和功能。
32 9
|
9天前
|
SQL 安全 关系型数据库
MySQL数据库——事务-简介、事务操作、四大特性、并发事务问题、事务隔离级别
MySQL数据库——事务-简介、事务操作、四大特性、并发事务问题、事务隔离级别
14 1
|
12天前
|
关系型数据库 MySQL 数据挖掘
MySQL 8窗口函数详解:高效数据处理的必备技能
MySQL 8窗口函数详解:高效数据处理的必备技能
12 3