SQL 窗口函数简介

您喜欢讨厌的非常强大的功能（但需要知道）

SQL 窗口函数提供了一些极其强大和有用的特性。 但对许多人来说，由于它们对标准 SQL 如此陌生，因此它们难以学习和理解，语法也很奇怪——而且经常被避免使用。

窗口函数可以简单地解释为类似于聚合的计算函数，但是在通过GROUP BY子句组合然后隐藏正在聚合的各个行的普通聚合中，窗口函数可以访问各个行并且可以将这些行中的一些属性添加到结果集。

在这个 SQL 窗口函数教程中，我将让您开始使用窗口函数，解释它们的好处以及何时使用它们，并为您提供真实的示例来帮助您理解这些概念。

了解您的数据的窗口

SQL 中最常用和最重要的特性之一是以特定方式聚合或分组数据行的能力。 但是，在某些情况下，分组可能会变得非常复杂，具体取决于所需的内容。

您是否曾经想要遍历查询结果以获得排名、前 x 列表或类似的？ 您是否有任何分析项目想要为可视化工具准备好数据，但发现这几乎不可能或太复杂以至于不值得？

窗口函数可以使事情变得更容易。 在获得查询结果之后——即，在WHERE子句和任何标准聚合之后，窗口函数将作用于剩余的行（数据窗口）并为您提供所需的内容。

我们将要研究的一些窗口函数包括：

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

太过简单

OVER子句用于指定窗口函数，并且必须始终包含在语句中。 OVER子句中的默认值是整个行集。 例如，让我们看一下公司数据库中的员工表，并在每一行显示员工总数，以及每个员工的信息，包括他们何时加入公司。

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

上面的代码，就像许多窗口函数一样，也可以用一种更熟悉的非窗口方式编写——在这个简单的例子中，这还不算太糟糕：

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

但是现在，假设我们希望显示与该行中的员工在同一个月开始工作的员工数量。 我们需要将每行的计数缩小或限制在该月。 这是怎么做的？ 我们使用 window PARTITION子句，如下所示：

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

分区允许您按某个或多个值将窗口过滤为多个部分。 每个部分通常被称为窗框。

更进一步，假设我们不仅想知道有多少员工在同一个月开始工作，而且我们想显示他们当月开始的顺序。 为此，我们可以使用熟悉的ORDER BY子句。 但是，在窗口函数中， ORDER BY的行为与查询结束时的行为略有不同。

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

在本例中， ORDER BY修改窗口，使其从分区的开始（在本例中为员工开始的月份和年份）到当前行。 因此，计数在每个分区重新开始。

排名它

窗口函数对于排名目的非常有用。 之前我们看到使用COUNT聚合函数可以让我们看到员工加入公司的顺序。 我们还可以使用窗口排名函数，例如ROW_NUMBER() 、 RANK()和DENSE_RANK() 。

我们在下个月添加新员工并删除分区后可以看到差异：

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

你可以看到差异。 ROW_NUMBER()给出给定分区内的顺序计数（但如果没有分区，它会遍历所有行）。 RANK()根据ORDER BY子句给出每行的排名。 它显示平局，然后跳过下一个排名。 DENSE_RANK也显示平局，但随后继续下一个连续值，就好像没有平局一样。

其他排名功能包括：

• CUME_DIST – 计算分区内当前行的相对排名
• NTILE – 尽可能平均地划分每个窗口分区的行
• PERCENT_RANK – 当前行的百分比排名

另请注意，在此示例中，您可以在单个查询中拥有多个 Window 函数，并且每个函数的分区和顺序都可以不同！

行、范围和框架，哦，我的

要在OVER()子句中进一步定义或限制您的窗口框架，您可以使用ROWS和RANGE 。 使用ROWS子句，您可以将分区中包含的行指定为当前行之前或之后的行。

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

在此示例中，窗口框架从第一行到当前行减 1，并且每一行的窗口大小继续增加。

范围有点不同，我们可能会得到不同的结果。

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

范围将包括窗口框架中与当前行具有相同ORDER BY值的那些行。 因此，如果ORDER BY不是唯一的，您可能会使用RANGE获得重复项。

有些人将ROWS描述为物理运算符，而RANGE是逻辑运算符。 在任何情况下， ROWS和RANGE的默认值始终是UNBOUNDED PRECEDING AND CURRENT ROW 。

还有什么？

大多数标准聚合函数都与 Window 函数一起使用。 我们已经在示例中看到了COUNT 。 其他包括SUM 、 AVG 、 MIN 、 MAX等。

使用窗口函数，您还可以使用LAG和LEAD以及FIRST_VALUE和LAST_VALUE访问以前的记录和后续记录。 例如，假设您想在每一行显示当月的销售数字，以及上个月销售数字之间的差异。 你可能会做这样的事情：

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

基本上，SQL 窗口函数非常强大

虽然这是对 SQL 窗口函数的快速介绍，但希望它会激发您的兴趣，了解它们可以做的所有事情。 我们了解到，窗口函数执行的计算类似于聚合函数的执行方式，但额外的好处是它们可以访问各个行中的数据，这使得它们非常强大。 它们总是包含OVER子句，并且可能包含PARTITION BY 、 ORDER BY和大量聚合（ SUM 、 COUNT等）和其他位置函数（ LEAD 、 LAG ）。 我们还了解了窗口框架以及它们如何封装数据部分。

请注意，不同风格的 SQL 可能以不同的方式实现窗口函数，并且有些可能没有实现所有窗口函数或子句。 确保检查您正在使用的平台的文档。

如果作为 SQL 开发人员，您对调整 SQL 数据库性能感兴趣，请查看面向开发人员的 SQL 数据库性能调整。

开窗快乐！

有关具体实现的更多信息，请参阅：

• PostgreSQL 用于 PostgreSQL 实现的 Window Functions 文档。
• SELECT - Microsoft 的 OVER 子句 (Transact-SQL)文档。
• SQL Server 中的窗口函数对 SQL Server 实现及其第 2 部分有一个很好的概述。