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/,如需转载,请注明出处,否则将追究法律责任。