窗口函数

概述

窗口函数（Window Functions）是 SQL 中一个强大的工具，允许你在查询结果集中执行复杂的计算。与聚合函数不同，窗口函数不会将结果集分组为单个输出行，而是为每一行计算一个值。这使得窗口函数在需要对数据进行分组、排序和计算排名时非常有用。

窗口函数的基本语法

窗口函数的基本语法如下：

function_name (expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

• function_name: 窗口函数的名称，如 ROW_NUMBER(), RANK(), SUM(), AVG() 等。
• expression: 要计算的表达式。
• PARTITION BY: 可选，用于将数据分组。
• ORDER BY: 可选，用于指定排序顺序。
• frame_clause: 可选，用于定义窗口框架。

常见的窗口函数

ROW_NUMBER()

ROW_NUMBER() 函数为每一行分配一个唯一的序号，从 1 开始。

SELECT 
    employee_id, 
    department_id, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM 
    employees;

RANK() 和 DENSE_RANK()

RANK() 和 DENSE_RANK() 函数用于计算排名。RANK() 会在遇到相同值时跳过后续排名，而 DENSE_RANK() 不会跳过。

SELECT 
    employee_id, 
    department_id, 
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM 
    employees;

SUM() 和 AVG()

SUM() 和 AVG() 函数用于计算窗口内的总和和平均值。

SELECT 
    employee_id, 
    department_id, 
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM 
    employees;

实践练习

练习 1: 计算每个部门的员工工资排名

假设你有一个 employees 表，包含 employee_id, department_id, 和 salary 字段。请编写一个查询，计算每个部门的员工工资排名。

SELECT 
    employee_id, 
    department_id, 
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

练习 2: 计算每个部门的累计工资

编写一个查询，计算每个部门的累计工资。

SELECT 
    employee_id, 
    department_id, 
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS cumulative_salary
FROM 
    employees;

总结

窗口函数是 SQL 中一个非常强大的工具，允许你在查询结果集中执行复杂的计算。通过 ROW_NUMBER(), RANK(), SUM(), AVG() 等函数，你可以轻松地对数据进行分组、排序和计算排名。掌握窗口函数将大大提升你在 SQL 查询中的灵活性和效率。

下一步

接下来，你可以学习更多高级的 SQL 主题，如公共表表达式（CTE）、递归查询、PIVOT 和 UNPIVOT 操作等。这些主题将进一步增强你在 SQL 中的数据处理能力。