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
表中的累计销售额。
在练习中,尝试使用不同的分析函数来解决实际问题,这将帮助你更好地理解和掌握这些函数的使用。