oracle库中的keep,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 11 222 11 333 21 555 31 666 32 111 12 222 12 333 22 555 29 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 test5 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE-------------------- -------------------- ------------------- ------------------------------ ------------------------------1 111 1 111 6661 222 1 111 6661 333 2 111 6661 555 3 111 6661 666 3 111 6662 111 1 111 5552 222 1 111 5552 333 2 111 5552 555 2 111 5559 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 test7 ;

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 6661 222 1 111 222 555 6661 333 2 111 222 555 6661 555 3 111 222 555 6661 666 3 111 222 555 6662 111 1 111 222 333 5552 222 1 111 222 333 5552 333 2 111 222 333 5552 555 2 111 222 333 5559 rows selectedmin(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) 就是111max(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的用法