SQL 开窗函数over 与sum(),AVG(),MIN(),MAX()

1.使用over子句与rows_number()以及聚合函数进行使用，可以进行编号以及各种操作。而且利用over子句的分组效率比group by子句的效率更高。

 

 2.在订单表（order）中统计中，生成这么每一条记录都包含如下信息：“所有订单的总和”、“每一位客户的所有订单的总和”、”每一单的金额“

关键点：使用了sum() over() 这个开窗函数 

如图：

 

代码如下：

 

1   select 
2   customerID,
3   SUM(totalPrice) over()  as  AllTotalPrice,
4   SUM(totalPrice) over(partition by customerID)  as  cusTotalPrice,
5   totalPrice
6   from OP_Order

 

 

 3.在订单表（order）中统计中，生成这么每一条记录都包含如下信息：“所有订单的总和（AllTotalPrice）”、“每一位客户的所有订单的总(cusTotalPrice)”、”每一单的金额(totalPrice)“，”每一个客户订单的平均金额(avgCusprice)“，”所有客户的所有订单的平均金额(avgTotalPrice)“，"客户所购的总额在所有的订单中总额的比例(CusAllPercent)","每一订单的金额在每一位客户总额中所占的比例(cusToPercent)"。

 

 

 

 

 

 代码如下

 1  with tabs  as
 2   (
 3    select 
 4   customerID,
 5   SUM(totalPrice) over()  as  AllTotalPrice,
 6   SUM(totalPrice) over(partition by customerID)  as  cusTotalPrice,
 7   AVG(totalPrice) over(partition by customerID)  as  avgCusprice,
 8   AVG(totalPrice) over()  as  avgTotalPrice,
 9   totalPrice
10   from OP_Order
11   )
12   select 
13   customerID,
14   AllTotalPrice,
15   cusTotalPrice,
16   totalPrice,
17  avgCusprice,
18  avgTotalPrice,
19   cusTotalPrice / AllTotalPrice  as  CusAllPercent,
20   totalPrice / cusTotalPrice  as  cusToPercent 
21   from tabs
22   

 

 

4.在订单表（order）中统计中，生成这么每一条记录都包含如下信息：“所有订单的总和（AllTotalPrice）”、“每一位客户的所有订单 的总(cusTotalPrice)”、”每一单的金额(totalPrice)“，”每一个客户订单的平均金额(avgCusprice)“，”所有客 户的所有订单的平均金额(avgTotalPrice)“,"订单金额最小值（MinTotalPrice）","客户订单金额最小值(MinCusPrice)","订单金额最大值(MaxTotalPrice)","客户订单金额最大值（MaxCusPrice）"，"客户所购的总额在所有的订单中总额的比例(CusAllPercent)","每一订单的金 额在每一位客户总额中所占的比例(cusToPercent)"。

 

关键：利用over子句进行操作。

 如图：

 

 具体代码如下：

 1  with tabs  as
 2   (
 3    select 
 4   customerID,
 5   SUM(totalPrice) over()  as  AllTotalPrice,
 6   SUM(totalPrice) over(partition by customerID)  as  cusTotalPrice,
 7   AVG(totalPrice) over(partition by customerID)  as  avgCusprice,
 8   AVG(totalPrice) over()  as  avgTotalPrice,
 9   MIN(totalPrice) over()  as  MinTotalPrice,
10   MIN(totalPrice) over(partition by customerID)  as  MinCusPrice,
11   MAX(totalPrice) over()  as  MaxTotalPrice,
12   MAX(totalPrice) over(partition by customerID)  as  MaxCusPrice,
13   totalPrice
14   from OP_Order
15   )
16   select 
17   customerID,
18   AllTotalPrice,
19   cusTotalPrice,
20   totalPrice,
21   avgCusprice,
22   avgTotalPrice,
23   MinTotalPrice,
24   MinCusPrice,
25   MaxTotalPrice,
26   MaxCusPrice,
27   cusTotalPrice / AllTotalPrice  as  CusAllPercent,
28   totalPrice / cusTotalPrice  as  cusToPercent 
29   from tabs
30   

转自：http://www.cnblogs.com/85538649/archive/2011/08/13/2137370.html