官方文档有如下说明:
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
分享到:
相关推荐
Oracle数据库的KEEP池是Oracle 8.0及更高版本中引入的一个高级特性,它允许数据库管理员(DBA)对数据缓存进行更精细化的管理。KEEP池的主要目的是为那些经常被访问但又容易因缓存竞争而被挤出缓存的段提供一个专门的...
服务器资源占用脚本甲骨文服务器保活脚本Oracle Server Keep Alive Script项目资源
在确认数据完整无误后,你可以在新环境中开始正常使用Oracle数据库。 总的来说,直接拷贝数据文件的迁移方式适用于特定条件下的Oracle数据迁移,它简化了传统备份恢复过程,但仍然需要谨慎操作,以避免数据丢失或不...
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 最小化排序时...
本文档旨在提供 Oracle 12c 备份恢复的技术手册,主要介绍 RMAN 工具的概念、架构、备份类型和使用方法。 一、RMAN 概念 RMAN(Recovery Manager)是 Oracle 推荐的备份和恢复工具。使用 RMAN 可以进行数据库备份...
其中,`CONTROL_FILE_RECORD_KEEP_TIME` 参数可以控制备份记录在控制文件中的保存时间长度,一旦超出设定时间,这些记录会被新的备份信息覆盖。 #### Recovery Catalog Recovery Catalog 可以用于保存 RMAN ...
- Database Buffers:数据缓冲区,根据不同的Oracle版本,该部分可能包含db_block_buffer、db_block_size、buffer_pool_keep、buffer_pool_recycle、db_cache_size等。 - Redo Buffers:重做日志缓冲区(log_buffer...
CPU_COUNT 是 Oracle数据库参数设置技术手册中另一个重要的参数,它控制着数据库的 CPU 使用率,对数据库的性能和稳定性有着重要的影响。数据库管理员可以根据实际情况调整 CPU_COUNT 的值,以提高数据库的性能和...
PGA 则主要包含用户全局区(User Global Area, UGA),在Oracle使用共享服务器模式(Multithreaded Server, MTS)时,部分UGA会放入large_pool_size中。UGA存储了用户的会话信息,如游标、变量和PL/SQL工作区等。 ...
Oracle数据库sqlplus是Oracle数据库管理系统中的一种命令行工具,用于执行SQL语句、查看数据库状态、执行数据库管理任务等。以下是Oracle数据库sqlplus常用命令的知识点总结: 获取数据库信息 1. 获取数据库名和...
Buffer Cache用于存储最近使用的数据块,Oracle使用LRU算法管理这些缓存,确保高效的缓存命中率。Buffer Cache被划分为Default、Keep和Recycle三个池,以满足不同类型的存储需求。Shared Pool用于存储解析的SQL语句...
在 Oracle 数据库中,为了更好地管理和控制用户对不同表空间的使用情况,引入了 Quota 的概念。Quota 主要用于限制用户在一个或多个表空间内所能使用的最大空间量。通过设置合适的 Quota,不仅可以有效地防止用户...
SQL语句是Oracle数据库中最基本的操作语言,包括select、insert、update和delete四种基本语句。select语句用于查询数据,insert语句用于添加数据,update语句用于修改数据,delete语句用于删除数据。 2. 数据类型:...
7. **Db_keep_cache_size**: 配置为224MB,用于保持经常使用的数据块不被替换,提高热数据的访问速度。 8. **Workarea_size_policy**: 设置为AUTO,使Oracle能够根据工作区的实际需求动态调整其大小,避免了固定...
Oracle数据库作为全球广泛使用的数据库管理系统之一,其稳定性和安全性备受赞誉。为了确保数据的安全,定期备份是必要的操作。本文将详细介绍如何利用任务计划和批处理脚本来实现Oracle数据库的定时备份。 一、任务...
`buffer_pool_keep`保留某些对象在内存中以减少I/O,`buffer_pool_recycle`则用于清理并重用内存。 8. **commit_point_strength**:在分布式事务中,控制提交点的确定,影响事务的一致性和分布。 9. **compatible*...
Buffer Cache 是 Oracle 中的一种缓存机制,负责将磁盘上的数据 block 读取到内存中,以提高数据库的访问速度。在本文中,我们将详细介绍 Buffer Cache 的工作原理、状态、管理和优化方法。 Buffer Cache 的工作...