SUM 函数示例

以下示例显示充当窗口函数的 SUM 函数。查询将返回按 DepartmentID 划分数据的结果集，然后提供员工薪水的累计总和 (Sum_Salary)（从在公司时间最长的员工开始）。结果集只包括居住在 California、Utah、New York 或 Arizona 的那些雇员。Sum_Salary 列提供雇员薪水的累计总额。

SELECT DepartmentID, Surname, StartDate, Salary,
SUM( Salary ) OVER ( PARTITION BY DepartmentID
    ORDER BY StartDate
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
AS "Sum_Salary"
FROM Employees
WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
   AND DepartmentID IN ( '100', '200' )
ORDER BY DepartmentID, StartDate;

下表是查询的结果集。结果集按 DepartmentID 划分。

	DepartmentID	Surname	StartDate	Salary	Sum_Salary
1	100	Whitney	1984-08-28	45700.00	45700.00
2	100	Cobb	1985-01-01	62000.00	107700.00
3	100	Shishov	1986-06-07	72995.00	180695.00
4	100	Driscoll	1986-07-01	48023.69	228718.69
5	100	Guevara	1986-10-14	42998.00	271716.69
6	100	Wang	1988-09-29	68400.00	340116.69
7	100	Soo	1990-07-31	39075.00	379191.69
8	100	Diaz	1990-08-19	54900.00	434091.69
9	200	Overbey	1987-02-19	39300.00	39300.00
10	200	Martel	1989-10-16	55700.00	95000.00
11	200	Savarino	1989-11-07	72300.00	167300.00
12	200	Clark	1990-07-21	45000.00	212300.00
13	200	Goggin	1990-08-05	37900.00	250200.00

对于 DepartmentID 100 而言，来自 California、Utah、New York 和 Arizona 的雇员的薪水累计总额为 $434,091.69，而 DepartmentID 200 的雇员的薪水累计总额是 $250,200.00。

计算相邻行间的增量

使用两个窗口—一个窗口在当前行上，另一个在前一行上，可以计算相邻行之间的增量或更改。例如，以下查询将计算结果中一名雇员与前一名雇员之间的薪水增量 (Delta)：

SELECT EmployeeID AS EmployeeNumber, 
   Surname AS LastName,
   SUM( Salary ) OVER ( ORDER BY BirthDate 
      ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) 
      AS CurrentRow,
   SUM( Salary ) OVER ( ORDER BY BirthDate
      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) 
      AS PreviousRow,
   ( CurrentRow - PreviousRow ) AS Delta
FROM Employees
WHERE State IN ( 'NY' );

	EmployeeNumber	LastName	CurrentRow	PreviousRow	Delta
1	913	Martel	55700.000	(NULL)	(NULL)
2	1062	Blaikie	54900.000	55700.000	-800.000
3	249	Guevara	42998.000	54900.000	-11902.000
4	390	Davidson	57090.000	42998.000	14092.000
5	102	Whitney	45700.000	57090.000	-11390.000
6	1507	Wetherby	35745.000	45700.000	-9955.000
7	1751	Ahmed	34992.000	35745.000	-753.000
8	1157	Soo	39075.000	34992.000	4083.000

SUM 仅在 CurrentRow 窗口的当前行上执行，因为窗口大小已设置为 ROWS BETWEEN CURRENT ROW AND CURRENT ROW。同理，对于 PreviousRow 窗口，SUM 仅在前一行上执行，因为窗口大小已设置为 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING。在第一行中 PreviousRow 的值为 NULL，因为第一行没有前一行；因此 Delta 值也为 NULL。

复杂情况分析

请考虑以下查询，该查询列出数据库中各产品的最佳销售人员（由总销售额定义）：

SELECT s.ProductID AS Products, o.SalesRepresentative, 
     SUM( s.Quantity ) AS total_quantity,
     SUM( s.Quantity * p.UnitPrice ) AS total_sales
  FROM SalesOrders o KEY JOIN SalesOrderItems s 
    KEY JOIN Products p
  GROUP BY s.ProductID, o.SalesRepresentative
  HAVING total_sales = ( 
    SELECT First SUM( s2.Quantity * p2.UnitPrice ) 
        AS sum_sales
      FROM SalesOrders o2 KEY JOIN
        SalesOrderItems s2 KEY JOIN Products p2
      WHERE s2.ProductID = s.ProductID
      GROUP BY o2.SalesRepresentative
      ORDER BY sum_sales DESC )
  ORDER BY s.ProductID;

此查询会返回如下结果：

	Products	SalesRepresentative	total_quantity	total_sales
1	300	299	660	5940.00
2	301	299	516	7224.00
3	302	299	336	4704.00
4	400	299	458	4122.00
5	401	902	360	3600.00
6	500	949	360	2520.00
7	501	690	360	2520.00
8	501	949	360	2520.00
9	600	299	612	14688.00
10	601	299	636	15264.00
11	700	299	1008	15120.00

原始查询由可确定任何特定产品最高销售额的相关子查询构成，其中 ProductID 是子查询的相关外部引用。但是，使用嵌套查询通常是一种开销巨大的选择，此示例中即是如此。这是因为，子查询不仅涉及 GROUP BY 子句，还涉及 GROUP BY 子句内的 ORDER BY 子句。这样一来，查询优化程序就不能在保留相同语义的情况下，将此嵌套查询重新编写为连接。因此，在查询执行期间，将对在外部块中计算的每个派生行计算子查询。

注意图形式计划中开销巨大的 Filter 谓语：优化程序估计，执行查询 99% 的开销由此计划运算符带来的。子查询的计划清晰地阐释了主块中过滤器运算符开销如此巨大的原因：子查询涉及两个嵌套循环连接，即散列的 GROUP BY 操作和一个分类。

使用秩函数重新编写

使用秩函数对同一查询进行重新编写，将更高效的计算出同一结果：

SELECT v.ProductID, v.SalesRepresentative, 
  v.total_quantity, v.total_sales
  FROM ( SELECT o.SalesRepresentative, s.ProductID,
            SUM( s.Quantity ) AS total_quantity,
            SUM( s.Quantity * p.UnitPrice ) AS total_sales,
            RANK() OVER ( PARTITION BY s.ProductID 
              ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC ) 
              AS sales_ranking
           FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
           GROUP BY o.SalesRepresentative, s.ProductID ) 
           AS v
  WHERE sales_ranking = 1
  ORDER BY v.ProductID;

此重新编写的查询最终会生成一个更简单的计划：

请回忆一下，窗口运算符是在处理 GROUP BY 子句之后，计算 SELECT 列表项和查询的 ORDER BY 子句之前进行计算的。如同在图形式计划中所看到的，连接三个表以后，将按 SalesRepresentative 和 ProductID 属性的组合对所连接行进行分组。因此，可为每个 SalesRepresentative 和 ProductID 组合计算 total_quantity 和 total_sales 的 SUM 集合函数。

计算 GROUP BY 子句之后，就会使用窗口计算 RANK 函数，以按 total_sales 降序排列中间结果中的行。WINDOW 说明包括 PARTITION BY 子句。通过执行此操作，将重新划分（重新分组）GROUP BY 子句的结果—此次按 ProductID 划分。这样，RANK 函数以总销售额的降序排序各个产品的行，但是将针对已销售该产品的所有销售代表。通过此排序，确定最佳销售人员只需将派生表的结果限制为仅接受秩为 1 的那些行。对于出现并列的情况（如结果集中的第 7 行和第 8 行），RANK 将返回相同值。因此，销售人员 690 和 949 都会出现在最终结果中。

另请参见

使用DocCommentXchange讨论此页。