SQL 窗口函数中的过滤（over…partition by）详解

在本文中，我们将介绍SQL中窗口函数的使用，并重点探讨其内部的过滤功能。窗口函数在SQL中是非常强大的工具，它能够在一个查询结果集的窗口内进行计算，并通过过滤条件来限制计算的范围。

阅读更多：SQL 教程

什么是窗口函数？

在SQL中，窗口函数是一种特殊的函数，它能够在查询结果集的窗口内执行计算。这个窗口由窗口函数的OVER子句定义，通常以PARTITION BY子句分隔成若干行。窗口函数可以对窗口内的每一行进行计算，并返回计算结果。

除了窗口函数的计算结果，查询结果集的其他列也能够被访问。通过使用窗口函数，我们可以在不使用子查询的情况下进行复杂的计算和聚合。

如何在窗口函数中进行过滤？

在窗口函数中，我们可以使用FILTER子句来进行过滤。FILTER子句可以用于对窗口函数的计算结果进行条件过滤。

让我们考虑一个例子，假设我们有一个员工表格，其中包含员工姓名、部门和工资等信息。我们希望计算每个部门中工资超过平均工资的员工数量。

以下是示例表格的数据：

+----+-------+-------+
| ID | 名字  | 工资  |
+----+-------+-------+
| 1  | 张三  | 5000  |
| 2  | 李四  | 4000  |
| 3  | 王五  | 4500  |
| 4  | 赵六  | 5500  |
| 5  | 钱七  | 6000  |
| 6  | 孙八  | 4000  |
+----+-------+-------+

我们可以使用窗口函数来实现这个功能：

SELECT 部门, COUNT(*) FILTER (WHERE 工资 > AVG(工资)) 
    OVER (PARTITION BY 部门) AS 超过平均工资的员工数量
FROM 员工表

执行以上查询后，将得到如下结果：

+-------+------------------------+
| 部门  | 超过平均工资的员工数量  |
+-------+------------------------+
| IT    | 1                      |
| HR    | 1                      |
+-------+------------------------+

结果表明，IT部门和HR部门中的员工工资都超过了各自部门的平均工资，因此每个部门中都有一个员工。

过滤条件的注意事项

在使用过滤条件时，需要注意以下几点：

过滤条件的位置

过滤条件应该位于窗口函数的OVER子句之后，例如：

SELECT 列,
       窗口函数() FILTER (WHERE 过滤条件) OVER (PARTITION BY 分区列)
FROM 表

过滤条件的语法

过滤条件可以使用任何标量表达式作为其计算结果，例如：

• 列名：WHERE 列名 = 值
• 运算：WHERE 列名 + 列名 > 值
• 函数：WHERE 函数名(列名) = 值
• 子查询：WHERE 列名 = (SELECT 列名 FROM 表)

过滤条件的范围

过滤条件仅适用于窗口函数的计算结果，并不影响查询结果集的筛选。因此，过滤条件的结果是基于窗口函数的计算结果进行过滤的。

总结

本文介绍了SQL中窗口函数的过滤功能。通过使用FILTER子句，在窗口函数中可以指定过滤条件来限制计算的范围。我们还讨论了过滤条件的位置、语法和范围等注意事项。

使用窗口函数的过滤功能可以帮助我们更灵活地进行数据计算和聚合，提高了SQL查询的灵活性和效率。掌握窗口函数的过滤功能将使我们在处理复杂数据分析和报表生成等场景中更加得心应手。