oracle keep用法

转自http://blog.csdn.net/a9529lty/article/details/6534564
看到很多人对于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_RANKLASTORDE MAX (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


min (mc) keep (DENSE_RANK first ORDER BY sl) over (partition by id)：id等于1的数量最小的（DENSE_RANK first ）为
1 111 1
1 222 1
在这个结果中取min(mc) 就是111
max (mc) keep (DENSE_RANK first ORDER BY sl) over (partition by id)
取max(mc) 就是222；
min (mc) keep (DENSE_RANK last ORDER BY sl) over (partition by id)：id等于1的数量最大的（DENSE_RANK first ）为
1 555 3
1 666 3
在这个结果中取min(mc) 就是555，取max(mc)就是666


实验
以下转自ITPUB
2011-7-16 分析函数 FIRST 关键字
作者：mentzel.iudith
难度：中

在我们的HR（人力资源）应用中我们有如下的员工表：
CREATE TABLE plch_employees
(
   employee_id  INTEGER PRIMARY KEY
, last_name    VARCHAR2(100)
, salary       NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
     VALUES (100, 'Jobs', 1000);

   INSERT INTO plch_employees
     VALUES (200, 'Ellison', 1000);

   INSERT INTO plch_employees
     VALUES (300, 'Gates', 1000);

   COMMIT;
END;
/

我们还有一张日志表，记录了每次员工的薪资调整：
CREATE TABLE plch_emp_log
(
   employee_id  INTEGER  REFERENCES plch_employees (employee_id)
, log_date     DATE
, old_salary   NUMBER
, new_salary   NUMBER
, CONSTRAINT plch_emp_log_pk PRIMARY KEY (employee_id, log_date)
)
/

BEGIN
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-01-10','DD-MM-RR'), 1000, 2000);
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('18-01-10','DD-MM-RR'), 2000, 1800);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-01-10','DD-MM-RR'), 1000, 1600);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-03-10','DD-MM-RR'), 1600, 2500);

   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('01-04-10','DD-MM-RR'), 1000, 2000);
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('05-04-10','DD-MM-RR'), 1800, 1900);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('10-05-10','DD-MM-RR'), 2500, 2300);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('15-05-10','DD-MM-RR'), 2000, 3000);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-09-10','DD-MM-RR'), 1900, 1500);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-09-10','DD-MM-RR'), 2300, 3000);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('10-10-10','DD-MM-RR'), 3000, 2700);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('05-01-11','DD-MM-RR'), 1500, 2500);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('10-02-11','DD-MM-RR'), 3000, 2000);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('20-02-11','DD-MM-RR'), 2700, 2500);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-04-11','DD-MM-RR'), 2500, 2200);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-06-11','DD-MM-RR'), 2000, 2800);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('15-09-11','DD-MM-RR'), 2500, 2900);

   COMMIT;
END;
/

我们决定实现一个视图，从而使得对这个视图的简单查询（只有过滤条件，没有连接、聚合、子查询等）能够回答诸如此类的需求：

返回一个员工列表，他们的工资在一个指定日期区间发生变动，要求显示每个员工在此区间的第一个和最后一个工资，这个区间可能是一年，一季度，或一个月。

这里有些例子：

1. 某一年，比如 2010
PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010             100          1000         1500
2010             200          1000         3000
2010             300          1000         2700

2. 某一季度，比如 2010 第一季度
PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010-1           100          1000         1800
2010-1           200          1000         2500

3. 某个月，比如 2010-01
PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010-01          100          1000         1800
2010-01          200          1000         1600

哪些选项可用来取代下列视图定义中的 /*FIRST_SALARY*/ 和 /*LAST_SALARY*/，使得这个视图可用来回答上述问题？

CREATE OR REPLACE VIEW plch_emp_salary_change
AS
SELECT
       TO_CHAR(log_date,'YYYY')  year,
       TO_CHAR(log_date,'Q')     quarter,
       TO_CHAR(log_date,'MM')    MMth,
       employee_id,
       /*FIRST_SALARY*/    first_salary,
       /*LAST_SALARY*/     last_salary,
       GROUPING_ID(TO_CHAR(log_date,'YYYY'),
                   TO_CHAR(log_date,'Q'),
                   TO_CHAR(log_date,'MM'))   gid
FROM
      plch_emp_log
GROUP BY
      ROLLUP(TO_CHAR(log_date,'YYYY'),
             TO_CHAR(log_date,'Q'),
             TO_CHAR(log_date,'MM')),
      employee_id
/

(A)
MIN(old_salary)
MAX(new_salary)

(B)
FIRST_VALUE(old_salary)
LAST_VALUE(new_salary)

(C)
MIN(old_salary) KEEP(DENSE_RANK FIRST ORDER BY log_date)
MIN(new_salary) KEEP(DENSE_RANK LAST  ORDER BY log_date)

CREATE OR REPLACE VIEW plch_emp_salary_change
AS
SELECT
       TO_CHAR(log_date,'YYYY')  year,
       TO_CHAR(log_date,'Q')     quarter,
       TO_CHAR(log_date,'MM')    MMth,
       employee_id,
       min(old_salary) keep(dense_rank first order by log_date) first_salary,
       min(new_salary) keep(dense_rank first order by log_date)last_salary,
       GROUPING_ID(TO_CHAR(log_date,'YYYY'),
                   TO_CHAR(log_date,'Q'),
                   TO_CHAR(log_date,'MM'))   gid
FROM
      plch_emp_log
GROUP BY
      ROLLUP(TO_CHAR(log_date,'YYYY'),
             TO_CHAR(log_date,'Q'),
             TO_CHAR(log_date,'MM')),
      employee_id
/
(D)
FIRST_VALUE(old_salary)   OVER(PARTITION BY employee_id
                               ORDER BY log_date)    
LAST_VALUE(new_salary)    OVER(PARTITION BY employee_id
                               ORDER BY log_date)
                              
(E)
MIN(old_salary)    OVER(PARTITION BY employee_id
                        ORDER BY log_date)
MAX(new_salary)    OVER(PARTITION BY employee_id
                        ORDER BY log_date)
                       
(F)
TO_NUMBER(SUBSTR(MIN(TO_CHAR(log_date,'YYYYMMDDHH24MISS')||old_salary),15))
TO_NUMBER(SUBSTR(MAX(TO_CHAR(log_date,'YYYYMMDDHH24MISS')||new_salary),15))

注意分组的语义概念以及要求的最开始的sal和最后的sal,这个过程中可能降薪
所以答案和明显了，2个

2011-7-16 答案 CF.
A:仅仅是求最大最小，不是第一和最后。
B:FIRST_VALUE, LAST_VALUE不能用在聚合函数。
C:正确
D:用了正确的分析函数语法，但是此处要求的是聚合函数。在聚合基础上使用分析函数，所有引用列都必须在GROUP BY列表中出现。
E:MIN, MAX在此试图作为分析函数来使用，但未满足上述要求。
F:正确，虽然方法有点曲折。如果你的数据库不支持KEEP可以考虑这种方法。
--------------------------------------------------------



-------------------------------------
关于 keep 和grouping_id的用法

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26844646/viewspace-749060/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26844646/viewspace-749060/