Mysql基础篇（11）—— MySQL8.0新特性之窗口函数

举例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)

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

在mysql8之前我们使用分组聚合函数来实现

• 第一步计算总销售金额存入临时表a

CREATE TEMPORARY TABLE a -- 创建临时表
SELECT SUM(sales_value) AS sales_value -- 计算总计金额
FROM sales;

• 第二步计算每个城市的销售总额并存入临时表b

CREATE TEMPORARY TABLE b -- 创建临时表
SELECT city,SUM(sales_value) AS sales_value -- 计算城市销售合计
FROM sales
GROUP BY city

• 第三步计算所有结果

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;

这个实现虽然逻辑思路很清晰但是步骤太繁琐了。

同样的查询如果使用窗口函数就简单许多我们可以用下面代码来实现

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;

个人对窗口函数的理解就是OVER后面的就是可以临时地对数据进行分组,且支持对分组里面每条数据进行处理,这里临时的分组就是窗口

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

• 静态窗口函数的窗口大小是固定的不会因为记录的不同而不同
• 动态窗口函数的窗口大小会随着记录的不同而变化

MySQL官方网站窗口函数的网址

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

除了大部分的聚合函数其他窗口函数还有

函数	函数说明
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关键字指定函数窗口的范围
• 窗口名为窗口设置一个别名用来标识窗口。
• PARTITION BY 子句指定窗口函数按照哪些字段进行分组。分组后窗口函数可以在每个分组中分别执行。
• ORDER BY 子句执行窗口函数按照哪些字段进行排序。执行排序操作时窗口函数按照排序后的数据记录的顺序进行编号。

举例2

创建表

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

ROW_NUMBER()

顺序排序序号不可能会出现重复。

比如展示每个商品在对应分类下的排序序号。

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

结果

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;

结果

DENSE_RANK()

并列排序不会跳过重复的序号。

举例同上。

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

结果

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
HERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

结果

CUME_DIST()

主要用于查询小于或等于某个值的比例。

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

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

结果

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;

结果

LEAD(expr, n)

返回当前行的后n行的expr的值。和LAG用法一样。

FIRST_VALUE(expr)

返回第一个expr的值。

比如返回每个种类价格最低的商品价格

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

结果

LAST_VALUE(expr)

返回最后一个expr的值用法同FIRST_VALUE。

NTH_VALUE(expr, n)

返回第n个expr的值n为1的时候和FIRST_VALUE()效果一样。

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

结果