一、窗口函數(shù)簡(jiǎn)介：
到目前為止，我們所學(xué)習(xí)的分析函數(shù)在計(jì)算/統(tǒng)計(jì)一段時(shí)間內(nèi)的數(shù)據(jù)時(shí)特別有用，但是假如計(jì)算/統(tǒng)計(jì)需要隨著遍歷記錄集的每一條記錄而進(jìn)行呢？舉些例子來說：
①列出每月的訂單總額以及全年的訂單總額
②列出每月的訂單總額以及截至到當(dāng)前月的訂單總額
③列出上個(gè)月、當(dāng)月、下一月的訂單總額以及全年的訂單總額
④列出每天的營業(yè)額及一周來的總營業(yè)額
⑤列出每天的營業(yè)額及一周來每天的平均營業(yè)額
仔細(xì)回顧一下前面我們介紹到的分析函數(shù)，我們會(huì)發(fā)現(xiàn)這些需求和前面有一些不同：前面我們介紹的分析函數(shù)用于計(jì)算/統(tǒng)計(jì)一個(gè)明確的階段/記錄集，而這里有部分需求例如2，需要隨著遍歷記錄集的每一條記錄的同時(shí)進(jìn)行統(tǒng)計(jì)。
也即是說：統(tǒng)計(jì)不止發(fā)生一次，而是發(fā)生多次。統(tǒng)計(jì)不至發(fā)生在記錄集形成后，而是發(fā)生在記錄集形成的過程中。
這就是我們這次要介紹的窗口函數(shù)的應(yīng)用了。它適用于以下幾個(gè)場(chǎng)合：
①通過指定一批記錄：例如從當(dāng)前記錄開始直至某個(gè)部分的最后一條記錄結(jié)束
②通過指定一個(gè)時(shí)間間隔：例如在交易日之前的前30天
③通過指定一個(gè)范圍值：例如所有占到當(dāng)前交易量總額5%的記錄
二、窗口函數(shù)示例－全統(tǒng)計(jì)：
下面我們以需求：列出每月的訂單總額以及全年的訂單總額為例，來看看窗口函數(shù)的應(yīng)用。
【1】測(cè)試環(huán)境：
復(fù)制代碼 代碼如下:
SQL> desc orders;
名稱 是否為空? 類型
----------------------- -------- ----------------
MONTH NUMBER(2)
TOT_SALES NUMBER
SQL>
【2】測(cè)試數(shù)據(jù)：
復(fù)制代碼 代碼如下:
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】測(cè)試語句：
回憶一下前面《Oracle開發(fā)專題之：分析函數(shù)(OVER)》一文中，我們使用了sum(sum(tot_sales)) over (partition by region_id) 來統(tǒng)計(jì)每個(gè)分區(qū)的訂單總額。現(xiàn)在我們要統(tǒng)計(jì)的不單是每個(gè)分區(qū)，而是所有分區(qū)，partition by region_id在這里不起作用了。
Oracle為這種情況提供了一個(gè)子句：rows between ... preceding and ... following。從字面上猜測(cè)它的意思是：在XXX之前和XXX之后的所有記錄，實(shí)際情況如何讓我們通過示例來驗(yàn)證：
復(fù)制代碼 代碼如下:
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行。
綠色高亮處的代碼在這里發(fā)揮了關(guān)鍵作用，它告訴oracle統(tǒng)計(jì)從第一條記錄開始至最后一條記錄的每月銷售額。這個(gè)統(tǒng)計(jì)在記錄集形成的過程中執(zhí)行了12次，這時(shí)相當(dāng)費(fèi)時(shí)的！但至少我們解決了問題。
unbounded preceding and unbouned following的意思針對(duì)當(dāng)前所有記錄的前一條、后一條記錄，也就是表中的所有記錄。那么假如我們直接指定從第一條記錄開始直至末尾呢？看看下面的結(jié)果：
復(fù)制代碼 代碼如下:
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行。
很明顯這個(gè)語句錯(cuò)了。實(shí)際1在這里不是從第1條記錄開始的意思，而是指當(dāng)前記錄的前一條記錄。preceding前面的修飾符是告訴窗口函數(shù)執(zhí)行時(shí)參考的記錄數(shù)，如同unbounded就是告訴oracle不管當(dāng)前記錄是第幾條，只要前面有多少條記錄，都列入統(tǒng)計(jì)的范圍。
三、窗口函數(shù)進(jìn)階－滾動(dòng)統(tǒng)計(jì)(累積/均值)：
考慮前面提到的第2個(gè)需求：列出每月的訂單總額以及截至到當(dāng)前月的訂單總額。也就是說2月份的記錄要顯示當(dāng)月的訂單總額和1,2月份訂單總額的和。3月份要顯示當(dāng)月的訂單總額和1,2,3月份訂單總額的和，依此類推。
很明顯這個(gè)需求需要在統(tǒng)計(jì)第N月的訂單總額時(shí)，還要再統(tǒng)計(jì)這N個(gè)月來的訂單總額之和。想想上面的語句，假如我們能夠把a(bǔ)nd unbounded following換成代表當(dāng)前月份的邏輯多好??！很幸運(yùn)的是Oracle考慮到了我們這個(gè)需求，為此我們只需要將語句稍微改成: curreent row就可以了。
復(fù)制代碼 代碼如下:
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行。
現(xiàn)在我們能得到滾動(dòng)的銷售總額了！下面這個(gè)統(tǒng)計(jì)結(jié)果看起來更加完美，它展現(xiàn)了所有我們需要的數(shù)據(jù)：
復(fù)制代碼 代碼如下:
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行。
在一些銷售報(bào)表中我們會(huì)時(shí)?？吹角笃骄档男枨?，有時(shí)可能是針對(duì)全年的數(shù)據(jù)求平均值，有時(shí)會(huì)是針對(duì)截至到當(dāng)前的所有數(shù)據(jù)求平均值。很簡(jiǎn)單，只需要將：
sum(sum(tot_sales))換成avg(sum(tot_sales))即可。
四、窗口函數(shù)進(jìn)階－根據(jù)時(shí)間范圍統(tǒng)計(jì)：
前面我們說過，窗口函數(shù)不單適用于指定記錄集進(jìn)行統(tǒng)計(jì)，而且也能適用于指定范圍進(jìn)行統(tǒng)計(jì)的情況，例如下面這個(gè)SQL語句就統(tǒng)計(jì)了當(dāng)天銷售額和五天內(nèi)的評(píng)價(jià)銷售額：
復(fù)制代碼 代碼如下:
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')
為了對(duì)指定范圍進(jìn)行統(tǒng)計(jì)，Oracle使用關(guān)鍵字range、interval來指定一個(gè)范圍。上面的例子告訴Oracle查找當(dāng)前日期的前2天，后2天范圍內(nèi)的記錄，并統(tǒng)計(jì)其銷售平均值。
五、窗口函數(shù)進(jìn)階－first_value/last_value：
Oracle提供了2個(gè)額外的函數(shù)：first_value、last_value，用于在窗口記錄集中查找第一條記錄和最后一條記錄。假設(shè)我們的報(bào)表需要顯示當(dāng)前月、上一個(gè)月、后一個(gè)月的銷售情況，以及每3個(gè)月的銷售平均值，這兩個(gè)函數(shù)就可以派上用場(chǎng)了。
復(fù)制代碼 代碼如下:
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在當(dāng)前記錄的前一條、后一條范圍內(nèi)查找并統(tǒng)計(jì)，而first_value和last_value在這3條記錄中至分別找出第一條、第三條記錄，這樣我們就輕松地得到相鄰三個(gè)月的銷售記錄及平均值了！
六、窗口函數(shù)進(jìn)階－比較相鄰記錄：
通過第五部分的學(xué)習(xí)，我們知道了如何利用窗口函數(shù)來顯示相鄰的記錄，現(xiàn)在假如我們想每次顯示當(dāng)月的銷售額和上個(gè)月的銷售額，應(yīng)該怎么做呢？
從第五部分的介紹我們可以知道，利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了，其實(shí)Oracle還有一個(gè)更簡(jiǎn)單的方式讓我們來比較2條記錄，它就是lag函數(shù)。
leg函數(shù)類似于preceding和following子句，它能夠通過和當(dāng)前記錄的相對(duì)位置而被應(yīng)用，在比較同一個(gè)相鄰的記錄集內(nèi)兩條相鄰記錄的時(shí)候特別有用。
復(fù)制代碼 代碼如下:
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月為基準(zhǔn)。
以上就是Oracle窗口函數(shù)用法的全部內(nèi)容，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
您可能感興趣的文章:- oracle 存儲(chǔ)過程和函數(shù)例子
- ORACLE常用數(shù)值函數(shù)、轉(zhuǎn)換函數(shù)、字符串函數(shù)
- Oracle中instr函數(shù)使用方法
- 給Oracle添加split和splitstr函數(shù)的方法
- oracle to_char函數(shù)將number轉(zhuǎn)成string
- Oracle round()函數(shù)與trunc()函數(shù)區(qū)別介紹
- Oracle隨機(jī)函數(shù)之dbms_random使用詳解
- ORACLE時(shí)間函數(shù)(SYSDATE)深入理解
- oracle中的trim函數(shù)使用介紹
- Oracle 函數(shù)大全
- Oracle中rank,over partition函數(shù)的使用方法
- Oracle開發(fā)之分析函數(shù)簡(jiǎn)介Over用法
- Oracle開發(fā)之分析函數(shù)(Rank, Dense_rank, row_number)
- Oracle開發(fā)之分析函數(shù)(Top/Bottom N、First/Last、NTile)
