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

銷售表:
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這樣去做。