oracle的視窗windowing函數__靜態函數

目錄
=========================================
1.視窗函數簡介
2.視窗函數樣本-全統計
3.視窗函數進階-滾動統計(累積/均值)
4.視窗函數進階-根據時間範圍統計
5.視窗函數進階-first_value/last_value
6.視窗函數進階-比較相鄰記錄

一、視窗函數簡介：

到目前為止，我們所學習的分析函數在計算/統計一段時間內的資料時特別有用，但是假如計算/統計需要隨著遍曆記錄集的每一條記錄而進行呢。舉些例子來說：

①列出每月的訂單總額以及全年的訂單總額
②列出每月的訂單總額以及截至到當前月的訂單總額
③列出上個月、當月、下一月的訂單總額以及全年的訂單總額
④列出每天的營業額及一周來的總營業額
⑤列出每天的營業額及一周來每天的平均營業額

仔細回顧一下前面我們介紹到的分析函數，我們會發現這些需求和前面有一些不同：前面我們介紹的分析函數用於計算/統計一個明確的階段/記錄集，而這裡有部分需求例如2，需要隨著遍曆記錄集的每一條記錄的同時進行統計。

也即是說：統計不止發生一次，而是發生多次。統計不至發生在記錄集形成後，而是發生在記錄集形成的過程中。

這就是我們這次要介紹的視窗函數的應用了。它適用於以下幾個場合：

①通過指定一批記錄：例如從目前記錄開始直至某個部分的最後一條記錄結束
②通過指定一個時間間隔：例如在交易日之前的前30天
③通過指定一個範圍值：例如所有佔到當前交易量總額5%的記錄

二、視窗函數樣本－全統計：

下面我們以需求：列出每月的訂單總額以及全年的訂單總額為例，來看看視窗函數的應用。

【1】測試環境： SQL >   desc  orders;
 名稱                    是否為空白? 類型
  -- --------------------- -------- ----------------
  MONTH                              NUMBER ( 2 )
 TOT_SALES                     NUMBER

SQL >  

【2】測試資料： SQL >   select   *   from  orders;

      MONTH   TOT_SALES
-- -------- ----------
          1       610697
          2       428676
          3       637031
          4       541146
          5       592935
          6       501485
          7       606914
          8       460520
          9       392898
         10       510117
         11       532889
         12       492458

已選擇12行。

【3】測試語句：

回憶一下前面《Oracle開發專題之：分析函數(OVER)》一文中，我們使用了sum(sum(tot_sales)) over (partition by region_id) 來統計每個分區的訂單總額。現在我們要統計的不單是每個分區，而是所有分區，partition by region_id在這裡不起作用了。

Oracle為這種情況提供了一個子句：rows between ... preceding and ... following。從字面上猜測它的意思是：在XXX之前和XXX之後的所有記錄，實際情況如何讓我們通過樣本來驗證： SQL >   select   month ,
   2           sum (tot_sales) month_sales,
   3           sum ( sum (tot_sales))  over  ( order   by   month
   4             rows between unbounded preceding and unbounded following ) total_sales
   5      from  orders
   6     group   by   month ;

      MONTH  MONTH_SALES TOTAL_SALES
-- -------- ----------- -----------
          1        610697       6307766
          2        428676       6307766
          3        637031       6307766
          4        541146       6307766
          5        592935       6307766
          6        501485       6307766
          7        606914       6307766
          8        460520       6307766
          9        392898       6307766
         10        510117       6307766
         11        532889       6307766
         12        492458       6307766

已選擇12行。

綠色高亮處的代碼在這裡發揮了關鍵作用，它告訴oracle統計從第一條記錄開始至最後一條記錄的每月銷售額。這個統計在記錄集形成的過程中執行了12次，這時相當費時的。但至少我們解決了問題。

unbounded preceding and unbouned following的意思針對當前所有記錄的前一條、後一條記錄，也就是表中的所有記錄。那麼假如我們直接指定從第一條記錄開始直至末尾呢。看看下面的結果： SQL >   select   month ,
   2           sum (tot_sales) month_sales,
   3           sum ( sum (tot_sales))  over  ( order   by   month
   4             rows  between   1  preceding  and  unbounded  following) all_sales
   5      from  orders
   6     group   by   month ;

      MONTH  MONTH_SALES  ALL_SALES
-- -------- ----------- ----------
          1        610697      6307766
          2        428676      6307766
          3        637031      5697069
          4        541146      5268393
          5        592935      4631362
          6        501485      4090216
          7        606914      3497281
          8        460520      2995796
          9        392898      2388882
         10        510117      1928362
         11        532889      1535464
         12        492458      1025347

已選擇12行。

（可以明顯的看到，他的sum(sum(total_sales))是從目前記錄的前一條到底下所有記錄的匯總值）
很明顯這個語句錯了。實際1在這裡不是從第1條記錄開始的意思，而是指目前記錄的前一條記錄。preceding前面的修飾符是告訴視窗函數執行時參考的記錄數，如同unbounded就是告訴oracle不管目前記錄是第幾條，只要前面有多少條記錄，都列入統計的範圍。

三、視窗函數進階－滾動統計(累積/均值)：

考慮前面提到的第2個需求：列出每月的訂單總額以及截至到當前月的訂單總額。也就是說2月份的記錄要顯示當月的訂單總額和1,2月份訂單總額的和。3月份要顯示當月的訂單總額和1,2,3月份訂單總額的和，依此類推。

很明顯這個需求需要在統計第N月的訂單總額時，還要再統計這N個月來的訂單總額之和。想想上面的語句，假如我們能夠把and unbounded following換成代表當前月份的邏輯多好啊。很幸運的是Oracle考慮到了我們這個需求，為此我們只需要將語句稍微改成: curreent row就可以了。 SQL >   select   month ,
   2           sum (tot_sales) month_sales,
   3           sum ( sum (tot_sales))  over ( order   by   month
   4            rows between unbounded preceding and current row ) current_total_sales
   5      from  orders
   6     group   by   month ;

      MONTH  MONTH_SALES CURRENT_TOTAL_SALES
-- -------- ----------- -------------------
          1        610697                610697
          2        428676               1039373
          3        637031               1676404
          4        541146               2217550
          5        592935               2810485
   &nbs