SQL窗口函数

1. 简介

SQL窗口函数是一种在数据库查询中使用的高级功能，它可以对某个查询结果的子集进行计算和分析，并返回额外的结果列。窗口函数不仅可以在每一行上执行计算，还可以对整个子集进行计算，从而为查询结果提供更详细的信息。

在一般的SQL语句中，我们通常通过聚合函数（如SUM、COUNT、AVG等）对整个结果集进行计算，得到一个汇总值。而窗口函数的特点在于，它不改变查询结果的行数，但可以为每一行返回关联的计算值。

窗口函数提供了一种灵活而强大的方式来处理相对复杂的查询需求，它在数据分析和报表生成等领域中非常有用。

本文将详细介绍SQL窗口函数的使用方法和常见应用场景，帮助读者更好地理解和应用这一强大的功能。

2. 窗口函数语法

SQL窗口函数的语法如下：

<窗口函数> OVER ([PARTITION BY <列名> [ORDER BY <列名> [ASC | DESC]]])

• <窗口函数>：要执行的窗口函数，如SUM、AVG、ROW_NUMBER等。
• PARTITION BY：可选的子句，用于将结果集分成多个分区，每个分区独立计算。相当于将查询结果进行分组。
• ORDER BY：可选的子句，用于指定分区内的排序方式。
• ASC或DESC：可选的排序方式，按升序或降序排列。默认为升序。

注意，在窗口函数内部可以使用其他标准聚合函数和普通SQL表达式。

3. 窗口函数示例

3.1. ROW_NUMBER

ROW_NUMBER函数返回每一行在分区或结果集中的序号。在分区内，序号从1开始递增。

下面是一个示例表employee，包含了员工的基本信息：

我们可以使用ROW_NUMBER函数为每个员工生成一个序号：

SELECT id, name, department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employee;

运行结果如下：

3.2. SUM

SUM函数用于计算一个数值列的总和，但可以与窗口函数结合使用，在每一行返回一个累计总和。

考虑一个存储销售记录的表sales：

我们可以使用SUM窗口函数计算每一行之前的总销售额，并将结果返回：

SELECT id, product, category, quantity, price, SUM(quantity * price) OVER (ORDER BY id) AS total_sales
FROM sales;

运行结果如下：

3.3. RANK

RANK函数用于计算每一行在排序结果中的排名。相比于ROW_NUMBER函数，RANK函数可以跳过重复值，例如有两个员工的工资相同，则它们的排名将相同，并且下一个排名将被跳过。

继续使用上述示例表employee，我们可以使用RANK函数计算每个员工的工资排名：

SELECT id, name, department, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employee;

运行结果如下：

4. 窗口函数的常见应用场景

4.1. 获取前N行数据

通过结合ROW_NUMBER函数和ORDER BY子句，我们可以方便地获取前N行数据。例如，可以使用以下查询获取销售额最高的前5个产品：

SELECT *
FROM (
  SELECT product, SUM(quantity * price) AS total_sales,
         ROW_NUMBER() OVER (ORDER BY SUM(quantity * price) DESC) AS row_num
  FROM sales
  GROUP BY product
) tmp
WHERE row_num <= 5;

4.2. 计算累计总和和平均值

通过使用SUM和AVG窗口函数，我们可以计算累计总和和平均值。例如，下面的查询计算了每个部门的销售额累计总和和平均销售额：

SELECT department, total_sales,
       SUM(total_sales) OVER (PARTITION BY department ORDER BY total_sales) AS cumulative_sales,
       AVG(total_sales) OVER (PARTITION BY department) AS average_sales
FROM (
  SELECT department, SUM(quantity * price) AS total_sales
  FROM sales
  GROUP BY department
) tmp;

4.3. 检测异常值

通过使用窗口函数，我们可以轻松地检测和标记异常值。例如，我们可以使用以下查询找到工资高于平均工资的员工：

SELECT id, name, department, salary, 
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employee
WHERE salary > AVG(salary) OVER (PARTITION BY department);

这将返回所有工资高于部门平均工资的员工记录。

4.4. 分组内排序

使用窗口函数，我们可以对每个分组内的行进行排序，而不会改变查询结果的行数。例如，以下查询将返回每个部门中工资前三名的员工：

SELECT id, name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS ranking
FROM employee
WHERE ranking <= 3;

这将返回每个部门中工资最高的三个员工记录。

5. 总结

SQL窗口函数提供了一种灵活而强大的方式来处理复杂的查询需求。它允许我们对查询结果进行计算和分析，并返回额外的结果列，而不改变结果集的行数。

本文介绍了窗口函数的语法和常见的使用示例，包括ROW_NUMBER、SUM和RANK等窗口函数的用法。此外，文中还介绍了一些窗口函数的常见应用场景，如获取前N行数据、计算累计总和和平均值、检测异常值以及分组内排序。

通过灵活运用窗口函数，我们可以在SQL中实现更复杂的数据分析和报表生成需求，提高查询的灵活性和效率。