oracle统计函数学习


		oracle统计函数学习
 
数据库准备
Java代码  
CREATE TABLE emp (  
  deptno int NOT NULL,  
  ename varchar(100) DEFAULT NULL,  
  sal int DEFAULT NULL  
);  
  
insert into emp values (10,'KING',5000);  
insert into emp values (10,'CLARK',2450);  
insert into emp values (10,'MILLER',1300);  
insert into emp values (20,'SCOTT',3000);  
insert into emp values (20,'FORD',3000);  
insert into emp values (20,'JONES',2975);  
insert into emp values (20,'ADAMS',1100);  
insert into emp values (20,'SMITH',800);  
insert into emp values (30,'BLAKE',2850);  
insert into emp values (30,'ALLEN',1600);  
insert into emp values (30,'TURNER',1500);  
insert into emp values (30,'WARD',1250);  
insert into emp values (30,'MARTIN',1250);  
insert into emp values (30,'JAMES',950);  
 
1.row_number() 的使用
 
原表信息：
Java代码  
select deptno, ename, sal from emp order by deptno, sal desc;  
 
Java代码  
    DEPTNO  ENAME   SAL  
1   10  KING    5000  
2   10  CLARK   2450  
3   10  MILLER  1300  
4   20  SCOTT   3000  
5   20  FORD    3000  
6   20  JONES   2975  
7   20  ADAMS   1100  
8   20  SMITH   800  
9   30  BLAKE   2850  
10  30  ALLEN   1600  
11  30  TURNER  1500  
12  30  WARD    1250  
13  30  MARTIN  1250  
14  30  JAMES   950  
 使用row_number()查出各部门薪水最高的三个员工姓名、薪水，多于三个的只取三个。
Java代码  
    DEPTNO  RW  ENAME   SAL  
1   10  1   KING    5000  
2   10  2   CLARK   2450  
3   10  3   MILLER  1300  
4   20  1   SCOTT   3000  
5   20  2   FORD    3000  
6   20  3   JONES   2975  
7   30  1   BLAKE   2850  
8   30  2   ALLEN   1600  
9   30  3   TURNER  1500  
 体会：row_number() 返回的主要是“行”的信息，并没有按照sal排名，如
        20          1 SCOTT            3000
                    2 FORD             3000
scott与ford薪水一样多，ford前面的2仅仅是行数、记录条数的感念。
 
2.rank()、dense_rank() 的使用
 
使用rank()查出各部门薪水前三名的员工姓名、薪水。
 
Java代码  
select *  
  from (select deptno,  
               rank() over(partition by deptno order by sal desc) rk,  
               ename,  
               sal  
          from emp)  
 where rk <= 3  
 
Java代码  
    DEPTNO  RK  ENAME   SAL  
1   10  1   KING    5000  
2   10  2   CLARK   2450  
3   10  3   MILLER  1300  
4   20  1   SCOTT   3000  
5   20  1   FORD    3000  
6   20  3   JONES   2975  
7   30  1   BLAKE   2850  
8   30  2   ALLEN   1600  
9   30  3   TURNER  1500  
 
使用dense_rank()查出各部门薪水前三名的员工姓名、薪水。
 
Java代码  
select *  
  from (select deptno,  
               dense_rank() over(partition by deptno order by sal desc) drk,  
               ename,  
               sal  
          from emp)  
 where drk <= 3  
 
 
Java代码  
    DEPTNO  DRK ENAME   SAL  
1   10  1   KING    5000  
2   10  2   CLARK   2450  
3   10  3   MILLER  1300  
4   20  1   SCOTT   3000  
5   20  1   FORD    3000  
6   20  2   JONES   2975  
7   20  3   ADAMS   1100  
8   30  1   BLAKE   2850  
9   30  2   ALLEN   1600  
10  30  3   TURNER  1500  
 Oracle统计函数（一）--Rank和Dense_Rank
分类:Oracle数据库
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
其语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)