分析函数——keep(dense_rank first/last)

来源于：http://blog.itpub.net/28929558/viewspace-1182183/

 

销售表:
SQL> select * from criss_sales where dept_id = 'D02' order by sale_date ;
 
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D02     2014/3/6    G00                500
D02     2014/3/6    G01                430
D02     2014/4/8    G02                100
D02     2014/4/27   G01                300
D02     2014/5/2    G03                900


此时有个新需求,希望查看部门 D02 内，销售记录时间最早，销售量最小的记录。


即希望得到这样的信息
D02     2014/3/6    G01                430


这样，就需要用keep(dense_rank first/last)来帮助处理


SQL> select
  2      dept_id
  3     ,min(sale_cnt)keep ( dense_rank first order by sale_date) min_early_date
  4  from criss_sales
  5  where dept_id = 'D02'
  6  group by dept_id
  7  ;
 
DEPT_ID  MIN_EARLY_DATE
------- ----------
D02            430


关于使用keep(dense_rank first/last) 会有一些疑问
1.keep(dense_rank first/last) 这句话的含义是什么?
2.为什么要使用min ?
3.为什么使用dense_rank ? rank不可以吗?

关于问题1:
  keep 字面意思就是'保持',也就是说保存满足keep()括号内条件的记录
       这里我们应该可以想象到，会有多条记录的情况，即存在多个last或first的情况)
  dense_rank 是排序策略
  first/last 是筛选策略
  
关于问题2:
使用min的原因是让最后得到的结果唯一，因为有时会存在多个last或first的情况。
例子中:
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D02     2014/3/6    G00                500
D02     2014/3/6    G01                430
这两条记录，同时满足keep的条件，通过 min(sale_cnt) 我们就得到了唯一的值 
   D02     2014/3/6    G01                430
  下面我们加上MAX 与 MIN对比下
  SQL> select
  2      dept_id
  3     ,min(sale_cnt)keep ( dense_rank first order by sale_date) min_early_date
  4     ,max(sale_cnt)keep ( dense_rank first order by sale_date) max_early_date
  5  from criss_sales
  6  where dept_id = 'D02'
  7  group by dept_id
  8  ;
 
DEPT_ID MIN_EARLY_DATE MAX_EARLY_DATE
------- -------------- --------------
D02                430            500

 很显然 max 取到了两条记录的较大值！

关于问题3:
先看一下换成rank的情况吧
select
    dept_id
   ,min(sale_cnt)keep ( rank first order by sale_date) min_early_date
   ,max(sale_cnt)keep ( rank first order by sale_date) max_early_date
from criss_sales
where dept_id = 'D02'
group by dept_id
 
ORA-02000: 缺失 DENSE_RANK 关键字 


换成rank以后直接报错了，至于原因，我的理解是rank不能表示记录排序的相对顺序
  例如: 记录   rank   dense_rank
          100    1     1
          100    1     1
          95     3     2
第三条记录与第一条和第二条记录的相对位置应该差1，但是用rank无法表示这一点。
感觉rank应该也能实现first/last的筛选，但是oracle似乎并没允许用rank这样去做。