oracle排序与分析函数.docx

已知：两种排名方式(分区和不分区):使用和不使用partition
--两种计算方式(连续,不连续),对应函数：dense_rank,rank
语法：
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)

1.顺序：asc|desc 名次与业务相关：
示例：找求优秀学员：成绩：降序 迟到次数：升序
2.分区字段：根据什么字段进行分区。

问题：分区与分组有什么区别?
·分区只是将原始数据进行名次排列(记录数不变),
·分组是对原始数据进行聚合统计记录数变少,每组返回一条),注意：聚合。
rank()与dense_rank()：非连续排名与连续排名(都是简单排名)
·查询原始数据：学号,姓名,科目名,成绩
select * from t_score;
S_ID
S_NAME
SUB_NAME
SCORE
1
张三   
语文
80.00
2
李四   
数学
80.00
1
张三   
数学
0.00
2
李四   
语文
50.00
3
张三丰 
语文
10.00
3
张三丰 
数学

3
张三丰 
体育
120.00
4
杨过   
JAVA
90.00
5
mike   
c++
80.00
3
张三丰 
Oracle
0.00
4
杨过   
Oracle
77.00
2
李四   
Oracle
77.00

·查询各学生科目为Oracle排名(简单排名)
select sc.s_id,sc.s_name,sub_name,sc.score, rank() over (order by score desc) 名次 from t_score sc where sub_name='Oracle';
S_ID
S_NAME
SUB_NAME
SCORE
名次
4
杨过   
Oracle
77.00
1
2
李四   
Oracle
77.00
1
3
张三丰 
Oracle
0.00
3

·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名)

S_ID
S_NAME
SUB_NAME
SCORE
名次
4
杨过   
Oracle
77.00
1
2
李四   
Oracle
77.00
1
3
张三丰 
Oracle
0.00
2

·查询各学生各科排名(分区排名)
select sc.s_id,sc.s_name,sub_name,sc.score, rank() over (partition by sub_name order by score desc) 名次 from t_score sc
S_ID
S_NAME
SUB_NAME
SCORE
名次
4
杨过   
JAVA
90.00
1
4
杨过   
Oracle
77.00
1
2
李四   
Oracle
77.00
1
3
张三丰 
Oracle
0.00
3
5
mike   
c++
80.00
1
3
张三丰 
数学

1
2
李四   
数学
80.00
2
1
张三   
数学
0.00
3
3
张三丰 
体育
120.00
1
1
张三   
语文
80.00
1
2
李四   
语文
50.00
2
3
张三丰 
语文
10.00
3

·查询各科前2名(分区排名)
select * from ( select sc.s_id,sc.s_name,sub_name,sc.score, dense_rank() over (partition by sub_name order by score desc) 名次 from t_score sc ) x where x.名次<=2;
S_ID
S_NAME
SUB_NAME
SCORE
名次
4
杨过   
JAVA
90.00
1
4
杨过   
Oracle
77.00
1
2
李四   
Oracle
77.00
1
3
张三丰 
Oracle
0.00
2

2.oracle分析函数row_number() over()使用
row_number() over ([partition by col1] order by col2) ) as 别名
表示根据col1分组，在分组内部根据 col2排序
而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省略。
、select deptno,ename,sal,
     sum(sal) over (order by ename) 累计, --按姓名排序，并将薪水逐个累加
     sum(sal) over () 总和 ,               -- 此处sum(sal) over () 等同于sum(sal)，求薪水总和
    100*round(sal/sum(sal) over (),4) "份额(%)" --求每个人的薪水占总额的比例，小数点后保留2位，括号和百分号为特殊符号，所以需要“”
    from emp
结果如下 ：

2、select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,--partition by deptno先按部门分组，再按姓名排序，并将薪水逐个累加
sum(sal) over (partition by deptno) 部门总和,   -- 每个部门的薪水总和
100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",--每个员工在各自部门的薪水比例
sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over () 总和,   -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal) over (),4) "总份额(%)" --求每个人的薪水占总额的比例
from emp
结果如下：

3、select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) deptsum,--根据部门分组，再按部门内的个人薪水排序，逐个累加。
sum(sal) over (order by deptno,sal) sum --按部门排序，将薪水逐个累加。
from emp;
结果如下：

4、部门从大到小排列，部门里各员工的薪水从高到低排列
select deptno,ename,sal,
   sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,--按部门分组后，按部门和薪水降序排
   sum(sal) over (order by deptno desc,sal desc) sum --按部门和薪水降序排
   from emp;
结果如下：

5、将各部门的员工按薪水排序
select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 --先按部门分组，再在部门中按薪水降序排名
from scott.emp
结果如下：

6、查找各部门中薪水最高的前2位
select ename,job,deptno,sal,排名 from
(select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名
from scott.emp                   --先将各部门的员工按薪水排序，再在结果中取出需要的部分
) where 排名<=2;
结果如下：

7、如果已经在over()中进行过分组，在"... from emp;"后面不要加order   by 子句