oracle 窗口函數 （keep）

看到很多人對于keep不理解，這里解釋一下！


Returns?the row ranked?first?using DENSE_RANK
2種取值：
DENSE_RANK?FIRST
DENSE_RANK?LAST

在keep?(DENSE_RANK?first?ORDER?BY?sl) 結果集中再取max、min的例子。


SQL>?select?*?from?test;

ID MC SL
-------------------- -------------------- -------------------
1?111?1
1?222?1
1?333?2
1?555?3
1?666?3
2?111?1
2?222?1
2?333?2
2?555?2

9?rows selected

SQL>?
SQL>?select?id,mc,sl,
2?min(mc)?keep?(DENSE_RANK?first?ORDER?BY?sl)?over(partition?by?id),
3?max(mc)?keep?(DENSE_RANK?last?ORDER?BY?sl)?over(partition?by?id)
4?from?test
5?;

ID MC SL?MIN(MC)KEEP(DENSE_RANKFIRSTORD?MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------
1?111?1?111?666
1?222?1?111?666
1?333?2?111?666
1?555?3?111?666
1?666?3?111?666
2?111?1?111?555
2?222?1?111?555
2?333?2?111?555
2?555?2?111?555

9?rows selected

SQL>

不要混淆keep內（first、last）外（min、max或者其他）：
min是可以對應last的
max是可以對應first的

SQL>?select?id,mc,sl,
2?min(mc)?keep?(DENSE_RANK?first?ORDER?BY?sl)?over(partition?by?id),
3?max(mc)?keep?(DENSE_RANK?first?ORDER?BY?sl)?over(partition?by?id),
4?min(mc)?keep?(DENSE_RANK?last?ORDER?BY?sl)?over(partition?by?id),
5?max(mc)?keep?(DENSE_RANK?last?ORDER?BY?sl)?over(partition?by?id)
6?from?test
7?;

ID MC SL?MIN(MC)KEEP(DENSE_RANKFIRSTORD?MAX(MC)KEEP(DENSE_RANKFIRSTORD?MIN(MC)KEEP(DENSE_RANKLASTORDEMAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1?111?1?111?222?555?666
1?222?1?111?222?555?666
1?333?2?111?222?555?666
1?555?3?111?222?555?666
1?666?3?111?222?555?666
2?111?1?111?222?333?555
2?222?1?111?222?333?555
2?333?2?111?222?333?555
2?555?2?111?222?333?555

9?rows selected


SQL>?select?id,mc,sl,
2?min(mc)?keep?(DENSE_RANK?first?ORDER?BY?sl)?over(partition?by?id),
3?max(mc)?keep?(DENSE_RANK?first?ORDER?BY?sl)?over(partition?by?id),
4?min(mc)?keep?(DENSE_RANK?last?ORDER?BY?sl)?over(partition?by?id),
5?max(mc)?keep?(DENSE_RANK?last?ORDER?BY?sl)?over(partition?by?id)
6?from?test