DENSE_RANK分析函数的使用.
2010-07-26 10:42 Tracy. 阅读(3666) 评论(0) 编辑 收藏 举报突然发现DENSE_RANK是个不错的函数,以前一直以为FIRST_VALUE,LAST_VALUE可以替代 ,但是其实不然.有时候可以用的到大家。
DENSE_RANK
功能描述:根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序,然后给每一行赋一个号,从而形成一个序列,该序列从1开始,往后累加。每次ORDER BY表达式的值发生变化时,该序列也随之增加。有同样值的行得到同样的数字序号(认为null时相等的)。密集的序列返回的时没有间隔的数.
FIRST
功能描述:从DENSE_RANK返回的集合中取出排在最前面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE:下面例子中DENSE_RANK按部门分区,再按佣金commission_pct排序,FIRST取出佣金最低的对应的所有行,然后前面的MAX函数从这个集合中取出薪水最低的值;LAST取出佣金最高的对应的所有行,然后前面的MIN函数从这个集合中取出薪水最高的值
LAST
功能描述:从DENSE_RANK返回的集合中取出排在最后面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE:下面例子中DENSE_RANK按雇用日期排序,FIRST取出salary最低的对应的所有行,然后前面的MAX函数从这个集合中取出薪水最低的值;LAST取出雇用日期最高的对应的所有行,然后前面的MIN函数从这个集合中取出薪水最高的值
SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY department_id) "Best"
FROM employees
然后再举个使用dense rank的例子,其实在有些特别的场景,比如我说统计部门最高工资里面入职最早员工的信息,dense rank 的first , last函数就非常好实现.
下面例子是求最大最小值的,其实没有完全利用到我刚才说的那个场景.
CREATE TABLE TEST( V1 VARCHAR2(20), V2 VARCHAR2(10), V3 VARCHAR2(10)) ;
Insert into TEST (V1, V2, V3) Values ('1', '1', 'm');
Insert into TEST (V1, V2, V3) Values ('1', '2', 'f');
Insert into TEST (V1, V2, V3) Values ('2', '1', 'n');
Insert into TEST (V1, V2, V3) Values ('2', '2', 'g');
Insert into TEST (V1, V2, V3) Values ('3', '1', 'b');
Insert into TEST (V1, V2, V3) Values ('3', '2', 'a');
Insert into TEST (V1, V2, V3) Values ('1', '3', 'a');
SQL> SELECT t.* ,t.rowid FROM test t order by v1,v2;
V1 V2 V3 ROWID
-------------------- ---------- ---------- ------------------
1 1 m AAASUkAAEAAAAisAAA
1 2 f AAASUkAAEAAAAisAAB
1 3 a AAASUkAAEAAAAisAAG
2 1 n AAASUkAAEAAAAisAAC
2 2 g AAASUkAAEAAAAisAAD
3 1 b AAASUkAAEAAAAisAAE
3 2 a AAASUkAAEAAAAisAAF
怎么实现如下结果:
V1 V3 V3
-------------------- ---------- ----------
1 m a
2 n g
3 b a
------------------------------------------------------------------------------------------------------------
Answer:
select v1
,max(v3) keep (dense_rank first order by v2)
,max(v3) keep (dense_rank last order by v2)
from test
group by v1;
-------------------------------------------------------------------------------------------------------------
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2010/07/26/1785077.html