好的，各位老铁，各位靓仔靓女，咱们今天来聊聊MySQL 8.0的WINDOW子句，这玩意儿可不是你家窗户上的玻璃，而是SQL查询里的一大利器！别看它长得平平无奇，用好了能帮你上天入地，做出各种高大上的报表，让你在老板面前狠狠地秀一把操作！😎
开场白：为什么我们需要WINDOW子句？
想象一下，你接到老板的指令：
“小王啊，给我看看咱们公司每个月销售额的增长趋势，还有每个员工的销售额占总销售额的百分比，另外再按部门统计一下，看看哪个部门最能打！”
听到这，你是不是感觉脑袋嗡嗡的？ 😵💫 传统的SQL语句写起来那叫一个费劲，要用自连接、子查询，一层套一层，写完自己都看不懂，更别说维护了。
这时候，WINDOW子句就闪亮登场了！它就像一位超级英雄，专门解决这种需要“分组内计算”的难题。它允许你在查询结果的每一行上，访问与当前行相关的其他行的数据，而无需使用复杂的自连接和子查询。简单来说，它能让你在分组的基础上，进行更灵活、更强大的计算。
第一节：WINDOW子句的基础语法：打开新世界的大门
WINDOW子句的基本语法如下：
SELECT
column1,
column2,
...
window_function(column) OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column3, column4, ...]
[ROWS | RANGE frame_clause]
) AS alias_name
FROM
table_name
[WHERE condition];别慌，咱们一步一步来解读：
window_function(column)： 这就是你要使用的窗口函数，比如SUM(),AVG(),RANK(),ROW_NUMBER()等等，稍后我们会详细讲解。OVER (...)： 这是WINDOW子句的核心，它定义了窗口的范围和计算方式。PARTITION BY column1, column2, ...： 相当于GROUP BY，它将数据分成不同的分区，窗口函数会在每个分区内独立计算。你可以把它想象成把数据按照某个标准分成一个个小团队。ORDER BY column3, column4, ...： 定义了每个分区内数据的排序方式，很多窗口函数都需要排序才能正确计算。比如你要计算累积销售额，就需要按照时间排序。ROWS | RANGE frame_clause： 定义了窗口的“帧”，也就是当前行周围哪些行会参与计算。这个比较高级，我们后面再详细讲解。AS alias_name： 给窗口函数的结果起个别名，方便引用。
举个栗子：
假设我们有一个sales表，记录了每个月的销售额：
| month | sales |
|---|---|
| 2023-01 | 1000 |
| 2023-02 | 1200 |
| 2023-03 | 1500 |
| 2023-04 | 1300 |
| 2023-05 | 1600 |
如果我们想计算每个月的累积销售额，可以使用以下SQL语句：
SELECT
month,
sales,
SUM(sales) OVER (ORDER BY month) AS cumulative_sales
FROM
sales;这条SQL语句的意思是：
- 遍历
sales表的每一行。 - 按照
month列进行排序。 - 对于每一行，计算从第一行到当前行的
sales的总和，作为cumulative_sales。
结果如下：
| month | sales | cumulative_sales |
|---|---|---|
| 2023-01 | 1000 | 1000 |
| 2023-02 | 1200 | 2200 |
| 2023-03 | 1500 | 3700 |
| 2023-04 | 1300 | 4000 |
| 2023-05 | 1600 | 5600 |
是不是很简单？ 😎
第二节：常用的窗口函数：十八般武艺样样精通
WINDOW子句最强大的地方在于它可以和各种窗口函数配合使用，实现各种复杂的计算。下面我们介绍几个常用的窗口函数：
-
SUM(),AVG(),MIN(),MAX()： 这些聚合函数大家都很熟悉，它们可以在窗口内进行求和、平均值、最小值、最大值等计算。- 例子： 计算每个部门的平均工资：
SELECT employee_name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary_by_department FROM employees; -
RANK(),DENSE_RANK(),ROW_NUMBER()： 这些排名函数可以用来对窗口内的数据进行排名。-
RANK()： 如果有相同的数值，会跳过后面的排名。比如 1, 2, 2, 4。 -
DENSE_RANK()： 如果有相同的数值，不会跳过后面的排名。比如 1, 2, 2, 3。 -
ROW_NUMBER()： 简单粗暴，直接按照顺序编号，不会考虑数值是否相同。 -
例子： 按照销售额对每个地区的销售人员进行排名：
SELECT region, salesperson, sales, RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank FROM sales_data; -
-
LAG(),LEAD()： 这两个函数可以用来访问窗口内当前行的前一行或后一行的数据。-
LAG(column, n, default_value)： 获取当前行之前第n行的column的值，如果没有则返回default_value。 -
LEAD(column, n, default_value)： 获取当前行之后第n行的column的值，如果没有则返回default_value。 -
例子： 计算每个月销售额的环比增长率：
SELECT month, sales, LAG(sales, 1, 0) OVER (ORDER BY month) AS previous_month_sales, (sales - LAG(sales, 1, 0) OVER (ORDER BY month)) / LAG(sales, 1, 0) OVER (ORDER BY month) AS growth_rate FROM sales; -
-
FIRST_VALUE(),LAST_VALUE(),NTH_VALUE()： 这些函数可以用来获取窗口内第一个、最后一个或第N个值。- 例子： 获取每个部门工资最高的员工的姓名：
SELECT department, FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid_employee FROM employees;
第三节：frame_clause：精确定位你的计算范围
frame_clause是WINDOW子句里最灵活的部分，它可以让你精确控制窗口的“帧”，也就是当前行周围哪些行会参与计算。 frame_clause有两种形式：
ROWS： 基于行数定义窗口。RANGE： 基于值定义窗口。
常用的frame_clause的关键字包括：
UNBOUNDED PRECEDING： 从分区的第一行开始。UNBOUNDED FOLLOWING： 到分区的最后一行结束。CURRENT ROW： 当前行。n PRECEDING： 当前行之前的n行。n FOLLOWING： 当前行之后的n行。
举个栗子：
假设我们要计算每个月的7天移动平均销售额：
SELECT
month,
sales,
AVG(sales) OVER (ORDER BY month ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average_7_days
FROM
sales;这条SQL语句的意思是：
- 遍历
sales表的每一行。 - 按照
month列进行排序。 - 对于每一行，计算从当前行往前数6行（包括当前行）的
sales的平均值，作为moving_average_7_days。
再来个栗子：
假设我们要计算每个月的销售额占过去三个月总销售额的百分比：
SELECT
month,
sales,
SUM(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS total_sales_last_3_months,
sales / SUM(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS percentage_of_total
FROM
sales;第四节：WINDOW子句的高级应用：打造炫酷的报表
现在，让我们用WINDOW子句来解决一些实际的报表分析问题：
-
计算每个用户的复购率：
假设我们有一个
orders表，记录了每个用户的订单信息：user_id order_date 1 2023-01-01 1 2023-01-15 2 2023-02-01 2 2023-02-10 2 2023-02-20 3 2023-03-01 我们可以使用以下SQL语句计算每个用户的复购率：
SELECT user_id, COUNT(*) AS total_orders, SUM(CASE WHEN order_rank > 1 THEN 1 ELSE 0 END) AS repeat_orders, SUM(CASE WHEN order_rank > 1 THEN 1 ELSE 0 END) / COUNT(*) AS repurchase_rate FROM ( SELECT user_id, order_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_rank FROM orders ) AS subquery GROUP BY user_id;这条SQL语句的思路是：
- 首先，使用
ROW_NUMBER()函数对每个用户的订单按照日期进行排序，生成order_rank列。 - 然后，统计每个用户的总订单数和复购订单数（
order_rank > 1）。 - 最后，计算复购率。
- 首先，使用
-
计算每个用户的留存率：
假设我们需要计算每个用户在注册后的第二个月、第三个月的留存率。我们可以使用以下SQL语句：
SELECT user_id, SUM(CASE WHEN month_diff = 1 THEN 1 ELSE 0 END) / COUNT(*) AS retention_rate_month_1, SUM(CASE WHEN month_diff = 2 THEN 1 ELSE 0 END) / COUNT(*) AS retention_rate_month_2 FROM ( SELECT user_id, DATE_DIFF(order_date, MIN(order_date) OVER (PARTITION BY user_id), MONTH) AS month_diff FROM orders ) AS subquery GROUP BY user_id;这条SQL语句的思路是：
- 首先，使用
MIN(order_date) OVER (PARTITION BY user_id)获取每个用户的首次订单日期。 - 然后，使用
DATE_DIFF()函数计算每个订单日期与首次订单日期之间的月份差，生成month_diff列。 - 最后，统计每个用户在第二个月、第三个月是否有订单，计算留存率。
- 首先，使用
-
计算每个产品的销售额占比，并按照占比进行排名：
SELECT product_name, total_sales, total_sales / SUM(total_sales) OVER () AS sales_percentage, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM ( SELECT product_name, SUM(sales) AS total_sales FROM order_details GROUP BY product_name ) AS subquery;这条SQL语句的思路是：
- 首先，统计每个产品的总销售额。
- 然后，使用
SUM(total_sales) OVER ()计算所有产品的总销售额。 - 接着，计算每个产品的销售额占比。
- 最后，使用
RANK()函数按照销售额占比进行排名。
第五节：WINDOW子句的性能优化：让你的查询飞起来
WINDOW子句虽然强大，但如果使用不当，也会影响查询性能。 以下是一些优化建议：
- 尽量使用索引：
PARTITION BY和ORDER BY子句中使用的列应该有索引，这样可以加速分组和排序操作。 - 避免在窗口函数中使用复杂的表达式： 复杂的表达式会增加计算成本，影响性能。
- 合理选择
frame_clause：frame_clause的范围越大，计算成本越高。 - 尽量避免在
WHERE子句中使用窗口函数的结果： 窗口函数是在查询结果集上计算的，如果需要在WHERE子句中使用，可以使用子查询或者Common Table Expression (CTE)。
总结：WINDOW子句：SQL世界的瑞士军刀
WINDOW子句是MySQL 8.0中一个非常强大的功能，它可以让你在SQL查询中进行各种复杂的计算和分析。掌握了WINDOW子句，你就可以轻松地解决各种报表分析问题，让你的数据分析能力更上一层楼。💪
希望今天的讲解对大家有所帮助！如果大家还有什么疑问，欢迎在评论区留言！ 咱们下期再见！ 😉