oracle统计函数学习

数据库准备

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() 的使用

 

原表信息：

select deptno, ename, sal from emp order by deptno, sal desc;

 

   	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()查出各部门薪水最高的三个员工姓名、薪水，多于三个的只取三个。

   	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()查出各部门薪水前三名的员工姓名、薪水。

 

 select *
   from (select deptno,
                rank() over(partition by deptno order by sal desc) rk,
                ename,
                sal
           from emp)
  where rk <= 3

 

   	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()查出各部门薪水前三名的员工姓名、薪水。

 

  select *
    from (select deptno,
                 dense_rank() over(partition by deptno order by sal desc) drk,
                 ename,
                 sal
            from emp)
   where drk <= 3

 

 

   	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 }]]...
)