跳到主要内容

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;

输出:

namesalaryrow_num
Alice90001
Bob80002
Charlie70003

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;

输出:

namesalaryrankdense_rank
Alice900011
Bob800022
Charlie700033

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;

输出:

namesalarycumulative_salarymoving_avg
Alice900090009000
Bob8000170008500
Charlie7000240008000

实际应用场景

1. 计算累计销售额

假设你有一个销售表 sales,包含 dateamount 两列。你可以使用窗口函数计算每天的累计销售额。

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 BYORDER BYframe_clause,你可以灵活地定义窗口的范围和排序方式。常见的窗口函数包括 ROW_NUMBER()RANK()SUM()AVG() 等。

提示

练习:尝试在你的数据库中创建一个包含员工信息的表,并使用窗口函数计算每个部门的工资排名和累计工资。

附加资源

通过掌握窗口函数,你将能够更高效地处理复杂的数据分析任务。继续练习并探索更多高级用法吧!