MySQL高级函数之：`Window Function`与`GROUP BY`：其在数据聚合中的差异。

MySQL高级函数之：Window Function与GROUP BY：其在数据聚合中的差异

大家好，今天我们来深入探讨MySQL中两种强大的数据聚合技术：GROUP BY 和 Window Function（窗口函数）。虽然两者都用于数据聚合，但它们在功能、应用场景和结果呈现上存在显著差异。本次讲座旨在帮助大家理解这两种技术的原理，掌握它们的用法，并能够在实际应用中选择最合适的工具。

一、GROUP BY：传统聚合的基石

GROUP BY 是SQL中最基础、最常用的聚合函数。它的核心作用是将数据按照指定的列进行分组，然后对每个分组应用聚合函数（如COUNT, SUM, AVG, MIN, MAX）来计算汇总结果。

1.1 基本语法：

SELECT column1, column2, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column_name;

• SELECT column1, column2, ...：选择要显示的列。注意：SELECT 子句中，除了参与 GROUP BY 的列和聚合函数外，其他列都需要包含在 GROUP BY 子句中，否则会报错（取决于sql_mode设置）。
• aggregate_function(column_name)：对指定列应用聚合函数。
• FROM table_name：指定要查询的表。
• WHERE condition：可选的筛选条件。
• GROUP BY column1, column2, ...：指定分组的列。
• ORDER BY column_name：可选的排序子句。

1.2 示例：

假设我们有一个名为 orders 的表，存储了订单信息：

order_id	customer_id	order_date	amount
1	101	2023-01-01	100
2	102	2023-01-01	150
3	101	2023-01-02	200
4	103	2023-01-02	120
5	102	2023-01-03	180
6	101	2023-01-03	250

如果我们想统计每个客户的订单总金额，可以使用以下 SQL 查询：

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

查询结果如下：

customer_id	total_amount
101	550
102	330
103	120

1.3 限制：

GROUP BY 的一个主要限制是，它会 折叠 数据。也就是说，它将具有相同分组值的行合并成一行，并仅返回聚合后的结果。这意味着你无法在结果中同时看到原始数据行和聚合结果。这在某些情况下会限制 GROUP BY 的应用。

二、Window Function：灵活的窗口计算

Window Function (窗口函数)，也被称为 OVER 函数，是 SQL 中一种高级的聚合函数。它允许我们在查询结果的 “窗口”（一组相关的行）上执行计算，而 不会折叠数据。这意味着你可以同时看到原始数据行和窗口计算的结果。

2.1 基本语法：

SELECT column1, column2, ...,
       window_function(column_name) OVER (
           [PARTITION BY column1, column2, ...]
           [ORDER BY column_name [ASC | DESC]]
           [ROWS | RANGE BETWEEN frame_start AND frame_end]
       ) AS window_function_result
FROM table_name
WHERE condition;

• window_function(column_name)：要应用的窗口函数，例如 ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM(), AVG(), MIN(), MAX() 等。
• OVER (...)：定义窗口的子句。
  • PARTITION BY column1, column2, ...：将数据分成不同的分区。每个分区都是一个独立的窗口。类似于 GROUP BY，但不会折叠数据。
  • ORDER BY column_name [ASC | DESC]：在每个分区内对数据进行排序。这对于某些窗口函数（如 RANK(), LAG(), LEAD()）非常重要。
  • ROWS | RANGE BETWEEN frame_start AND frame_end：定义窗口的帧。帧是当前行的一个子集，用于计算窗口函数。 如果没有定义帧，则默认帧是整个分区。

2.2 常用窗口函数：

• 排名函数：

  • ROW_NUMBER()：为每个分区中的每一行分配一个唯一的序号，从 1 开始。
  • RANK()：为每个分区中的每一行分配一个排名。如果存在相同的数值，则排名相同，但下一个排名会跳过相应的数量。
  • DENSE_RANK()：与 RANK() 类似，但排名是连续的，不会跳过。
  • NTILE(n)：将每个分区中的行分成 n 组，并为每行分配一个组号。

• 偏移函数：

  • LAG(column_name, offset, default_value)：返回当前行之前 offset 行的 column_name 的值。如果 offset 行不存在，则返回 default_value。
  • LEAD(column_name, offset, default_value)：返回当前行之后 offset 行的 column_name 的值。如果 offset 行不存在，则返回 default_value。

• 聚合函数：

  • SUM(), AVG(), MIN(), MAX(), COUNT()：可以在窗口上计算聚合值。

2.3 示例：

继续使用上面的 orders 表。如果我们想为每个客户的订单按照订单日期排序，并添加一个序号，可以使用以下 SQL 查询：

SELECT order_id, customer_id, order_date, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_number
FROM orders;

查询结果如下：

order_id	customer_id	order_date	amount	order_number
1	101	2023-01-01	100	1
3	101	2023-01-02	200	2
6	101	2023-01-03	250	3
2	102	2023-01-01	150	1
5	102	2023-01-03	180	2
4	103	2023-01-02	120	1

可以看到，我们仍然保留了原始数据行，并且添加了一个 order_number 列，表示每个客户的订单序号。

2.4 窗口帧 (Window Frame)：

窗口帧定义了当前行要参与计算的行的范围。如果没有指定窗口帧，默认是整个分区。

常用的窗口帧定义：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区的第一行到当前行。
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到分区的最后一行。
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：整个分区。
• ROWS BETWEEN n PRECEDING AND CURRENT ROW：从当前行之前 n 行到当前行。
• ROWS BETWEEN CURRENT ROW AND n FOLLOWING：从当前行到当前行之后 n 行。

示例：计算每个客户的订单金额的累积总和：

SELECT order_id, customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM orders;

查询结果如下：

order_id	customer_id	order_date	amount	cumulative_amount
1	101	2023-01-01	100	100
3	101	2023-01-02	200	300
6	101	2023-01-03	250	550
2	102	2023-01-01	150	150
5	102	2023-01-03	180	330
4	103	2023-01-02	120	120

可以看到，cumulative_amount 列显示了每个客户截止到当前订单日期的订单金额的累积总和。

三、GROUP BY vs. Window Function：差异与选择

特性	GROUP BY	Window Function
数据折叠	是	否
返回结果	每个分组一行	原始数据行 + 窗口计算结果
应用场景	汇总统计，例如计算总和、平均值等	排名、偏移、累积计算等，需要保留原始数据行的场景
语法	GROUP BY column1, column2, ...	OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)

3.1 何时使用 GROUP BY？

当你只需要汇总统计信息，而不需要保留原始数据行时，GROUP BY 是一个不错的选择。例如：

• 计算每个部门的员工数量。
• 计算每个产品的销售总额。
• 查找平均工资高于某个值的部门。

3.2 何时使用 Window Function？

当你需要在原始数据行的基础上进行计算，并保留原始数据行时，Window Function 是一个更合适的选择。例如：

• 计算每个员工的工资排名。
• 计算每个订单的销售额占总销售额的比例。
• 查找每个客户的上一个订单日期。
• 计算移动平均值。

3.3 示例对比：

假设我们有一个名为 employees 的表，存储了员工信息：

employee_id	department_id	salary
1	10	50000
2	10	60000
3	20	70000
4	20	80000
5	10	55000

场景1：计算每个部门的平均工资。

使用 GROUP BY：

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

查询结果：

department_id	average_salary
10	55000
20	75000

使用 Window Function：

SELECT employee_id, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS average_salary
FROM employees;

查询结果：

employee_id	department_id	salary	average_salary
1	10	50000	55000
2	10	60000	55000
5	10	55000	55000
3	20	70000	75000
4	20	80000	75000

可以看到，使用 GROUP BY 只能得到每个部门的平均工资，而使用 Window Function 可以同时得到每个员工的工资和所在部门的平均工资。

场景2：计算每个员工的工资排名。

使用 Window Function：

SELECT employee_id, department_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

查询结果：

employee_id	department_id	salary	salary_rank
4	20	80000	1
3	20	70000	2
2	10	60000	3
5	10	55000	4
1	10	50000	5

在这个场景中，使用 GROUP BY 无法直接实现。

四、高级应用：结合 GROUP BY 和 Window Function

在某些复杂场景下，我们可以将 GROUP BY 和 Window Function 结合使用，以实现更强大的数据分析功能。

例如，假设我们想找到每个部门中工资最高的员工的信息。

首先，我们可以使用 Window Function 计算每个员工在所在部门的工资排名：

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

然后，我们可以将这个查询作为子查询，并使用 GROUP BY 筛选出每个部门中工资排名第一的员工：

SELECT employee_id, department_id, salary
FROM (
    SELECT employee_id, department_id, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees
) AS ranked_employees
WHERE salary_rank = 1;

查询结果：

employee_id	department_id	salary
2	10	60000
4	20	80000

这个例子展示了如何将 Window Function 用于预处理数据，然后使用 GROUP BY 进行最终的筛选和聚合。

五、性能考量

Window Function 通常比 GROUP BY 在某些场景下性能更高，尤其是在处理需要保留原始数据行的情况下。 这是因为 GROUP BY 需要对数据进行排序和折叠，而 Window Function 可以在不折叠数据的情况下执行计算。

但是，Window Function 的性能也取决于窗口的大小和使用的函数。 复杂的窗口函数和大型窗口可能会导致性能下降。 因此，在实际应用中，需要根据具体情况进行性能测试和优化。

六、未来趋势

随着数据量的不断增长，Window Function 在数据分析和报表生成中的应用越来越广泛。 越来越多的数据库系统开始支持 Window Function，并且提供了更多的窗口函数和更灵活的窗口定义。

未来，我们可以期待 Window Function 在数据处理领域发挥更大的作用，帮助我们更高效地分析和利用数据。

理解差异，灵活选择

GROUP BY 和 Window Function 都是强大的数据聚合工具，但它们的应用场景和结果呈现方式有所不同。 理解它们的差异，并根据实际需求选择最合适的工具，是成为一名优秀 SQL 开发者的关键。 熟练掌握这两种技术，能够帮助你更好地处理和分析数据，挖掘数据背后的价值。