实际上，窗口聚合与分组聚合在功能上是相同的，唯一的差别是，分组聚合是通过GROUP BY进行分组计算，而窗口聚合是通过OVER子句定义的窗口进行计算。前面我们讲了，这个所谓的窗口，实际上也是一组数据。

SQL Server提供的聚合函数包括：AVG、CHECKSUM_AGG、COUNT、COUNT_BIG、GROUPING、GROUPING_ID、MAX、MIN、SUM、STDEV、STDEVP、VAR、VARP。除了GROUPING和GROUPING_ID，都可以跟在OVER子句后面用于窗口的聚合计算。

开窗聚合函数支持分区、排序和框架三种元素，语法格式如下：

函数名称( < 参数 > ) OVER ( [ ]

[ [ ] ]

)

PARTITION BY用于指定按哪列进行分区，如果不指定分区的话，也就是说OVER子句的圆括号中内容为空时，聚合函数实际上是对整个行集进行计算。

ORDER BY与子句共同对框架所涉及的行进行限定。通常情况下，框架都会涉及到排序问题，就像9.1节介绍的实例——在每一行计算第1～第n名的平均成绩一样。

9.2.1 窗口的分区

通过分区，可以将窗口限定为与当前行的分区列具有相同值的那些行。分区与分组有些类似，但是，在一条语句中指定分组列后，对于整个语句你只能按此列进行分组，而分区则可以在一条语句中指定多个不同的分区。我们通过下面的示例来说明这个问题，首先创建演示用的Orders表。表中存放着雇员ID、销售年份、销售季度和销售额四列数据。

IF OBJECT_ID('dbo.Orders','U') IS NOT NULL

DROP TABLEdbo.Orders;



CREATE TABLE dbo.Orders

(

EmpID int,

SalesYearint,

SalesQuarter int,

SubTotalmoney

);

INSERT INTO dbo.Orders VALUES

(1, 2013,1, 100.00),

(1, 2013,2, 200.00),

(1, 2013,3, 300.00),

(1, 2013,4, 400.00),

(1, 2014,1, 200.00),

(1, 2014,2, 200.00),

(1, 2014,3, 100.00),

(1, 2014,4, 100.00),

(2, 2013,1, 150.00),

(2, 2013,2, 250.00),

(2, 2013,3, 350.00),

(2, 2013,4, 450.00),

(2, 2014,1, 250.00),

(2, 2014,2, 250.00),

(2, 2014,3, 150.00),

(2, 2014,4, 150.00);

假设要计算年度销售合计和每年中每季度的销售合计，两种不同的分组，在下面一条分区语句就可以实现，查询结果如表9-3所示。可以看到，2013年和2014年的销售合计是分别是2200和1400，并且为年中的每个季度都计算出了合计，例如，2013年1季度是250，2季度是450。

SELECT EmpID, SalesYear, SalesQuarter, SubTotal,

SUM(SubTotal) OVER (PARTITION BY SalesYear)AS YSubTotal,

SUM(SubTotal) OVER (PARTITION BY SalesYear,SalesQuarter) AS QSubTotal

FROM dbo.Orders

ORDER BY SalesYear, SalesQuarter, EmpID;

表9-3 使用分区同时计算按年和按年+季度的分组合计

如果使用分组的方式计算每年度销售合计和年度中每季度的销售合计，至少需要通过下面两条语句来完成。但是，我们分析一下表9-3，如果我们需要计算雇员销售额占全年销售额的比例，或是占季度销售额的比例，是不是非常容易呢？直接SubTotal/YSubTotal、SubTotal/QSubTotal就可以实现。

SELECT SalesYear, SUM(SubTotal) AS YSubTotal

FROM dbo.Orders

GROUP BY SalesYear;



SELECT SalesYear, SalesQuarter, SUM(SubTotal) ASQSubTotal

FROM dbo.Orders

GROUP BY SalesYear, SalesQuarter;

还有一种观点，就像我们在9.1节介绍的示例一样，分区可以通过子查询来实现。例如，下面两条语句的查询结果是相同的，都是计算雇员销售额占全年的比例，查询结果如表9-4所示。

SELECT SalesYear, SalesQuarter, EmpID, SubTotal,

SubTotal / SUM(SubTotal) OVER (PARTITION BY SalesYear) AS PerSubTotal

FROM dbo.Orders

ORDER BY SalesYear, SalesQuarter, EmpID;



SELECT O1.SalesYear, O1.SalesQuarter, O1.EmpID,O1.SubTotal,

O1.SubTotal / (SELECT SUM(SubTotal)

FROM dbo.Orders AS O2

WHERE O2.SalesYear = O1.SalesYear) ASPerSubTotal

FROM dbo.Orders AS O1

ORDER BY O1.SalesYear, O1.SalesQuarter, O1.EmpID;

表9-4 使用分区和子查询方式查询雇员销售额占全年销售额的比例