官方文档有如下说明:
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
分享到:
相关推荐
KEEP池的使用十分简单,设置DB_KEEP_CACHE_SIZE的值大于0,就可以将其他对象的BUFFER_POOL参数设置为KEEP了。
8.2.4 使用 DBMS_SHARED_POOL.KEEP 的技巧 8.2.5 怎样生成进行固定操作的脚本 8.2.6 使用 DBMS_SHARED_POOL .UNKEEP 的技巧 8.3 优化数据排序的技术 8.3.1 在内存中进行全部或大部分排序 8.3.2 最小化排序时...
You’ll learn how to understand Oracle database architecture, set up and manage an Oracle database, and keep it running in tiptop form. Oracle 11g For Dummies covers: The building blocks behind the ...
how to perform all common administrative tasks needed to keep the database operational. These tasks include configuring the database, managing memory and storage, managing users, managing database ...
§3.2.2 在参数值中使用特殊字符 65 §3.2.3 修改参数值 66 §3.2.4 显示当前参数值 69 §3.2.5 参数的使用 69 §3.2.6 参数的类型 69 §3.2.7 不能在参数文件中指定的参数 70 §3.2.8 当参数指定错误时怎么办? 70 ...
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用 6、startup force 强制启动方式 当不能关闭数据库时,可以用startup force来完成数据库的关闭 先关闭数据库,再执行正常启动数据库命令 7、...
值范围: Oracle8i National Language Support Guide 中指定的任何有效的10 字节字符串。 默认值: BINARY nls_currency: 说明: 为 L 数字格式元素指定用作本地货币符号的字符串。该参数的默认值由 NLS_TERRITORY ...
韩顺平Oracle数据库教程的全部笔记,本人从头看到尾,感觉对于Oracle的入门和复习很有帮助,希望能够帮助那些想要学习Oracle的朋友或者复习Oracle知识的朋友。
如果在应用SQL编程中不使用捆绑变量,很容易引起 shared_pool 和library cache 的latch 竞争. 这种latch等待时间可以通过优化SQL来减少. Oracle 8i Release 2 (8.1.6)可以做直接常量和捆绑变量的 ...
Administrators struggle to keep up with the explosion of access and activity driven by the proliferation of computing into everything from phones to tablets to PCs in our increasingly connected world...
Administrators struggle to keep up with the explosion of access and activity driven by the proliferation of computing into everything from phones to tablets to PCs in our increasingly connected world...
避免动态空间管理Oracle数据库增长空间是就以区的单位扩展的,区由块组成,区的增长方式有两种,一种是allocation_type是UNIFORM,每次分配区的大小是一致的,另一种Allocation_type是SYSTEM自动分配。区的大是...
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段...
6.2.3.4 DB_KEEP_CACHE_SIZE 42 6.2.3.5 LOCK_SGA 43 6.2.3.6 DB_FILES 43 6.2.3.7 DB_FILE_MULTIBLOCK_READ_COUNT 43 6.2.3.8 LOG_BUFFER 44 6.2.4 与并行操作有关的参数 44 6.2.5 Data Guard有关的参数 45 6.2.5.1...
Keep3rV1Oracles是滑动窗口的Oracle,它使用在窗口上收集的观察windowSize ,以windowSize / granularity的精度提供过去windowSize移动价格平均值。 windowSize基于用户提供的granularity 。 每个periodSize都有一...
ORACLE EBS11i —OAF开发笔记 Author: Jarwang(王重东) Create Date: July 12, 2009 Update Date: Control No: Current Edition: 1.0 声明:本文可以任意免费转载、复制、传播。但您务必保持其完整性! If you ...
Today’s information architecture is much more dynamic than it was just a few years ago. Businesses now demand ...whether you've made the right decisions to keep your multi-TB system highly available?
总结的很全面的oracle优化。值得你拥有。
您可以使用Hora建立模块以调用编码或者您可以自己进行代码的编写。调试器则不需要任何KeepTool产品的安装支持。调试器可进行多级别的工作。您可以通过嵌套单元执行命令,包括触发器。多重窗口显示编码,断点列表以及...