SQL窗口函数

目录

SQL窗口函数简介

SQL OVER子句

SQL OVER与PARTITION BY

使用ORDER BY的SQL OVER

对窗口函数结果进行排序

使用两个窗口函数

带有PARTITION BY和ORDER BY的SQL OVER

SQL窗口函数关键概念

---

在SQL中，窗口函数是指一个函数，例如sum或average，它作用于相对于当前行的结果集的行。

有很多细节需要介绍，但您会在后面的文章中看到我们介绍的内容。现在，我们将深入研究窗口函数的工作原理，并真正了解有关框架的更多信息。

本课的所有示例均基于Microsoft SQL Server Management Studio和AdventureWorks数据库。您可以通过我的SQL Server入门指南开始使用这些免费工具。

SQL窗口函数简介

在大多数情况下，窗口函数是熟悉的。它们的特别之处在于它们被设置为对一组相对于查询处理器当前行的行进行操作。这个集合称为窗口框架，它是使用OVER子句设置的。

当您引入ORDER BY和PARTITION BY参数时，OVER子句可能会变得很棘手，但我们一次只做一个。在本文结束时，您将了解这些如何影响窗口函数计算，并准备好处理其他分析函数和分区选项。

下图汇集了我们将在本文中讨论的所有概念。当您阅读后续部分时，我鼓励您回到这幅插图，以便您了解它如何融入整体图景。

SQL窗口函数概述

SQL OVER子句

正如我们之前提到的，它是使聚合成为SQL窗口函数的OVER子句。在这里，您将看到一个简单的窗口函数来计算所有销售人员的SalesYTD总数。

SELECT BusinessEntityID
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER() as TotalSalesYTD
FROM  Sales.SalesPerson S

运行时输出：

SQL窗口函数结果

请注意，TotalSalesYTD与我们运行查询时的值相同

SELECT SUM(SalesYTD)
FROM   Sales.SalesPerson

事实上，如果我们愿意，我们可以使用这个子查询创建同样的结果：

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,(SELECT SUM(SalesYTD) 
          FROM Sales.SalesPerson) as TotalSalesYTD
FROM Sales.SalesPerson S

但是，当我们深入研究SQL窗口函数时，您会发现，它们聚合数据的能力迅速超过了仅使用子查询所实现的能力。

SQL OVER与PARTITION BY

SQL PARTITION BY将行分成组，窗口函数根据这些组计算其结果。跨这些行计算窗口函数。

它类似于GROUP BY子句的工作方式；但是，该GROUP BY子句的结果是一个摘要行。使用窗口函数，原始行保持不变。分区是逻辑的，仅用于计算目的。

这是我们之前示例的延续。在这种情况下，我们将通过TerritoryID划分结果。

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(PARTITION BY TerritoryID) as TotalSalesYTD
FROM   Sales.SalesPerson

请注意，TotalSalesYTD现在代表每个Territory的TotalSalesYTD。

我们可以使用子查询完成同样的查询吗？当然！我们可以使用类似于下面的相关子查询

SELECT BusinessEntityID
        ,TerritoryID
        ,SalesYTD
        ,(SELECT SUM(SalesYTD)
          FROM   Sales.SalesPerson
          WHERE  COALESCE(TerritoryID, -1) = COALESCE(S.TerritoryID, -1)
         ) AS TotalSalesYTD
FROM Sales.SalesPerson AS S
ORDER BY TerritoryID;

但是正如你所见，子查询写起来越来越麻烦；而SQL窗口函数则更为优雅。

你可能已经想通了，但是如果没有指定分区，那么整个结果都被认为是分区。当我们进入下一个主题时，了解这一点很重要，这与我们如何对分区中的行进行排序以进行计算有关。

使用ORDER BY的SQL OVER

在窗口中使用ORDER BY子句时，它设置在分区内处理的顺序行。请记住，如果未指定PARTITION BY，则分区包括每一行。

从分区开始到当前行的所有行或等于当前行ORDER BY值的行都被视为窗口框架的一部分。许多窗口函数，例如SUM，都在此框架内运行，这意味着根据窗口的ORDER BY设置运行总计、平均值等变得非常简单。

让我们看一个示例，我们将在其中计算运行总计或通过SalesPerson计算TotalSalesYTD。我们将通过销售人员的BusinessEntityID来订购窗口：

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(ORDER BY BusinessEntityID) as TotalSalesYTD
FROM   Sales.SalesPerson

这是结果

OVER的窗函数结果

请注意TotalSalesYTD逐渐变大并最终等于先前计算的TotalSalesYTD值。

对窗口函数结果进行排序

所以现在你可能想知道如果我们尝试用不同于BusinessEntityID的东西来排序结果会发生什么？

按顺序排序TerritoryID会破坏运行总计算吗？

其实不会！原因是SQL窗口函数的计算发生在SELECT执行顺序阶段，即在结果排序之前。如果您对此不确定，请查看此并排比较：

SQL窗口函数和排序结果

请注意，尽管左侧的结果与右侧的结果排序不同，但TotalSalesYTD对应BusinessEntityID的结果保持不变。您可以看到我在两个例子中指出了这一点（请参阅突出显示的行）。

关键在于，当SQL窗口函数执行计算时，它们是按照行的逻辑顺序进行的，这与最终结果无关。这些行称为框架，因为它们是窗口的子集。

使用两个窗口函数

让我们通过将我们的窗口函数组合成一个查询来继续这个例子。下面你会看到你有两个窗口函数：一个是计算总金额，另一个是运行总计：

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(ORDER BY BusinessEntityID)
       as RunningTotalSalesYTD     
      ,SUM(SalesYTD) OVER() as GrandTotalSalesYTD
FROM   Sales.SalesPerson
ORDER BY BusinessEntityID

这说明您可以在同一个查询中有两个框架。

两个窗口函数

RunningTotalSalesYTD的框架通过BusinessEntityID由当前行的顺序定义。GrandTotalSalesYTD框架，因为没有ORDER BY，所以覆盖了整个分区。

现在我们对SQL窗口函数的概念有了更好的理解，让我们看看如何在OVER子句中PARTITION BY协同ORDER BY是如何工作的。

带有PARTITION BY和ORDER BY的SQL OVER

在OVER子句中同时使用PARTION BY和ORDER BY提供了一种强大的组合。这样做，允许我们创建框架以在组内执行计算，例如运行总计。

考虑用于按销售人员计算每个销售区域内的运行总计的查询。

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(PARTITION BY TerritoryID                          
 ORDER BY BusinessEntityID)
        as RunningTotalSalesYTDTerritory
FROM  Sales.SalesPerson

在结果中，您可以看到当遇到新的TerritoryID (分区)时，运行的总重置量。

ORDER BY 和分区 BY

让我们回顾一下一些关键概念。

SQL窗口函数关键概念

以下是我们在本文中讨论的一些关键概念。我知道有很多内容，但是在继续之前了解概念和术语很重要。

OVER——这是你的信号，前面的函数是一个窗口函数。OVER子句中包含的项目，例如ORDER BY或PARTITION BY规定窗口的框架方式。意思是，如何计算窗口函数。

ORDER BY——这就是使函数成为运行总计的原因。它确定行在框架内处理到函数的顺序。

PARTITION——这是组成分组的原因。部分可以是一列或多列。一个新的分区结束窗口函数结果被重置。

一些观察：  

如果您没有ORDER BY，那么您将获得总计而不是运行总计。

您可以在同一个查询中有不同的窗口。例如，这使得包含按日、月和年的运行总计变得非常容易！

要记住的一个关键点是order by和partition by定义的框架与排序顺序完全分开。它与您在窗口函数中定义的窗口无关。它们不影响结果，因为首先计算结果。

本文最初发布于SQL Window Functions [Visual Explanation] - Essential SQL

https://www.codeproject.com/Articles/5326887/SQL-Window-Functions