Oracle中Keep的应用
日期：2014-05-16 浏览次数：20545 次
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]
官方文档有如下说明：
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
----- -----
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
