Oracle keep的用法

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。
从这个前提出发，我们可以看到其实这个目标通过一般的row_number分析函数也可以实现，即指定rn=1。但是，该函数无法实现同时获取最大和最小值。或者说用first_value和last_value，结合row_number实现，但是该种方式需要多次使用分析函数，而且还需要套一层SQL。于是出现了keep，该函数先可以参考如下链接：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions056.htm#SQLRF00641
具体例子，假设有如下数据:
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-687036/