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

结果如下：