first_value()和last_value()字面意思已經很直觀了，取首尾記錄值。
例:查詢部門最早發(fā)生銷售記錄日期和最近發(fā)生的銷售記錄日期

selectdept_id,sale_date,goods_type,sale_cnt,first_value(sale_date) over (partition by dept_id order by sale_date) first_value,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value from criss_sales;

看結果first_value()很直觀，不用多解釋
但是，last_value()值，部門D01不是應該為2014/6/12，部門D02不是應該為2014/5/2嗎?為什么會每條記錄都不一樣?
可以這樣去理解:last_value()默認統(tǒng)計范圍是 rows between unbounded preceding and current row
驗證一下:

selectdept_id,sale_date,goods_type,sale_cnt,first_value(sale_date) over (partition by dept_id order by sale_date) first_value,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value,last_value(sale_date) over (partition by dept_id order by sale_date rows between unbounded preceding and unbounded following) last_value_all from criss_sales;

全統(tǒng)計的情況下得到的last_value()值，部門D01為2014/6/12，部門D02為2014/5/2。

---

Oracle開發(fā)之：窗口函數(shù) （轉） rows between unbounded preceding and current row

一、窗口函數(shù)簡介：

到目前為止，我們所學習的分析函數(shù)在計算/統(tǒng)計一段時間內的數(shù)據(jù)時特別有用，但是假如計算/統(tǒng)計需要隨著遍歷記錄集的每一條記錄而進行呢？舉些例子來說：

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

仔細回顧一下前面我們介紹到的分析函數(shù)，我們會發(fā)現(xiàn)這些需求和前面有一些不同：前面我們介紹的分析函數(shù)用于計算/統(tǒng)計一個明確的階段/記錄集，而這里有部分需求例如2，需要隨著遍歷記錄集的每一條記錄的同時進行統(tǒng)計。

也即是說：統(tǒng)計不止發(fā)生一次，而是發(fā)生多次。統(tǒng)計不至發(fā)生在記錄集形成后，而是發(fā)生在記錄集形成的過程中。

這就是我們這次要介紹的窗口函數(shù)的應用了。它適用于以下幾個場合：

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

二、窗口函數(shù)示例－全統(tǒng)計：

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

【1】測試環(huán)境：

SQL> desc orders;名稱 是否為空? 類型----------------------- -------- ----------------MONTH NUMBER(2)TOT_SALES NUMBERSQL>

【2】測試數(shù)據(jù)：

SQL> select * from orders;MONTH TOT_SALES ---------- ----------1 6106972 4286763 6370314 5411465 5929356 5014857 6069148 4605209 39289810 51011711 53288912 492458

已選擇12行。

【3】測試語句：

回憶一下前面《Oracle開發(fā)專題之：分析函數(shù)(OVER)》一文中，我們使用了sum(sum(tot_sales)) over (partition by region_id) 來統(tǒng)計每個分區(qū)的訂單總額?，F(xiàn)在我們要統(tǒng)計的不單是每個分區(qū)，而是所有分區(qū)，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 month4 rows between unbounded preceding and unbounded following) total_sales5 from orders6 group by month;MONTH MONTH_SALES TOTAL_SALES ---------- ----------- -----------1 610697 63077662 428676 63077663 637031 63077664 541146 63077665 592935 63077666 501485 63077667 606914 63077668 460520 63077669 392898 630776610 510117 630776611 532889 630776612 492458 6307766

已選擇12行。

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

unbounded preceding and unbouned following的意思針對當前所有記錄的前一條、后一條記錄，也就是表中的所有記錄。那么假如我們直接指定從第一條記錄開始直至末尾呢？看看下面的結果：

SQL> select month,2 sum(tot_sales) month_sales,3 sum(sum(tot_sales)) over (order by month4 rows between 1 preceding and unbounded following) all_sales5 from orders6 group by month;MONTH MONTH_SALES ALL_SALES ---------- ----------- ----------1 610697 63077662 428676 63077663 637031 56970694 541146 52683935 592935 46313626 501485 40902167 606914 34972818 460520 29957969 392898 238888210 510117 192836211 532889 153546412 492458 1025347

已選擇12行。

很明顯這個語句錯了。實際1在這里不是從第1條記錄開始的意思，而是指當前記錄的前一條記錄。preceding前面的修飾符是告訴窗口函數(shù)執(zhí)行時參考的記錄數(shù)，如同unbounded就是告訴oracle不管當前記錄是第幾條，只要前面有多少條記錄，都列入統(tǒng)計的范圍。

三、窗口函數(shù)進階－滾動統(tǒng)計(累積/均值)：

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

很明顯這個需求需要在統(tǒng)計第N月的訂單總額時，還要再統(tǒng)計這N個月來的訂單總額之和。想想上面的語句，假如我們能夠把and unbounded following換成代表當前月份的邏輯多好啊！很幸運的是Oracle考慮到了我們這個需求，為此我們只需要將語句稍微改成: curreent row就可以了。

SQL> select month,2 sum(tot_sales) month_sales,3 sum(sum(tot_sales)) over(order by month4 rows between unbounded preceding and current row) current_total_sales5 from orders6 group by month;MONTH MONTH_SALES CURRENT_TOTAL_SALES ---------- ----------- -------------------1 610697 6106972 428676 10393733 637031 16764044 541146 22175505 592935 28104856 501485 33119707 606914 39188848 460520 43794049 392898 477230210 510117 528241911 532889 581530812 492458 6307766

已選擇12行。

現(xiàn)在我們能得到滾動的銷售總額了！下面這個統(tǒng)計結果看起來更加完美，它展現(xiàn)了所有我們需要的數(shù)據(jù)：

SQL> select month,2 sum(tot_sales) month_sales,3 sum(sum(tot_sales)) over(order by month4 rows between unbounded preceding and current row) current_total_sales,5 sum(sum(tot_sales)) over(order by month6 rows between unbounded preceding and unbounded following) total_sales7 from orders8 group by month;MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES ---------- ----------- ------------------- -----------1 610697 610697 63077662 428676 1039373 63077663 637031 1676404 63077664 541146 2217550 63077665 592935 2810485 63077666 501485 3311970 63077667 606914 3918884 63077668 460520 4379404 63077669 392898 4772302 630776610 510117 5282419 630776611 532889 5815308 630776612 492458 6307766 6307766

已選擇12行。

在一些銷售報表中我們會時?？吹角笃骄档男枨?#xff0c;有時可能是針對全年的數(shù)據(jù)求平均值，有時會是針對截至到當前的所有數(shù)據(jù)求平均值。很簡單，只需要將：
sum(sum(tot_sales))換成avg(sum(tot_sales))即可。

四、窗口函數(shù)進階－根據(jù)時間范圍統(tǒng)計：

前面我們說過，窗口函數(shù)不單適用于指定記錄集進行統(tǒng)計，而且也能適用于指定范圍進行統(tǒng)計的情況，例如下面這個SQL語句就統(tǒng)計了當天銷售額和五天內的評價銷售額：

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_avgfrom cust_orderwhere 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')

為了對指定范圍進行統(tǒng)計，Oracle使用關鍵字range、interval來指定一個范圍。上面的例子告訴Oracle查找當前日期的前2天，后2天范圍內的記錄，并統(tǒng)計其銷售平均值。

五、窗口函數(shù)進階－first_value/last_value：

Oracle提供了2個額外的函數(shù)：first_value、last_value，用于在窗口記錄集中查找第一條記錄和最后一條記錄。假設我們的報表需要顯示當前月、上一個月、后一個月的銷售情況，以及每3個月的銷售平均值，這兩個函數(shù)就可以派上用場了。

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_avgfrom orderswhere year = 2001 and region_id = 6group by monthorder by month;

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

六、窗口函數(shù)進階－比較相鄰記錄：

通過第五部分的學習，我們知道了如何利用窗口函數(shù)來顯示相鄰的記錄，現(xiàn)在假如我們想每次顯示當月的銷售額和上個月的銷售額，應該怎么做呢？

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

leg函數(shù)類似于preceding和following

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

select month, sum(tot_sales) monthly_sales,lag(sum(tot_sales), 1) over (order by month) prev_month_salesfrom orderswhere year = 2001and region_id = 6group by monthorder by month;

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

總結

以上是生活随笔為你收集整理的Oracle分析函数-first_value()和last_value()的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。