SQL OVER开窗函数

 SQL OVER开窗函数

 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子句进行操作。

 如图：

 

 

 

 具体代码如下：

 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,
  MIN(totalPrice) over() as MinTotalPrice,
  MIN(totalPrice) over(partition by customerID) as MinCusPrice,
  MAX(totalPrice) over() as MaxTotalPrice,
  MAX(totalPrice) over(partition by customerID) as MaxCusPrice,
  totalPrice
  from OP_Order
  )
  select 
  customerID,
  AllTotalPrice,
  cusTotalPrice,
  totalPrice,
  avgCusprice,
  avgTotalPrice,
  MinTotalPrice,
  MinCusPrice,
  MaxTotalPrice,
  MaxCusPrice,
  cusTotalPrice/AllTotalPrice as CusAllPercent,
  totalPrice/cusTotalPrice as cusToPercent 
  from tabs

　　

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