OVER子句

　　OVER子句用于为行为定义一个窗口（windows），以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。例如，聚合函数和排名函数都是可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也称之为开窗函数。

　　聚合函数的要点就是要对一组值进行聚合，聚合函数传统上一直以GROUP BY查询作为操作的上下文。在前面的“GROUP BY”子句的讨论中，我们知道在对数据进行分组以后，查询为每个组只返回一行；因此，也就是要限制所有的表达式为每个组只能返回一个值。

　　聚合开窗函数使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用GROUP BY子句提供的上下文。这样就不必对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。为了理解OVER子句，现在考虑Sales.OrderValues视图，现在只要简单地把视图看作是一个表就可以了。Sales.OrderValues视图的每一行代表一个订单，包含订单ID（orderid），客户ID（custid）、雇员ID（empid）、承运人ID（shipperid）、订单日期（orderdate），以及订单价格（val）。

　　带有空括号的OVER子句会提供所有行进行计算。这里的“所有行”并不一定是在FROM子句中出现的那些表中的所有行，而是在FROM、WHERE、GROUP BY，以及HAVING处理阶段完成后仍然可用的那些行。注意，只有在SELECT和ORDER BY处理阶段才允许使用OVER子句。这里只中点介绍在SELECT处理阶段如何使用OVER子句。例如，如果在对OrderValues视图进行查询的SELECT子句中指定了SUM(val) OVER()表达式，这个函数就会对SELECT阶段操作的所有行计算其总价格。

　　如果查询在SELECT阶段之前没有过滤数据，也没有应用其他的逻辑阶段，这个表达式将返回OrderValues视图中所有行的总价格。如果想对行进行限制或分区，则可以使用PARTITION BY子句。例如，现在不想返回OrderValues中所有行的总价格，而只是想返回当前客户（和当前行具有相同custid的所有行）的总价格，则可以指定SUM(val) OVER(PARTITION BY custid)。

　　以下查询可以返回OrderValues的所有行，并且演示了同时使用非分区和分区表达式的方法。此外，每一行除了基本列以外，查询还会返回所有行的总价格和当前客户的总价格：

1 SELECT orderid,custid,val,
2 SUM(val) OVER() AS totalvalue,
3 SUM(val) OVER(PARTITION BY custid) AS custtotalvalue
4 FROM Sales.OrderValues;

　　这个查询返回以下结果：

　　所有结果行的totalvalue列表示所有行的价格总数。custtotalvalue列表示所有行中与当前行具有相同custid值的那些行的价格总数。

　　OVER子句的一个优点就是能够在返回基本列的同时，在同一行对它们进行聚合；也可以在表达式中混合使用基本列和聚合值列。例如，以下查询为OrderValues的每一行计算当前价格占总价格的百分比，以及当前价格占客户总价格的百分比。

1 SELECT orderid,custid,val,
2 100. * val /SUM(val) OVER() AS totalvalue,
3 100. * val /SUM(val) OVER(PARTITION BY custid) AS custtotalvalue
4 FROM Sales.OrderValues;

　　注意，在表达式中使用的是十进制实数100.（100后面加个点），而不是直接使用整数100，因为这样可以隐式地将整数数值val和SUM(val)转换成十进制实数值。否则，表达式中的除法将是“整数除法”，会截去数值的小数部分。

　　这个查询返回以下结果：

　　OVER子句也支持四种排名函数：ROW_NUMBER(行号)、RANK(排名)、DENSE_RANK(密集排名)，以及NTILE。以下查询演示了这些函数的用法：

2     ROW_NUMBER() OVER(ORDERBY val) AS rownum,
3     RANK() OVER(ORDERBY val) AS rank,
4     DENSE_RANK() OVER(ORDERBY val) AS dense_rank,
5     NTILE(10) OVER(ORDERBY val) AS ntile
6 FROM Sales.OrderValues
7 ORDERBY val;

　　该查询会生成以下输出：

　　ROW_NUMBER函数用于为查询的结果集中的各行分配递增的序列号，其逻辑顺序通过OVER子句中的ORDER BY语句进行指定。在我们的查询例子中，逻辑顺序基于的是val列；因此，从输出中可以看到，随着订单价格的增加，行号也随之增加。不过，即使订单价格没有增加，行号也会依然增加。所以，如果ROW_NUMBER函数的ORDER BY不能唯一确定行的顺序（如前面这个例子所示），查询结果就是不确定的。也就是说，查询可能返回多个正确的结果。例如，可以看到订单价格为36.00的两行，它们的行号分别是7和8。如果这些行的顺序发生了变化，结果都可以认为是正确的。如果想让行号计算值是确定的，则必须在ORDER BY列表中添加元素，让它具有唯一性；也就是说，要让ORDER BY子句中列出的元素能够唯一地确定各个行。例如，可以在ORDER BY列表中增加orderid作为附加属性，这样，行号计算结果就成为确定的。

　　前面提过，即使行的排序值相同，ROW_NUMBER函数也一定为其生成唯一的行号值。如果想以同样的方式对排序值中的相同值进行更多的处理，可以考虑使用RANK或DENSE_RANK函数。这两个函数与ROW_NUMBER类似，但它们为具有相同逻辑排序值的所有行生成同样的排名。RANK和DENSE_RANK的区别是：RANK表示之前有多少行具有更低的排序值，而DENSE_RANK则表示之前有多少个更低的排序值。例如，在我们的查询例子中，rank列为9表示前面有8行具有更小的排序值，dense_rank列为9表示前面有8个更小的不同排序值。

　　NTILE函数可以把结果中的行关联到组（tile，相当于由行组成的指定数目的组），并为每一行分配一个所属的组的编号。NTILE函数接受一个表示组的数量的输入参数，并要在OVER子句中指定逻辑顺序。我们的查询例子有830行，请求将其分成10组；因此，组的大小就是83（830除以10）。逻辑排序基于的是val列，这意味着价格最低的83行将分在第1组，接下来的83行将分在第2组，在接下来的83行将分在第3组，以此类推。NTILE函数在逻辑上需要依赖于ROW_NUMBER函数。整个过程是先根据对val的排序结果，为每一行分配行号；在基于前面计算好的组的大小83行，将第1行到第83行分配到第1组，将第84行到第166行分配到第2组，以此类推。如果组数无法整除表的行数，余数（remainder）中的每一行会被分配到最前面的每个组。例如，假设有102行，请求分成5组，那么前两组将有21行而不是20行。

　　和聚合开窗函数类似，排名函数也支持在OVER子句中使用PARTITION BY语句。以排名计算作为背景，理解PARTITION BY子句的含义可能要更容易些。例如，与在整个集合中分配行号不同，表达式ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val)为各行中具有相同custid的子集独立地分配行号。在以下查询中使用了这个表达式：

1 SELECT orderid,custid,val,
2     ROW_NUMBER() OVER(PARTITION BY custid ORDERBY val) AS rownum
3 FROM Sales.OrderValues
4 ORDERBY custid,val;

　　该查询会生成以下输出：

　　从这些输出中可以看到，行号是为每个客户独立计算的，好像计算过程为每个客户重置了一样。

　　注意，OVER子句中指定的ORDER BY逻辑与数据展示没什么关系，并不会改变查询结果表最终的任何内容。如果在查询中不指定ORDER BY，和前面介绍的一样，就不能保证输出中行的任何顺序。如果需要确保查询结果的排序顺序，就必须在ORDER BY子句中增加相应的排序条件，就像前面排名函数的最后两个查询例子演示的那样。

　　如果在SELECT处理阶段指定了开窗函数，开窗计算会在DISTINCT子句（如果存在）之前进行处理。

　　作为对目前为止已经讨论过的所有子句的总结，以下列出了它们的逻辑处理顺序：

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT　　
• OVER
• DISTINCE
• TOP
• ORDER BY

　　你考虑过DISTINCT子句为什么要在SELECT中的开窗计算之后进行处理，不能在它前面处理吗？这里用一个例子来说明这个问题。OrderValues视图目前有830行，795个不同的值。考虑以下查询极其输出：

1 SELECTDISTINCT val,ROW_NUMBER() OVER(ORDERBY val) AS rownum
2 FROM Sales.OrderValues;

　　输出结果如下所示：

　　ROW_NUMBER函数是在DISTINCT子句之前处理的。首先，为OrderValues视图中的830行分配唯一的行号。接着再处理DISTINCT子句，所以这时不会有任何重复的行要删除。可以认为在同一SELECT子句中不能同时指定DISTINCT和ROW_NUMBER是一个最佳实践原则，因为DISTINCT子句在这种情况下不起任何作用。如果想要为795个唯一值分配行号，那就需要采用其他不同的解决办法。例如，因为GROUP BY阶段是在SELECT阶段之前处理的，可以使用以下查询：

1 SELECT val,ROW_NUMBER() OVER(ORDERBY val) AS rownum
2 FROM Sales.OrderValues
3 GROUPBY val;

　　这里，GROUP BY处理阶段为795个不重复的值生成了795个组，接着SELECT处理阶段为每个组中的价格值和行号（基于对val的排序）生成一行。