转:http://blog.csdn.net/wanghai__/article/details/5011051
2种取值:
DENSE_RANK FIRST
DENSE_RANK LAST
SQL> select * from test;
ID MC SL
-------------------- -------------------- -------------------
1 111 1
1 222 1
1 333 2
1 555 3
1 666 3
2 111 1
2 222 1
2 333 2
2 555 2
9 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 test
5 ;
ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------
1 111 1 111 666
1 222 1 111 666
1 333 2 111 666
1 555 3 111 666
1 666 3 111 666
2 111 1 111 555
2 222 1 111 555
2 333 2 111 555
2 555 2 111 555
9 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 test
7 ;
ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDEMAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666
2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555
对于id=1的结果集进行一下解释
min(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) 就是111
max(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
id=2的结果集同理
***********************************************
转:http://blog.csdn.net/java3344520/article/details/5603309
先看一段ORACLE官方文档
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/analysis.htm#25806:
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函数按照某个字段排序后取得第一行或者最后一行,FIRST/LAST聚集函数可以按A列排序,B列聚集,避免了自连接和子查询.分组聚合函数(min,max....)位于FIRST/LAST函数之前产生多行结果集,并且按照排序返回FIRST/LAST单个值.
要指定在每个组的顺序,FIRST/LAST函数之前加上以关键字KEEP开始即可
FIRST/LAST Syntax
These functions have the following syntax:
aggregate_function KEEP
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]
Note that the ORDER
BY
clause can take multiple expressions.请注意在ORDER BY子句可以采取多种表现形式
Returns the row ranked first using DENSE_RANK
2种取值:
DENSE_RANK FIRST
DENSE_RANK LAST
在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。
例子如下:oracle分析函数中,keep and over的区别
公司部门中入厂时间最早的员工的薪水最小的是多少
SQL>SELECT deptno,ename,empno,sal,
MIN(sal) KEEP (dense_rank FIRST ORDER BY hiredate) over (PARTITION BY deptno) "min_sal"
FROM emp;
DEPTNO | ENAME | EMPNO | HIREDATE | SAL | min_sal |
10 | CLARK | 7782 | 1981-06-09 | 2450.00 | 2450 |
10 | KING | 7839 | 1981-11-17 | 5000.00 | 2450 |
10 | MILLER | 7934 | 1982-01-23 | 1300.00 | 2450 |
20 | yang_ping | 7389 | 1980-12-17 | 2700.00 | 800 |
20 | SMITH | 7369 | 1980-12-17 | 800.00 | 800 |
20 | ADAMS | 7876 | 1987-05-23 | 1100.00 | 800 |
20 | FORD | 7902 | 1981-12-03 | 3000.00 | 800 |
20 | SCOTT | 7788 | 1987-04-19 | 4000.00 | 800 |
20 | JONES | 7566 | 1981-02-22 | 2975.00 | 800 |
30 | ALLEN | 7499 | 1981-02-20 | 1600.00 | 1600 |
30 | BLAKE | 7698 | 1981-05-01 | 2850.00 | 1600 |
30 | MARTIN | 7654 | 1981-09-28 | 1250.00 | 1600 |
30 | JAMES | 7900 | 1981-12-03 | 950.00 | 1600 |
30 | TURNER | 7844 | 1981-12-03 | 1500.00 | 1600 |
30 | WARD | 7521 | 1981-02-22 | 1250.00 | 1600 |
查看结果分析:红色部分,2个入厂日期一样,同时取工资最低得到800
再看一个:计算部门平均工资,并且入工厂最早的最低的工资
SQL>select deptno,avg(sal) as sal,
min(sal)KEEP (dense_rank FIRST ORDER BY hiredate) AS min_sal
from emp group by deptno;
DEPTNO | SAL | MIN_SAL |
10 | 3437.5 | 2450 |
20 | 2645.833 | 800 |
30 | 1566.667 | 1600 |
相关推荐
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 ...
§10.13.8 使用ORACLE 诊断工具 126 第三部分 ORACLE应用系统开发优化 128 第11章 诊断与调整工具- 128 §11.1 警告日志文件 128 §11.1.1 警告日志文件管理 128 §11.1.2 参考警告日志文件调整 128 §11.2 后台进程...
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用 6、startup force 强制启动方式 当不能关闭数据库时,可以用startup force来完成数据库的关闭 先关闭数据库,再执行正常启动数据库命令 7、...
韩顺平Oracle数据库教程的全部笔记,本人从头看到尾,感觉对于Oracle的入门和复习很有帮助,希望能够帮助那些想要学习Oracle的朋友或者复习Oracle知识的朋友。
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...
– 每个查询会耗用2%CPU – 大量的I/O <br> 所以: – 170 meg = 5038 SQL Areas = 131319 x$ksmsp records Keep the Shared_pool_size at 100M or lower (50M if possible). <br> If v...
说明: 指定 Oracle 使用哪种日历系统作为日期格式。例如, 如果 NLS_CALENDAR 设置为 'Japanese Imperial', 那么日期格式为 'E YY-MM-DD'。即: 如果日期是 1997 年 5 月 15 日, 那么 SYSDATE 显示为 'H 09-05-15'。 ...
Keep3rV1Oracles是滑动窗口的Oracle,它使用在窗口上收集的观察windowSize ,以windowSize / granularity的精度提供过去windowSize移动价格平均值。 windowSize基于用户提供的granularity 。 每个periodSize都有一...
避免动态空间管理Oracle数据库增长空间是就以区的单位扩展的,区由块组成,区的增长方式有两种,一种是allocation_type是UNIFORM,每次分配区的大小是一致的,另一种Allocation_type是SYSTEM自动分配。区的大是...
ORACLE EBS11i —OAF开发笔记 Author: Jarwang(王重东) Create Date: July 12, 2009 Update Date: Control No: Current Edition: 1.0 声明:本文可以任意免费转载、复制、传播。但您务必保持其完整性! If you ...
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...
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?
sql> alter index xay_id allocate extent(size 200k datafile \'c:/oracle/index.dbf\'); <8>.alter index xay_id deallocate unused; 、查看索引 SQL>select index_name,index_type,table_name from user...
总结的很全面的oracle优化。值得你拥有。
您可以使用Hora建立模块以调用编码或者您可以自己进行代码的编写。调试器则不需要任何KeepTool产品的安装支持。调试器可进行多级别的工作。您可以通过嵌套单元执行命令,包括触发器。多重窗口显示编码,断点列表以及...