跳到主要内容

MySQL 分析函数

MySQL分析函数是一种强大的工具,用于在查询结果中执行复杂的计算和分析。它们允许你在不改变查询结果集的情况下,对数据进行分组、排序和聚合操作。分析函数通常用于生成排名、累计值、移动平均值等高级分析结果。

什么是分析函数?

分析函数是SQL中的一类特殊函数,它们可以在查询结果集的基础上执行额外的计算。与普通的聚合函数(如SUMAVG)不同,分析函数不会将结果集分组,而是为每一行返回一个值。这使得分析函数非常适合用于生成排名、累计值、移动平均值等复杂的分析结果。

分析函数的基本语法

分析函数的基本语法如下:

sql
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[ROWS BETWEEN frame_start AND frame_end]
)
  • function_name: 分析函数的名称,如ROW_NUMBERRANKSUM等。
  • expression: 要计算的表达式或列。
  • PARTITION BY: 可选,用于将数据分组。
  • ORDER BY: 可选,用于对分组内的数据进行排序。
  • ROWS BETWEEN: 可选,用于定义计算范围。

常用的分析函数

以下是一些常用的MySQL分析函数:

  1. ROW_NUMBER(): 为每一行分配一个唯一的序号。
  2. RANK(): 为每一行分配一个排名,相同值的行会得到相同的排名,后续排名会跳过。
  3. DENSE_RANK(): 类似于RANK(),但不会跳过后续排名。
  4. SUM(): 计算累计值。
  5. AVG(): 计算移动平均值。

示例1: 使用ROW_NUMBER()生成序号

假设我们有一个employees表,包含员工的姓名和薪水信息。我们可以使用ROW_NUMBER()为每个员工分配一个唯一的序号:

sql
SELECT 
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM
employees;

输出:

namesalaryrow_num
Alice900001
Bob800002
Charlie750003
David700004

在这个例子中,ROW_NUMBER()函数根据薪水从高到低的顺序为每个员工分配了一个唯一的序号。

示例2: 使用RANK()生成排名

如果我们想要为员工生成一个排名,可以使用RANK()函数:

sql
SELECT 
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM
employees;

输出:

namesalaryrank
Alice900001
Bob800002
Charlie750003
David700004

在这个例子中,RANK()函数根据薪水从高到低的顺序为每个员工生成了一个排名。

示例3: 使用SUM()计算累计值

假设我们想要计算每个员工的累计薪水,可以使用SUM()函数:

sql
SELECT 
name,
salary,
SUM(salary) OVER (ORDER BY salary DESC) AS cumulative_salary
FROM
employees;

输出:

namesalarycumulative_salary
Alice9000090000
Bob80000170000
Charlie75000245000
David70000315000

在这个例子中,SUM()函数计算了每个员工的累计薪水。

实际应用场景

场景1: 计算移动平均值

假设我们有一个sales表,包含每日的销售额。我们可以使用AVG()函数计算7天的移动平均值:

sql
SELECT 
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
sales;

输出:

sale_dateamountmoving_avg
2023-10-01100100.00
2023-10-02150125.00
2023-10-03200150.00
2023-10-04250175.00
2023-10-05300200.00
2023-10-06350225.00
2023-10-07400250.00

在这个例子中,AVG()函数计算了7天的移动平均值。

场景2: 生成部门内的员工排名

假设我们有一个employees表,包含员工的姓名、部门和薪水信息。我们可以使用RANK()函数为每个部门内的员工生成一个薪水排名:

sql
SELECT 
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM
employees;

输出:

namedepartmentsalarydept_rank
AliceSales900001
BobSales800002
CharlieHR750001
DavidHR700002

在这个例子中,RANK()函数为每个部门内的员工生成了一个薪水排名。

总结

MySQL分析函数是一种强大的工具,可以帮助你在查询结果中执行复杂的计算和分析。通过使用分析函数,你可以轻松生成排名、累计值、移动平均值等高级分析结果。本文介绍了分析函数的基本概念、语法以及一些常用的分析函数,并通过实际案例展示了它们的应用场景。

附加资源

练习

  1. 使用ROW_NUMBER()函数为sales表中的每日销售额生成一个序号。
  2. 使用RANK()函数为employees表中的员工生成一个部门内的薪水排名。
  3. 使用SUM()函数计算sales表中的累计销售额。
提示

在练习中,尝试使用不同的分析函数来解决实际问题,这将帮助你更好地理解和掌握这些函数的使用。