Oracle開發之：視窗函式 （轉） rows between unbounded preceding and current row

張博208發表於2017-02-21
Oracle函式

目錄
=========================================
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行。


很明顯這個語句錯了。實際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 
          6        501485               3311970 
          7        606914               3918884 
          8        460520               4379404 
          9        392898               4772302 
         10        510117               5282419 
         11        532889               5815308 
         12        492458               6307766 

已選擇12行。


現在我們能得到滾動的銷售總額了！下面這個統計結果看起來更加完美，它展現了所有我們需要的資料：

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           sum ( sum (tot_sales))  over ( order   by   month 
   6          rows  between  unbounded preceding  and  unbounded following ) total_sales
   7      from  orders
   8     group   by   month ;

      MONTH  MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
-- -------- ----------- ------------------- ----------- 
          1        610697                610697       6307766 
          2        428676               1039373       6307766 
          3        637031               1676404       6307766 
          4        541146               2217550       6307766 
          5        592935               2810485       6307766 
          6        501485               3311970       6307766 
          7        606914               3918884       6307766 
          8        460520               4379404       6307766 
          9        392898               4772302       6307766 
         10        510117               5282419       6307766 
         11        532889               5815308       6307766 
         12        492458               6307766       6307766 

已選擇12行。

 

 

 


在一些銷售報表中我們會時常看到求平均值的需求，有時可能是針對全年的資料求平均值，有時會是針對截至到當前的所有資料求平均值。很簡單，只需要將： 
sum(sum(tot_sales)) 換成 avg(sum(tot_sales)) 即可。

四、視窗函式進階－根據時間範圍統計： 

前面我們說過，視窗函式不單適用於指定記錄集進行統計，而且也能適用於指定範圍進行統計的情況，例如下面這個SQL 語句就統計了當天銷售額和五天內的評價銷售額：

  select  trunc(order_dt)  day ,
              sum (sale_price) daily_sales,
              avg ( sum (sale_price))  over  ( order   by  trunc(order_dt)
                      range  between  interval  ' 2 '   day  preceding  
                                      and  interval  ' 2 '   day  following)  five_day_avg
    from  cust_order
  where  sale_price  is   not   null  
      and  order_dt  between  to_date( ' 01-jul-2001 ' , ' dd-mon-yyyy ' )
      and  to_date( ' 31-jul-2001 ' , ' dd-mon-yyyy ' )

 

為了對指定範圍進行統計， Oracle 使用關鍵字 range 、 interval 來指定一個範圍。上面的例子告訴 Oracle 查詢當前日期的前 2 天，後 2 天範圍內的記錄，並統計其銷售平均值。 

五、視窗函式進階－ first_value/last_value ： 

Oracle 提供了 2 個額外的函式： first_value 、 last_value ，用於在視窗記錄集中查詢第一條記錄和最後一條記錄。假設我們的報表需要顯示當前月、上一個月、後一個月的銷售情況，以及每 3

個月的銷售平均值，這兩個函式就可以派上用場了。

select   month ,
             first_value ( sum (tot_sales))  over  ( order   by   month  
                                    rows  between   1  preceding  and   1  following) prev_month,
 
              sum (tot_sales) monthly_sales,
 
             last_value ( sum (tot_sales))  over  ( order   by   month  
                                  rows  between   1  preceding  and   1  following) next_month,
 
              avg ( sum (tot_sales))  over  ( order   by   month  
                                 rows  between   1  preceding  and   1  following ) rolling_avg
     from  orders
  where   year   =   2001  
       and  region_id  =   6 
   group   by   month 
  order   by   month ;

 

首先我們來看 :rows between 1 preceding and 1 following 告訴 Oracle 在當前記錄的前一條、後一條範圍內查詢並統計，而 first_value 和 last_value 在這 3 條記錄中至分別找出第一條、第三條記錄，這樣我們就輕鬆地得到相鄰三個月的銷售記錄及平均值了！ 

六、視窗函式進階－比較相鄰 記錄： 

通過第五部分的學習，我們知道了如何利用視窗函式來顯示相鄰的記錄，現在假如我們想每次顯示當月的銷售額和上個月的銷售額，應該怎麼做呢？ 

從第五部分的介紹我們可以知道，利用 first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following)) 就可以做到了，其實 Oracle 還有一個更簡單的方式讓我們來比較 2 條記錄，它就是 lag函式。 

leg 函式類似於 preceding 和 following

子句，它能夠通過和當前記錄的相對位置而被應用，在比較同一個相鄰的記錄集內兩條相鄰記錄的時候特別有用。

select   month ,            
           sum (tot_sales) monthly_sales,
          lag( sum (tot_sales),  1 )  over  ( order   by   month )  prev_month_sales
    from  orders
  where   year   =   2001 
       and  region_id  =   6 
   group   by   month 
  order   by   month ;

 

lag(sum(tot_sales),1) 中的 1 表示以 1 月為基準。

相關文章