MySQL的窗口函数(也称为分析函数)非常有用,特别是在进行复杂数据分析和处理时。它们能够对数据集的各部分进行计算,而不必改变数据的物理顺序。这里将列举一些常用的窗口函数,并提供具体的SQL代码示例。
窗口函数
1. ROW_NUMBER()
用于为窗口内的每一行分配一个唯一的序号。
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
在这个例子中,ROW_NUMBER()
为每个部门内的员工根据薪水高低分配一个排名。
执行结果:
name | department | salary | rank |
---|---|---|---|
Alice | Sales | 5000 | 1 |
Bob | Sales | 4500 | 2 |
Carol | Sales | 4500 | 3 |
Dave | HR | 3000 | 1 |
说明:
在这个结果中,每个部门的员工根据薪水从高到低被赋予一个唯一的序号。即使薪水相同,序号也会递增。
2. RANK()
与 ROW_NUMBER()
类似,但如果遇到并列,则会跳过下一个序号。
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
执行结果:
name | department | salary | rank |
---|---|---|---|
Alice | Sales | 5000 | 1 |
Bob | Sales | 4500 | 2 |
Carol | Sales | 4500 | 2 |
Dave | HR | 3000 | 1 |
说明:
与ROW_NUMBER()
类似,但在薪水并列的情况下,赋予相同的排名,并且下一个排名会跳过。
3. DENSE_RANK()
与 RANK()
类似,但不跳过任何序号。
SELECT
name,
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
即使有并列,也不会跳过任何排名。
执行结果:
name | department | salary | rank |
---|---|---|---|
Alice | Sales | 5000 | 1 |
Bob | Sales | 4500 | 2 |
Carol | Sales | 4500 | 2 |
Dave | HR | 3000 | 1 |
说明:
此函数不会跳过任何排名,即使有并列情况。
4. SUM()
对窗口内的值进行求和。
SELECT
name,
department,
salary,
SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;
这个例子计算了每个部门所有员工的薪水总和。
执行结果:
name | department | salary | total_salary |
---|---|---|---|
Alice | Sales | 5000 | 14000 |
Bob | Sales | 4500 | 14000 |
Carol | Sales | 4500 | 14000 |
Dave | HR | 3000 | 3000 |
说明:
显示了每个部门的员工薪水总和。
5. AVG()
计算窗口内值的平均值。
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;
计算每个部门员工薪水的平均值。
执行结果:
name | department | salary | avg_salary |
---|---|---|---|
Alice | Sales | 5000 | 4666.67 |
Bob | Sales | 4500 | 4666.67 |
Carol | Sales | 4500 | 4666.67 |
Dave | HR | 3000 | 3000 |
说明:
计算每个部门的平均薪水,结果为浮点数以反映精确的平均值。
6. MIN()
和 MAX()
分别找出窗口内的最小值和最大值。
SELECT
name,
department,
salary,
MIN(salary) OVER (PARTITION BY department) AS min_salary,
MAX(salary) OVER (PARTITION BY department) AS max_salary
FROM employees;
这个例子找出每个部门中的最低和最高薪水。
执行结果:
name | department | salary | min_salary | max_salary |
---|---|---|---|---|
Alice | Sales | 5000 | 4500 | 5000 |
Bob | Sales | 4500 | 4500 | 5000 |
Carol | Sales | 4500 | 4500 | 5000 |
Dave | HR | 3000 | 3000 | 3000 |
说明:
每个部门的最低和最高薪水。
7. LEAD()
和 LAG()
LEAD()
查看后面的值,LAG()
查看前面的值。
SELECT
name,
department,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS previous_salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;
这个例子显示了每位员工在同部门的前一位和后一位员工的薪水。
执行结果:
name | department | salary | previous_salary | next_salary |
---|---|---|---|---|
Alice | Sales | 5000 | NULL | 4500 |
Bob | Sales | 4500 | 5000 | 4500 |
Carol | Sales | 4500 | 4500 | NULL |
Dave | HR | 3000 | NULL | NULL |
说明:
LAG()
显示前一行的薪水,而LEAD()
显示后一行的薪水。
8. FIRST_VALUE()
和 LAST_VALUE()
FIRST_VALUE()
和 LAST_VALUE()
是 MySQL 中的窗口函数,它们用于从一组记录中分别获取该组内的第一个值和最后一个值,这些值是根据窗口中的特定排序规则确定的。这两个函数非常有用,特别是在需要分析数据范围内的极值时。
FIRST_VALUE()
此函数返回窗口中第一个记录的值,通常与 OVER()
子句结合使用,该子句定义了分组和排序的规则。
SQL 代码示例:
SELECT name, department, salary,
FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_earner
FROM employees;
执行结果:
name | department | salary | highest_earner |
---|---|---|---|
June | Sales | 900 | June |
Jane | Sales | 800 | June |
John | Sales | 700 | June |
Joe | Marketing | 800 | Joe |
Judy | Marketing | 750 | Joe |
Jim | Marketing | 500 | Joe |
Jean | HR | 720 | Jean |
Jade | HR | 680 | Jean |
Joan | HR | 650 | Jean |
Jake | HR | 600 | Jean |
LAST_VALUE()
此函数返回窗口中最后一个记录的值。为了使 LAST_VALUE()
在不同的数据库管理系统中表现一致,通常需要设定窗口的 ROWS BETWEEN
子句。
SQL 代码示例:
SELECT name, department, salary,
LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lowest_earner
FROM employees;
执行结果:
name | department | salary | lowest_earner |
---|---|---|---|
June | Sales | 900 | John |
Jane | Sales | 800 | John |
John | Sales | 700 | John |
Joe | Marketing | 800 | Jim |
Judy | Marketing | 750 | Jim |
Jim | Marketing | 500 | Jim |
Jean | HR | 720 | Jake |
Jade | HR | 680 | Jake |
Joan | HR | 650 | Jake |
Jake | HR | 600 | Jake |
这两个函数在实际应用中可以帮助我们快速识别出每个部门的最高或最低薪资者,或是其他相关的业务指标最值。
OVER()
子句参数
- PARTITION BY - 将数据分成不同的分区(组),窗口函数会在每个分区内独立计算。
- ORDER BY - 在窗口内对行进行排序,影响窗口函数的计算结果。
- ROWS/RANGE BETWEEN - 定义窗口的范围。
ROWS
指按物理行距离来计算,而RANGE
与值的范围有关。
示例与执行结果
假设我们有一个名为 sales
的表格,包含以下列和数据:
employee_id | sale_date | sales_amount |
---|---|---|
1 | 2020-01-01 | 100 |
1 | 2020-01-02 | 150 |
2 | 2020-01-01 | 200 |
2 | 2020-01-02 | 250 |
示例 1:使用 SUM()
窗口函数计算累计销售额
SQL 语句:
SELECT employee_id, sale_date, sales_amount,
SUM(sales_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS cumulative_sales
FROM sales;
执行结果:
employee_id | sale_date | sales_amount | cumulative_sales |
---|---|---|---|
1 | 2020-01-01 | 100 | 100 |
1 | 2020-01-02 | 150 | 250 |
2 | 2020-01-01 | 200 | 200 |
2 | 2020-01-02 | 250 | 450 |
示例 2:使用 ROW_NUMBER()
窗口函数进行排序
SQL 语句:
SELECT employee_id, sale_date, sales_amount,
ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sales_amount DESC) AS rank
FROM sales;
执行结果:
employee_id | sale_date | sales_amount | rank |
---|---|---|---|
1 | 2020-01-02 | 150 | 1 |
1 | 2020-01-01 | 100 | 2 |
2 | 2020-01-02 | 250 | 1 |
2 | 2020-01-01 | 200 | 2 |
在 SQL 中使用 ROWS BETWEEN
子句可以非常精确地定义窗口函数的计算范围。这个子句通常用于指定窗口内的行的相对位置,用于计算如累积总和、移动平均等。
ROWS BETWEEN
子句参数
ROWS BETWEEN
子句可以接受几种不同的参数来定义窗口的起始点和终点:
- UNBOUNDED PRECEDING - 窗口开始于分区的第一行。
- UNBOUNDED FOLLOWING - 窗口结束于分区的最后一行。
- CURRENT ROW - 窗口的起点或终点是当前行。
- n PRECEDING - 窗口起始于当前行之前的 n 行,其中 n 是一个正整数。
- n FOLLOWING - 窗口结束于当前行之后的 n 行,其中 n 是一个正整数。
示例与执行结果
假设我们继续使用之前的 sales
表,现在我们来看一个计算移动平均的例子。
示例:计算每位员工的移动销售平均值
SQL 语句:
SELECT employee_id, sale_date, sales_amount,
AVG(sales_amount) OVER (PARTITION BY employee_id ORDER BY sale_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;
执行结果:
employee_id | sale_date | sales_amount | moving_avg |
---|---|---|---|
1 | 2020-01-01 | 100 | 100.0 |
1 | 2020-01-02 | 150 | 125.0 |
2 | 2020-01-01 | 200 | 200.0 |
2 | 2020-01-02 | 250 | 225.0 |
这个例子中,我们计算了一个移动平均值,窗口包括当前行和前一行。对于每个员工的第一行,因为没有前一行,所以移动平均值等于当前行的 sales_amount
。
通过这样的 SQL 语句和 ROWS BETWEEN
子句的不同参数组合,可以灵活地控制窗口函数的计算范围,实现各种复杂的数据分析任务。