SQL 窗口函数详解
SQL窗口函数(Window Functions)是SQL中一种强大的工具,允许你在不改变查询结果集的情况下,对数据进行复杂的分析和计算。与普通的聚合函数不同,窗口函数不会将多行合并为一行,而是为每一行返回一个计算结果。这使得窗口函数非常适合用于排名、累计计算、移动平均等场景。
什么是窗口函数?
窗口函数是一种特殊的SQL函数,它可以在一个“窗口”内对数据进行计算。这个“窗口”是由 OVER
子句定义的,它指定了函数计算的范围。窗口函数不会改变查询结果的行数,而是为每一行返回一个计算结果。
窗口函数的基本语法
sql
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[frame_clause]
)
- function_name: 窗口函数的名称,如
ROW_NUMBER()
、RANK()
、SUM()
等。 - expression: 需要计算的表达式。
- PARTITION BY: 将数据分组,类似于
GROUP BY
,但不会减少行数。 - ORDER BY: 指定窗口内的排序方式。
- frame_clause: 定义窗口的范围,如
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
。
常见的窗口函数
1. ROW_NUMBER()
ROW_NUMBER()
函数为每一行分配一个唯一的序号,序号从1开始。
sql
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
输出:
name | salary | row_num |
---|---|---|
Alice | 9000 | 1 |
Bob | 8000 | 2 |
Charlie | 7000 | 3 |
2. RANK() 和 DENSE_RANK()
RANK()
和 DENSE_RANK()
函数用于为行分配排名。RANK()
会在有相同值时跳过后续排名,而 DENSE_RANK()
则不会。
sql
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
输出:
name | salary | rank | dense_rank |
---|---|---|---|
Alice | 9000 | 1 | 1 |
Bob | 8000 | 2 | 2 |
Charlie | 7000 | 3 | 3 |
3. SUM() 和 AVG()
SUM()
和 AVG()
函数可以用于计算累计和或移动平均值。
sql
SELECT
name,
salary,
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary,
AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;
输出:
name | salary | cumulative_salary | moving_avg |
---|---|---|---|
Alice | 9000 | 9000 | 9000 |
Bob | 8000 | 17000 | 8500 |
Charlie | 7000 | 24000 | 8000 |
实际应用场景
1. 计算累计销售额
假设你有一个销售表 sales
,包含 date
和 amount
两列。你可以使用窗口函数计算每天的累计销售额。
sql
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM sales;
2. 计算员工工资排名
你可以使用 RANK()
函数计算每个部门内员工的工资排名。
sql
SELECT
department,
name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees;
总结
SQL窗口函数是一种强大的工具,可以帮助你在不改变查询结果集的情况下,对数据进行复杂的分析和计算。通过 PARTITION BY
、ORDER BY
和 frame_clause
,你可以灵活地定义窗口的范围和排序方式。常见的窗口函数包括 ROW_NUMBER()
、RANK()
、SUM()
和 AVG()
等。
提示
练习:尝试在你的数据库中创建一个包含员工信息的表,并使用窗口函数计算每个部门的工资排名和累计工资。
附加资源
- SQL Window Functions - PostgreSQL Documentation
- SQL Window Functions - MySQL Documentation
- SQL Window Functions - SQL Server Documentation
通过掌握窗口函数,你将能够更高效地处理复杂的数据分析任务。继续练习并探索更多高级用法吧!