首页 > 代码库 > sql over开窗函数

sql over开窗函数

2024-11-04 07:32:39   204人阅读

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

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

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

如图：

 

技术分享

代码如下：

 

select   customerID,  SUM(totalPrice) over() as AllTotalPrice,  SUM(totalPrice) over(partition by customerID) as cusTotalPrice,  totalPrice  from OP_Order

 

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

 

 

 

技术分享

 

 代码如下

 with tabs as (  select  　　customerID, 　　SUM(totalPrice) over() as AllTotalPrice, 　　SUM(totalPrice) over(partition by customerID) as cusTotalPrice, 　　AVG(totalPrice) over(partition by customerID) as avgCusprice, 　　AVG(totalPrice) over() as avgTotalPrice, 　　totalPrice from OP_Order ) select 　　customerID, 　 AllTotalPrice,   cusTotalPrice,   totalPrice,   avgCusprice,   avgTotalPrice,   cusTotalPrice/AllTotalPrice as CusAllPercent,   totalPrice/cusTotalPrice as cusToPercent  from tabs

 

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  totalPrice14  from OP_Order15  )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

 

 总结：领用聚合函数再结合over子句，可以使表格向右扩张。并进行一些数据的统计。

 

参考： over子句

      ：over子句的详解 

 

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

sql over开窗函数


联系
我们