8-1 窗口函数

在SQL的广阔领域中，窗口函数（Window Functions）是一个强大而灵活的工具，它们允许用户在SQL查询的结果集上执行复杂的计算，而不必将数据分组到单独的记录中。这些函数在处理数据分析、报告生成以及复杂的数据聚合任务时尤其有用。窗口函数通过定义一个“窗口”（或称为“分区”），在这个窗口内对一组行执行计算，而不影响结果集中的行数。本章节将深入介绍窗口函数的基本概念、使用场景、常见函数类型以及如何在实际查询中应用它们。

8.1.1 窗口函数基础

窗口函数是在SELECT语句的SELECT列表、ORDER BY或HAVING子句中使用的特殊函数。与聚合函数（如SUM、AVG）不同，窗口函数不会将多行数据缩减为单行输出，而是为结果集中的每一行提供一个计算值。窗口函数的这种特性使得它们非常适合于进行行与行之间的比较或计算累积总和、移动平均等。

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

<窗口函数名>() OVER (
    [PARTITION BY <列名>]
    [ORDER BY <列名> [ASC|DESC]]
    [ROWS|RANGE BETWEEN <起点> AND <终点>]
)

• <窗口函数名>：如ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()等，但用作窗口函数时需与OVER子句结合。
• PARTITION BY：可选，用于将结果集划分为多个分区，每个分区内独立执行窗口函数。
• ORDER BY：可选，指定窗口内行的排序方式，对于某些窗口函数（如ROW_NUMBER()）是必须的。
• ROWS|RANGE BETWEEN…AND…：定义窗口的框架，即窗口函数操作的数据范围。这是可选的，但如果不指定，则默认为整个分区。

8.1.2 常见窗口函数类型

1. 排名函数：

   • ROW_NUMBER()：为分区内的每行分配一个唯一的连续整数。
   • RANK()：为分区内的每行分配一个排名，如果两行或多行具有相同的值，则它们会获得相同的排名，并且排名会留下空缺。
   • DENSE_RANK()：与RANK()类似，但排名是连续的，即使存在并列也不会留下空缺。
   • NTILE(n)：将分区内的行分配到n个有序桶中，每个桶内的行数量尽可能相等。

2. 聚合窗口函数：

   • SUM()、AVG()、MIN()、MAX()等聚合函数也可以作为窗口函数使用，允许在分区内对指定列进行求和、平均、求最小值和最大值等操作，而结果集行数保持不变。

3. 分析函数：

   • LEAD(column, offset) 和 LAG(column, offset)：分别用于访问当前行之后的第offset行和之前的第offset行的数据。
   • FIRST_VALUE(column) 和 LAST_VALUE(column)：分别返回窗口内第一行和最后一行的指定列的值。
   • PERCENT_RANK() 和 CUME_DIST()：提供行在分区内的相对位置信息。

8.1.3 使用场景示例

示例1：使用ROW_NUMBER()进行排名

假设有一个员工表employees，包含员工ID、姓名和销售额字段，我们想要按销售额对员工进行排名：

SELECT employee_id, name, sales,
       ROW_NUMBER() OVER (ORDER BY sales DESC) AS sales_rank
FROM employees;

这里，ROW_NUMBER()函数会根据销售额降序排列结果，并为每行分配一个唯一的排名。

示例2：使用SUM()作为窗口函数计算累计销售额

如果我们想要查看每个员工的销售额以及到当前员工为止的累计销售额：

SELECT employee_id, name, sales,
       SUM(sales) OVER (ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM employees;

这里，SUM(sales)作为窗口函数，通过指定ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW作为窗口框架，计算从结果集的第一行到当前行的累计销售额。

示例3：使用PARTITION BY进行分区计算

假设员工表还包含部门ID，我们想要在每个部门内部按销售额对员工进行排名：

SELECT employee_id, name, department_id, sales,
       RANK() OVER (PARTITION BY department_id ORDER BY sales DESC) AS dept_sales_rank
FROM employees;

在这个查询中，RANK()函数首先根据department_id将员工分组到不同的分区中，然后在每个分区内按销售额降序对员工进行排名。

8.1.4 注意事项

• 窗口函数通常与大型数据集一起使用，因此性能优化变得尤为重要。合理设计分区和排序策略，以及使用适当的索引，可以显著提高查询效率。
• 窗口函数的结果依赖于ORDER BY子句（如果函数需要的话），因此在没有明确指定排序顺序的情况下，结果可能是不确定的。
• 窗口函数并不改变结果集中的行数，它们只是为每行添加了一个或多个计算列。

通过掌握窗口函数，你可以解锁SQL数据分析的强大能力，解决复杂的业务问题，生成富有洞察力的报告。希望本章内容能够为你提供坚实的基础，让你在SQL学习的道路上更进一步。