跳到主要内容

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，包含 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() 等。

提示

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

附加资源​

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