MySQL高级讲座篇之：MySQL 8.0的`Window Functions`的应用，实现无`JOIN`的复杂数据分析。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众，大家好！我是今天的主讲人，外号“SQL老司机”。今天咱们不聊那些虚头巴脑的理论，直接上干货，聊聊MySQL 8.0里让人眼前一亮的Window Functions（窗口函数）。这玩意儿啊，就像给你的SQL加了个“透视镜”，让你不用写一大堆让人头疼的JOIN，也能轻松搞定各种复杂的数据分析。

一、窗口函数是啥？别慌，我来给你“解剖”一下

简单来说，窗口函数就是在指定的数据“窗口”上进行计算的函数。这个“窗口”可不是你家窗户，而是由OVER()子句定义的，它决定了函数计算的数据范围。

传统的聚合函数（比如SUM(), AVG(), COUNT()）会把多行数据聚合成一行，但是窗口函数不会，它会为每一行都返回一个值，这个值是基于当前行所在的窗口计算出来的。听起来有点绕？别急，咱们用例子说话。

二、窗口函数的基本语法：OVER()子句

OVER()子句是窗口函数的核心，它定义了窗口的规则。最简单的OVER()子句就是OVER()，表示整个结果集都是窗口。当然，这没什么意义，咱们一般会配合以下参数使用：

PARTITION BY: 把数据分成多个“分区”，每个分区都是一个独立的窗口。
ORDER BY: 在每个分区内，按照指定的列进行排序。
ROWS/RANGE BETWEEN: 定义窗口的范围，也就是当前行的前N行到后M行。

三、窗口函数家族大阅兵：常用函数介绍

MySQL 8.0提供了丰富的窗口函数，咱们挑几个最常用的来重点讲讲：

聚合函数 (Aggregates as Window Functions): SUM(), AVG(), COUNT(), MIN(), MAX()

这些函数摇身一变，也能当窗口函数用。它们在窗口内进行聚合计算，并为每一行返回聚合结果。

例子：计算每个部门的工资总额，并显示每个员工的工资和部门总工资。

假设我们有张员工表employees：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, department, salary) VALUES
(1, '销售部', 5000.00),
(2, '销售部', 6000.00),
(3, '技术部', 8000.00),
(4, '技术部', 9000.00),
(5, '行政部', 4000.00);

使用窗口函数：

SELECT
    id,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS department_total_salary
FROM
    employees;

结果：

id	department	salary	department_total_salary
5	行政部	4000.00	4000.00
3	技术部	8000.00	17000.00
4	技术部	9000.00	17000.00
1	销售部	5000.00	11000.00
2	销售部	6000.00	11000.00

解释：SUM(salary) OVER (PARTITION BY department)的意思是，按照department分组，然后在每个组内计算salary的总和。注意，每一行都显示了该行员工的工资以及其所在部门的工资总额。

排序函数 (Ranking Functions): RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

这些函数可以为每一行分配一个排名，基于指定的排序规则。

RANK(): 排名，如果有并列，会跳过后面的排名。
DENSE_RANK(): 排名，如果有并列，不会跳过后面的排名。
ROW_NUMBER(): 为每一行分配一个唯一的序号，即使有并列。
NTILE(n): 将结果集分成n组，并为每一行分配组号。

例子：按照工资高低给员工排名。

SELECT
    id,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_row_number
FROM
    employees;

结果：

id	department	salary	salary_rank	salary_dense_rank	salary_row_number
4	技术部	9000.00	1	1	1
3	技术部	8000.00	2	2	2
2	销售部	6000.00	3	3	3
1	销售部	5000.00	4	4	4
5	行政部	4000.00	5	5	5

解释：

RANK() OVER (ORDER BY salary DESC)：按照工资降序排列，并计算排名。
DENSE_RANK() OVER (ORDER BY salary DESC)：按照工资降序排列，并计算紧密排名（没有排名跳跃）。
ROW_NUMBER() OVER (ORDER BY salary DESC)：按照工资降序排列，并分配唯一的行号。

例子：将员工按照工资分成3组。

SELECT
    id,
    department,
    salary,
    NTILE(3) OVER (ORDER BY salary DESC) AS salary_group
FROM
    employees;

结果：

id	department	salary	salary_group
4	技术部	9000.00	1
3	技术部	8000.00	1
2	销售部	6000.00	2
1	销售部	5000.00	2
5	行政部	4000.00	3

解释：NTILE(3) OVER (ORDER BY salary DESC)：按照工资降序排列，并将员工分成3组。

值函数 (Value Functions): LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

这些函数可以访问窗口中其他行的值。

LAG(column, n, default): 返回当前行之前第n行的column的值。如果没有前n行，则返回default。
LEAD(column, n, default): 返回当前行之后第n行的column的值。如果没有后n行，则返回default。
FIRST_VALUE(column): 返回窗口中第一行的column的值。
LAST_VALUE(column): 返回窗口中最后一行的column的值。默认情况下，LAST_VALUE会受到窗口帧的限制，需要显式指定帧的范围才能正确使用。
NTH_VALUE(column, n): 返回窗口中第n行的column的值。

例子：计算每个员工的工资与前一个员工的工资差。

SELECT
    id,
    department,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY id) AS prev_salary,
    salary - LAG(salary, 1, 0) OVER (ORDER BY id) AS salary_diff
FROM
    employees;

结果：

id	department	salary	prev_salary	salary_diff
1	销售部	5000.00	0.00	5000.00
2	销售部	6000.00	5000.00	1000.00
3	技术部	8000.00	6000.00	2000.00
4	技术部	9000.00	8000.00	1000.00
5	行政部	4000.00	9000.00	-5000.00

解释：LAG(salary, 1, 0) OVER (ORDER BY id)：按照id排序，返回当前行之前一行的salary值。如果当前行是第一行，则返回0。

例子：获取每个部门工资最高的员工的工资。

SELECT
    id,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary
FROM
    employees;

结果：

id	department	salary	highest_salary
5	行政部	4000.00	4000.00
4	技术部	9000.00	9000.00
3	技术部	8000.00	9000.00
2	销售部	6000.00	6000.00
1	销售部	5000.00	6000.00

解释：FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC)：按照department分组，然后在每个组内按照salary降序排列，并返回每个组的第一行的salary值，也就是工资最高的员工的工资。

四、窗口帧 (Window Framing)：精细控制窗口范围

窗口帧允许你更精确地定义窗口的范围。它使用ROWS或RANGE子句来指定窗口的起始和结束位置。

ROWS BETWEEN: 基于行数定义窗口范围。
RANGE BETWEEN: 基于排序后的值定义窗口范围。

常用的窗口帧选项：

UNBOUNDED PRECEDING: 窗口从分区的第一行开始。
n PRECEDING: 窗口从当前行之前的n行开始。
CURRENT ROW: 窗口从当前行开始或结束。
n FOLLOWING: 窗口从当前行之后的n行结束。
UNBOUNDED FOLLOWING: 窗口到分区的最后一行结束。

例子：计算每个员工及其前后一个员工的工资总和（滑动平均）。

SELECT
    id,
    department,
    salary,
    SUM(salary) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average
FROM
    employees;

结果：

id	department	salary	moving_average
1	销售部	5000.00	11000.00
2	销售部	6000.00	19000.00
3	技术部	8000.00	23000.00
4	技术部	9000.00	21000.00
5	行政部	4000.00	13000.00

解释：SUM(salary) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)：按照id排序，然后计算当前行及其前一行和后一行的salary总和。

五、窗口函数 VS. GROUP BY：谁更胜一筹？

GROUP BY也能进行聚合计算，那窗口函数和它有什么区别呢？

特性	`GROUP BY`	窗口函数
返回结果	将多行聚合成一行	为每一行都返回一个值
数据粒度	改变结果集的粒度	不改变结果集的粒度
适用场景	需要对数据进行分组聚合时	需要在不改变数据粒度的前提下进行分析时
是否需要`JOIN`	某些复杂场景可能需要`JOIN`	通常可以避免`JOIN`

简单来说，GROUP BY是“瘦身”，窗口函数是“透视”。如果你只想知道每个组的汇总结果，用GROUP BY。如果你想在每一行数据旁边都显示一些聚合信息，用窗口函数。

六、窗口函数的应用场景：让你的数据分析更上一层楼

窗口函数在数据分析领域应用广泛，可以解决各种复杂的问题。比如：

计算累计总和、移动平均: 统计每日销售额的累计总和，或者计算最近7天的销售额平均值。
计算排名和百分比: 对销售人员进行业绩排名，或者计算每个客户的消费金额占总消费金额的百分比。
查找最大值、最小值: 找出每个部门工资最高的员工，或者找出每个月销售额最低的产品。
计算同比、环比: 计算今年销售额与去年同期的增长率，或者计算本月销售额与上个月的增长率。
复杂报表统计: 灵活生成各种复杂的报表，例如帕累托分析、ABC分析等。

七、窗口函数的性能优化：别让它成为你的“猪队友”

窗口函数虽然强大，但如果使用不当，也会影响查询性能。以下是一些优化建议：

尽量使用索引: PARTITION BY和ORDER BY子句中使用的列，最好都有索引。
避免在窗口函数中使用复杂的表达式: 复杂的表达式会增加计算成本。
合理选择窗口帧: 窗口帧的范围越小，计算量越小。
MySQL版本要给力: 升级到MySQL 8.0的最新版本，享受性能优化。
具体情况具体分析: 针对具体的查询，进行性能测试和优化。

八、案例分析：不用JOIN也能飞

假设我们有两张表：orders (订单表) 和 customers (客户表)。

orders表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(1, 101, '2023-01-01', 100.00),
(2, 101, '2023-01-05', 150.00),
(3, 102, '2023-01-10', 200.00),
(4, 101, '2023-02-01', 120.00),
(5, 102, '2023-02-15', 180.00),
(6, 103, '2023-03-01', 250.00);

customers表：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

INSERT INTO customers (customer_id, customer_name) VALUES
(101, '张三'),
(102, '李四'),
(103, '王五');

需求：查询每个客户的订单总金额，以及该客户的订单总金额占所有客户订单总金额的百分比。

如果不用窗口函数，我们需要先用GROUP BY计算每个客户的订单总金额，然后再JOIN到总金额表，才能计算百分比。代码如下：

SELECT
    c.customer_name,
    customer_total.total_amount,
    customer_total.total_amount / (SELECT SUM(amount) FROM orders) * 100 AS percentage
FROM
    customers c
JOIN
    (SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id) AS customer_total
ON
    c.customer_id = customer_total.customer_id;

是不是感觉有点绕？现在，让我们用窗口函数来简化这个查询：

SELECT
    c.customer_name,
    SUM(o.amount) OVER (PARTITION BY c.customer_id) AS total_amount,
    SUM(o.amount) OVER (PARTITION BY c.customer_id) / SUM(o.amount) OVER () * 100 AS percentage
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

结果：

customer_name	total_amount	percentage
张三	370.00	35.57692307692308
李四	380.00	36.53846153846153
王五	250.00	24.03846153846154

解释：

SUM(o.amount) OVER (PARTITION BY c.customer_id)：按照customer_id分组，计算每个客户的订单总金额。
SUM(o.amount) OVER ()：计算所有订单的总金额。
SUM(o.amount) OVER (PARTITION BY c.customer_id) / SUM(o.amount) OVER () * 100：计算每个客户的订单总金额占所有订单总金额的百分比。

这个查询避免了复杂的JOIN操作，代码更简洁，可读性也更高。

九、总结：窗口函数，你的SQL超能力

今天我们一起探索了MySQL 8.0的窗口函数，学习了它的基本语法、常用函数和应用场景。窗口函数就像给你的SQL注入了一剂“超能力”，让你能够以更优雅、更高效的方式进行数据分析。

记住，掌握窗口函数，你就能：

摆脱复杂的JOIN，简化你的SQL代码。
在不改变数据粒度的前提下，进行各种聚合计算。
轻松解决各种复杂的数据分析问题。
让你的数据分析报告更加清晰易懂。

所以，赶紧操练起来，让窗口函数成为你数据分析的得力助手吧！

希望今天的分享对大家有所帮助。感谢各位的观看，咱们下次再见！

发表回复 取消回复

发表回复取消回复