ORACLE排序与分析函数

--已知：两种排名方式(分区和不分区):使用和不使用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杨过JAVA90.00
5mike c++80.00
3张三丰Oracle0.00
4杨过Oracle77.00
2李四Oracle77.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杨过Oracle77.001
2李四Oracle77.001
3张三丰Oracle0.003
·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名)
S_ID S_NAME SUB_NAME SCORE名次
4杨过Oracle77.001
2李四Oracle77.001
3张三丰Oracle0.002
·查询各学生各科排名(分区排名)
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杨
过JAVA90.001
4杨
过Oracle77.001
2李
四Oracle77.001
3张三丰Oracle0.003
5mike
c++80.001
3张三丰数学1
2李
四数学80.002
1张
三数学0.003
3张三丰体育120.001
1张
三语文80.001
2李
四语文50.002
3张三丰语文10.003
·查询各科前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杨过JAVA90.001
4杨过Oracle77.001
2李四Oracle77.001
3张三丰Oracle0.002
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)dept_sum,--根据部门分组，再按部门内的个人薪水排序，逐个累加。

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子句。