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