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_IDS_NAMESUB_NAMESCORE1张三 语文80.002李四 数学80.001张三 数学0.002李四 语文50.003张三丰 语文10.003张三丰 数学3张三丰 体育120.004杨过 JAVA90.005mike c+80.003张三丰 Oracle0.004杨过 Oracle77.002李四 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_IDS_NAMESUB_NAMESCORE名次4杨过 Oracle77.0012李四 Oracle77.0013张三丰 Oracle0.003对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名) S_IDS_NAMESUB_NAMESCORE名次4杨过 Oracle77.0012李四 Oracle77.0013张三丰 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_IDS_NAMESUB_NAMESCORE名次4杨过 JAVA90.0014杨过 Oracle77.0012李四 Oracle77.0013张三丰 Oracle0.0035mike c+80.0013张三丰 数学12李四 数学80.0021张三 数学0.0033张三丰 体育120.0011张三 语文80.0012李四 语文50.0023张三丰 语文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_IDS_NAMESUB_NAMESCORE名次4杨过 JAVA90.00 14杨过 Oracle77.00 12李四 Oracle77.00 13张三丰 Oracle0.00 22.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