程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle開發之窗口函數,oracle窗口函數

Oracle開發之窗口函數,oracle窗口函數

編輯:Oracle教程

Oracle開發之窗口函數,oracle窗口函數


一、窗口函數簡介:

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

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

仔細回顧一下前面我們介紹到的分析函數,我們會發現這些需求和前面有一些不同:前面我們介紹的分析函數用於計算/統計一個明確的階段/記錄集,而這裡有部分需求例如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,
         sum(tot_sales) month_sales,
         sum(sum(tot_sales)) over (order by month
            rows between unbounded preceding and unbounded following) total_sales
    from orders
   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,
         sum(tot_sales) month_sales,
         sum(sum(tot_sales)) over (order by month
            rows between 1 preceding and unbounded following) all_sales
    from orders
   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,
         sum(tot_sales) month_sales,
         sum(sum(tot_sales)) over(order by month
           rows between unbounded preceding and current row) current_total_sales
    from orders
   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,
         sum(tot_sales) month_sales,
         sum(sum(tot_sales)) over(order by month
         rows between unbounded preceding and current row) current_total_sales,
         sum(sum(tot_sales)) over(order by month
         rows between unbounded preceding and unbounded following) total_sales
    from orders
   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月為基准。

以上就是Oracle窗口函數用法的全部內容,希望能給大家一個參考,也希望大家多多支持幫客之家。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved