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版本之前，只有分析功能（易做图ytic ），即从一个查询结果中计算每一行的排序值，是基于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 }]]...
)
