MySQL 分析函数
MySQL分析函数是一种强大的工具，用于在查询结果中执行复杂的计算和分析。它们允许你在不改变查询结果集的情况下，对数据进行分组、排序和聚合操作。分析函数通常用于生成排名、累计值、移动平均值等高级分析结果。
什么是分析函数？
分析函数是SQL中的一类特殊函数，它们可以在查询结果集的基础上执行额外的计算。与普通的聚合函数（如SUM、AVG）不同，分析函数不会将结果集分组，而是为每一行返回一个值。这使得分析函数非常适合用于生成排名、累计值、移动平均值等复杂的分析结果。
分析函数的基本语法
分析函数的基本语法如下：
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[ROWS BETWEEN frame_start AND frame_end]
)
function_name: 分析函数的名称，如ROW_NUMBER、RANK、SUM等。expression: 要计算的表达式或列。PARTITION BY: 可选，用于将数据分组。ORDER BY: 可选，用于对分组内的数据进行排序。ROWS BETWEEN: 可选，用于定义计算范围。
常用的分析函数
以下是一些常用的MySQL分析函数：
- ROW_NUMBER(): 为每一行分配一个唯一的序号。
- RANK(): 为每一行分配一个排名，相同值的行会得到相同的排名，后续排名会跳过。
- DENSE_RANK(): 类似于
RANK()，但不会跳过后续排名。 - SUM(): 计算累计值。
- AVG(): 计算移动平均值。
示例1: 使用ROW_NUMBER()生成序号
假设我们有一个employees表，包含员工的姓名和薪水信息。我们可以使用ROW_NUMBER()为每个员工分配一个唯一的序号：
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM
employees;
输出:
| name | salary | row_num |
|---|---|---|
| Alice | 90000 | 1 |
| Bob | 80000 | 2 |
| Charlie | 75000 | 3 |
| David | 70000 | 4 |
在这个例子中，ROW_NUMBER()函数根据薪水从高到低的顺序为每个员工分配了一个唯一的序号。
示例2: 使用RANK()生成排名
如果我们想要为员工生成一个排名，可以使用RANK()函数：
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM
employees;
输出:
| name | salary | rank |
|---|---|---|
| Alice | 90000 | 1 |
| Bob | 80000 | 2 |
| Charlie | 75000 | 3 |
| David | 70000 | 4 |
在这个例子中，RANK()函数根据薪水从高到低的顺序为每个员工生成了一个排名。
示例3: 使用SUM()计算累计值
假设我们想要计算每个员工的累计薪水，可以使用SUM()函数：
SELECT
name,
salary,
SUM(salary) OVER (ORDER BY salary DESC) AS cumulative_salary
FROM
employees;
输出:
| name | salary | cumulative_salary |
|---|---|---|
| Alice | 90000 | 90000 |
| Bob | 80000 | 170000 |
| Charlie | 75000 | 245000 |
| David | 70000 | 315000 |
在这个例子中，SUM()函数计算了每个员工的累计薪水。
实际应用场景
场景1: 计算移动平均值
假设我们有一个sales表，包含每日的销售额。我们可以使用AVG()函数计算7天的移动平均值：
SELECT
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
sales;
输出:
| sale_date | amount | moving_avg |
|---|---|---|
| 2023-10-01 | 100 | 100.00 |
| 2023-10-02 | 150 | 125.00 |
| 2023-10-03 | 200 | 150.00 |
| 2023-10-04 | 250 | 175.00 |
| 2023-10-05 | 300 | 200.00 |
| 2023-10-06 | 350 | 225.00 |
| 2023-10-07 | 400 | 250.00 |
在这个例子中，AVG()函数计算了7天的移动平均值。
场景2: 生成部门内的员工排名
假设我们有一个employees表，包含员工的姓名、部门和薪水信息。我们可以使用RANK()函数为每个部门内的员工生成一个薪水排名：
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM
employees;
输出:
| name | department | salary | dept_rank |
|---|---|---|---|
| Alice | Sales | 90000 | 1 |
| Bob | Sales | 80000 | 2 |
| Charlie | HR | 75000 | 1 |
| David | HR | 70000 | 2 |
在这个例子中，RANK()函数为每个部门内的员工生成了一个薪水排名。
总结
MySQL分析函数是一种强大的工具，可以帮助你在查询结果中执行复杂的计算和分析。通过使用分析函数，你可以轻松生成排名、累计值、移动平均值等高级分析结果。本文介绍了分析函数的基本概念、语法以及一些常用的分析函数，并通过实际案例展示了它们的应用场景。
附加资源
练习
- 使用
ROW_NUMBER()函数为sales表中的每日销售额生成一个序号。 - 使用
RANK()函数为employees表中的员工生成一个部门内的薪水排名。 - 使用
SUM()函数计算sales表中的累计销售额。
在练习中，尝试使用不同的分析函数来解决实际问题，这将帮助你更好地理解和掌握这些函数的使用。