sql over开窗函数,1.使用over子句与rows_number()以及聚合函数进行使用,可以进行编号以及各种操作。而且利用over子句的分组效率比group by子句的效率更高。
2.在订单表(order)中统计中,生成这么每一条记录都包含如下信息:“所有订单的总和”、“每一位客户的所有订单的总和”、”每一单的金额“ 关键点:使用了sum() over() 这个开窗函数 如图:
代码如下:
View Code
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)"。
代码如下 View Code
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子句进行操作。 如图:
具体代码如下: View Code
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
总结:领用聚合函数再结合over子句,可以使表格向右扩张。并进行一些数据的统计。
|
|