黑马程序员技术交流社区

标题: OVER子句中聚合函数的用法 [打印本页]

作者: 叶彦召 时间: 2012-2-27 20:43
标题: OVER子句中聚合函数的用法
OVER子句用于为行定义一个窗口（即用户定义的一组行），以便进行特定的运算。例如，聚合函数和排名函数都支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也成为开窗函数。

聚合开窗函数使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用GROUP BY子句提供上下文。这样就不必对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。带有空的圆括号的OVER子句会提供所有行的计算，这里的“所有行”并不一定是from子句中出现的那些表中的那些行。只有在select和order by处理阶段才允许使用over子句。在进行查询的时候指定了SUM（val） OVER（）表达式，这个函数就会对select阶段操作的所有行计算其价格。如果想对行进行限制或分区，则可以使用partition by子句。一下例子查询返回OrderValue的所有行，并演示了同时使用分区和非分区表达式的方法，此时每一行除了基本列之外，查询还会返回所有行的总价格和当前客户的总价格。

select orderid,custid,val,

SUM(val) over() as totalvalue,

SUM(val) OVER(partition by custid) as custtotalvalue

from OrderValue;

这个查询结果如下：

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

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

select orderid,custid,val,

100.*val/SUM(val) over() as totalvalue,

100.*val/SUM(val) OVER(partition by custid) as custtotalvalue

from OrderValue;

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

这个查询结果如下：

OVER子句在order by处理阶段的聚合函数的应用和在select处理阶段聚合函数的应用类似！

作者: 赵国梁 时间: 2012-2-27 23:06
学习学习，讲解的很详细。

作者: 成刚 时间: 2012-2-28 09:10
飘过停留，学习中…… ，多谢共享！

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2