DENSE_RANK分析函数的使用

突然发现DENSE_RANK是个不错的函数，以前一直以为FIRST_VALUE,LAST_VALUE可以替代 ,但是其实不然.有时候可以用的到大家。 DENSE_RANK 功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排

突然发现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;

-------------------------------------------------------------------------------------------------------------

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

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;

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;