keep是Oracle下的另一個分析函數,他的用法不同于通過over關鍵字指定的分析函數,可以用于這樣一種場合下:取同一個分組下以某個字段排序後,對指定字段取最小或最大的那個值。
從這個前提出發,我們可以看到其實這個目标通過一般的row_number分析函數也可以實作,即指定rn=1。但是,該函數無法實作同時擷取最大和最小值。或者說用first_value和last_value,結合row_number實作,但是該種方式需要多次使用分析函數,而且還需要套一層SQL。于是出現了keep,該函數先可以參考如下連結:
具體例子,假設有如下資料:
SQL> with
2 tmp as (
3 select 'a,20110201,111' a from dual union all
4 select 'a,20110202,222' from dual union all
5 select 'a,20110302,333' from dual union all
6 select 'a,20110403,555' from dual union all
7 select 'a,20110403,666' from dual union all
8 select 'b,20110201,111' from dual union all
9 select 'b,20110202,222' from dual union all
10 select 'b,20110302,333' from dual union all
11 select 'b,20110402,555' from dual),
12 tt as (select pkg_dc_public.strpart(a,1) netno,
13 to_date(pkg_dc_public.strpart(a,2), 'yyyymmdd') sale_date,
14 pkg_dc_public.strpart(a,3) sale_count
15 from tmp)
16 select * from tt
17 /
NETNO SALE_DATE SALE_COUNT
-------- ----------- --------------
a 2011-2-1 111
a 2011-2-2 222
a 2011-3-2 333
a 2011-4-3 555
a 2011-4-3 666
b 2011-2-1 111
b 2011-2-2 222
b 2011-3-2 333
b 2011-4-2 555
9 rows selected
假設資料是各網點幾天的銷售資料
字段netno是網點辨別,這裡有a網點和b網點;
字段sale_count是銷售量;
字段sale_date是銷售量統計日期。
現要取a網點中:
最早那個月最小的銷量
最早那個月最大的銷量
最近一個月最小的銷量
最近一個月最大的銷量
SQL> with
2 tmp as (
3 select 'a,20110201,111' a from dual union all
4 select 'a,20110202,222' from dual union all
5 select 'a,20110302,333' from dual union all
6 select 'a,20110403,555' from dual union all
7 select 'a,20110403,666' from dual union all
8 select 'b,20110201,111' from dual union all
9 select 'b,20110202,222' from dual union all
10 select 'b,20110302,333' from dual union all
11 select 'b,20110402,555' from dual),
12 tt as (select pkg_dc_public.strpart(a,1) netno,
13 to_date(pkg_dc_public.strpart(a,2), 'yyyymmdd') sale_date,
14 pkg_dc_public.strpart(a,3) sale_count
15 from tmp)
16 --select * from tt
17 select netno,
18 min(sale_count)keep(dense_rank first order by trunc(sale_date,'mm')) fst_min,
19 min(sale_count)keep(dense_rank last order by trunc(sale_date,'mm')) lst_min,
20 max(sale_count)keep(dense_rank first order by trunc(sale_date,'mm')) fst_max,
21 max(sale_count)keep(dense_rank last order by trunc(sale_date,'mm')) lst_max
22 from tt t
23 group by netno
24 /
NETNO FST_MIN LST_MIN FST_MAX LST_MAX
----------- ------------ ------------ ------------ -----------
a 111 555 222 666
b 111 555 222 555
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12932950/viewspace-687036/,如需轉載,請注明出處,否則将追究法律責任。