oracle库中的keep,Oracle中Keep的应用

Oracle中Keep的使用

官方文档有如下说明：

FIRST/LAST Functions

The FIRST/LAST aggregate functions allow you to return the result of an aggregate applied over a set of rows that rank as the first or last with respect to a given order specification. FIRST/LAST lets you order on column A but return an result of an aggregate applied on column B. This is valuable because it avoids the need for a self-join or subquery, thus improving performance. These functions begin with a tiebreaker function, which is a regular aggregate function (MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) that produces the return value. The tiebreaker function is performed on the set rows (1 or more rows) that rank as first or last respect to the order specification to return a single value.

To specify the ordering used within each group, the FIRST/LAST functions add a new clause starting with the word KEEP.

FIRST/LAST Syntax

These functions have the following syntax:

aggregate_function KEEP

( DENSE_RANK LAST ORDER BY

expr [ DESC | ASC ] [NULLS { FIRST | LAST }]

[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...

)

[OVER query_partitioning_clause]

-- emp表的数据

SQL> SELECT t.empno,

2 t.ename,

3 t.mgr,

4 t.sal,

5 t.deptno

6 FROM emp t

7 ORDER BY t.sal,

8 t.deptno;

EMPNO ENAME MGR SAL DEPTNO

---------- -------------------- ---------- ---------- ----------

111 aaa 2222 800 9

7369 SMITH 7902 800 20

7900 JAMES 7698 950 30

7876 ADAMS 7788 1100 20

7521 WARD 7698 1250 30

7654 MARTIN 7698 1250 30

7934 MILLER 7782 1300 10

7844 TURNER 7698 1500 30

7499 ALLEN 7698 1600 30

7782 CLARK 7839 2450 10

7698 BLAKE 7839 2850 30

EMPNO ENAME MGR SAL DEPTNO

---------- -------------------- ---------- ---------- ----------

7566 JONES 7839 2975 20

7788 SCOTT 7566 3000 20

7902 FORD 7566 3000 20

7839 KING 5000 10

222 bbb 3333 5000 40

-- 1.现在要查询表中工资最高的部门号的最大最小值,工资最低的部门号的最大最小值

-- 因为是DENSE_RANK，会产生重复数据，使用min,max取一条。

-- 这个sql没有使用over子句，后面的例子会使用

SQL> SELECT MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) a,

2 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) b,

3 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) c,

4 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) d

5 FROM emp t;

A B C D

---------- ---------- ---------- ----------

9 20 10 40

-- 2.加上over,对每一行记录做计算，看看效果：

SQL>

SQL> SELECT t.empno,

2 t.ename,

3 t.mgr,

4 t.sal,

5 t.deptno,

6 MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() a,

7 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() b,

8 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() c,

9 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() d

10 FROM emp t

11 ORDER BY t.sal,

12 t.deptno

13 ;

EMPNO ENAME MGR SAL DEPTNO A B C D

----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------

111 aaa 2222 800.00 9 9 20 10 40

7369 SMITH 7902 800.00 20 9 20 10 40

7900 JAMES 7698 950.00 30 9 20 10 40

7876 ADAMS 7788 1100.00 20 9 20 10 40

7521 WARD 7698 1250.00 30 9 20 10 40

7654 MARTIN 7698 1250.00 30 9 20 10 40

7934 MILLER 7782 1300.00 10 9 20 10 40

7844 TURNER 7698 1500.00 30 9 20 10 40

7499 ALLEN 7698 1600.00 30 9 20 10 40

7782 CLARK 7839 2450.00 10 9 20 10 40

7698 BLAKE 7839 2850.00 30 9 20 10 40

7566 JONES 7839 2975.00 20 9 20 10 40

7788 SCOTT 7566 3000.00 20 9 20 10 40

7902 FORD 7566 3000.00 20 9 20 10 40

7839 KING 5000.00 10 9 20 10 40

222 bbb 3333 5000.00 40 9 20 10 40

-- 3.下面对每一个mgr求最大(最小)工资的部门号的最大(最小)值

SQL> SELECT t.empno,

2 t.ename,

3 t.mgr,

4 t.sal,

5 t.deptno,

6 MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) a,

7 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) b,

8 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) c,

9 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) d

10 FROM emp t

11 ORDER BY t.sal,

12 t.deptno

13 ;

EMPNO ENAME MGR SAL DEPTNO A B C D

----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------

111 aaa 2222 800.00 9 9 9 9 9

7369 SMITH 7902 800.00 20 20 20 20 20

7900 JAMES 7698 950.00 30 30 30 30 30

7876 ADAMS 7788 1100.00 20 20 20 20 20

7654 MARTIN 7698 1250.00 30 30 30 30 30

7521 WARD 7698 1250.00 30 30 30 30 30

7934 MILLER 7782 1300.00 10 10 10 10 10

7844 TURNER 7698 1500.00 30 30 30 30 30

7499 ALLEN 7698 1600.00 30 30 30 30 30

7782 CLARK 7839 2450.00 10 10 10 20 20

7698 BLAKE 7839 2850.00 30 10 10 20 20

7566 JONES 7839 2975.00 20 10 10 20 20

7902 FORD 7566 3000.00 20 20 20 20 20

7788 SCOTT 7566 3000.00 20 20 20 20 20

7839 KING 5000.00 10 10 10 10 10

222 bbb 3333 5000.00 40 40 40 40 40