Oracle中rank() over, dense_rank(), row_number() 的区别_数据库技术_Linux公社-Linux系统门户网站
你好，游客
Oracle中rank() over, dense_rank(), row_number() 的区别
来源：Linux社区&
作者：zdp072
中rank() over, dense_rank(), row_number() 的区别
假设现在有一张学生表student，学生表中有姓名、分数、课程编号，现在我需要按照课程对学生的成绩进行排序。
select * from student
1. rank over ()可以实现对学生排名，特点是成绩相同的两名是并列，如下1 2 2 4 5
select name,& & &
course,& & &
rank() over(partition by course order by score desc) as rank&
2. dense_rank()和rank over()很像，但学生成绩并列后并不会空出并列所占的名次，如下1 2 2 3 4
select name,& & &
course,& & &
dense_rank() over(partition by course order by score desc) as rank&
3. row_number这个函数不需要考虑是否并列，那怕根据条件查询出来的数值相同也会进行连续排名
select name,& & &
course,& & &
row_number() over(partition by course order by score desc) as rank&
1. partition by用于给结果集进行分区。
2. partition by和group by有何区别？
partition by只是将原始数据进行名次排列(记录数不变)
group by是对原始数据进行聚合统计(记录数可能变少, 每组返回一条)
3. 使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
可以这样： rank over(partition by course order by score desc nulls last)
更多Oracle相关信息见 专题页面
本文永久更新链接地址：
相关资讯 & & &
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款4501人阅读
PL/SQL（25）
over partition by 与 group by 都是与统计类函数用，这两个有什么区别呢？目前我只知道一个这样的区别：比如有一张表saraly:CREATE TABLE SALARY AS SELECT 'A' NAME,10 DEPT,1000 SALARY FROM DUAL UNION ALL SELECT 'B',10,2000 FROM DUAL UNION ALL SELECT 'C' ,20,1500 FROM DUAL UNION ALL SELECT 'D',20,3000 FROM DUAL UNION ALLSELECT 'E',10,1000 FROM DUAL;NAME DEPT SALARYA 10 1000B 10 2000C 20 1500D 20 3000E 10 1000 用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资：select name,dept,salary,sum(salary) over (partition by dept) total_ name dept salary tatal_salaryA 10 B 10 E 10 C 20 D 20 用goup by 就没办法做到这点，只能查询到每个部门的总工资：select dept,sum(salary) total_salary from salary group by deptdept total_salary10 400020 4500另外over partion by 还可以做到查询每位员工占部门总工资的百分比：select name,dept,salary,salary*100/sum(salary) over (partition by dept)name dept salary percentA 10 1000 25B 10 2000 50E 10 1000 25C 20 D 20
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：984219次
积分：11739
积分：11739
排名：第1273名
原创：198篇
转载：233篇
评论：52条
(1)(1)(2)(5)(2)(1)(7)(1)(1)(7)(5)(12)(5)(6)(8)(3)(3)(3)(8)(1)(1)(20)(1)(1)(12)(6)(15)(7)(12)(5)(3)(14)(12)(1)(5)(3)(2)(13)(7)(10)(10)(24)(3)(12)(5)(5)(4)(42)(3)(6)(85)OracleSQL分组函数和分析函数详解
SQL分组函数和分析函数详解。
一，常见的分组函数，使用分组函数的SQL语句，必须对查询的结果进行分组，即必须有group by子句，这个和MySQL的语法不一样，
stddev() 求标准方差 variance() 求方差 COUNT(*) 返回满足选择条件的所有行的行数，包括值为空的行和重复的行 COUNT(expr) 返回满足选择条件的且表达式不为空行数。 COUNT(DISTINCT expr) 返回满足选择条件的且表达式不为空，且不重复的行数。 Select count(1) 和 Select count(*), select count(column1) 返回的结果一样的 注意例外： 分组函数中只有count(*)会处理NULL值得段，其他函数都不处理，比如avg()计算平均值肯定不处理null 当employees 表中的字段 commission_pct 有空值的时候下面结果不一样。 select count(*) from employees select count(commission_pct) from employees
二，group by 子句增强函数，在使用分组函数之后，group by 子句对分组后的查询结果再做一遍过滤。
rollup()函数产生常规分组后的各小组的汇总行，和整个查询结果的汇总行 案例： SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id & 60 GROUP BY ROLLUP(department_id, job_id); 结果分析：
这里新增的各小组的汇总行，是按照第一个分组字段进行统计的。
看查询结果有的字段为空，不好看，在rollup和cube中可以使用grouping来填充 GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1;如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
案例： SELECT department_id,grouping(department_id), job_id,grouping(job_id), SUM(salary) FROM employees WHERE department_id & 60 GROUP BY ROLLUP(department_id, job_id);
第1行， department_id 和 job_id都被用到了，所以都返回0; 第2行, job_id 没有被用到，所以返 回1;最后一行行，department_id 和job_id 都没有被用到，所以都返回1
总结： 假设有一个表test，有A、B、C、D、E5列。 如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是&卷起&，这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping)，对于n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的： Select A,B,C,sum(E) from test group by rollup(A,B,C) 与 Select A,B,C,sum(E) from test group by A,B,C union all Select A,B,null,sum(E) from test group by A,B union all Select A,null,null,sum(E) from test group by A union all Select null,null,null,sum(E) from test cube()函数：实际使用到的可能性不大，产生Rollup结果集 + 多维度的交叉表数据源 cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group by cube(A,B,C),，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql： Select A,B,C,sum(E) from test group by cube(A,B,C); 与 Select A,B,C,sum(E) from test group by A,B,C union all Select A,B,null,sum(E) from test group by A,B union all Select A,null,C,sum(E) from test group by A,C union all Select A,null,null,sum(E) from test group by A union all Select null,B,C,sum(E) from test group by B,C union all Select null,B,null,sum(E) from test group by B union all Select null,null,C,sum(E) from test group by C union all Select null,null,null,sum(E)
grouping sets() grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by,grouping sets的参数允许重复
案例：使用Grouping Set 来代替多次UNION SELECT department_id, job_id, manager_id, avg(salary) FROM employees GROUP BY GROUPING SETS((department_id, job_id),(job_id, manager_id));
总结 rollup (N+1个分组方案) cube (2^N个分组方案) grouping sets (自定义罗列出分组方案)
机制不同 在rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。
集合可运算 3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列(比如说A+B，A||B)，也可以是这两种列的一个集合(例如(A+B，C))，对于grouping set更是特殊，可以是空集合()，表示对全表进行group by。
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。 分析函数和聚合函数的区别 普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
分析函数的形式 分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) 。 使用形式如下：over(partition by xxx order by yyy rows between zzz)。
案例1：显示各部门员工的工资，并附带显示该部门的最高工资。 SELECT E.Department_Id, E.Employee_Id, E.Last_Name, E.Salary, LAST_VALUE(E.Salary) OVER(PARTITION BY E.Department_Id ORDER BY E.Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM employees E; --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录, --unbounded：不受控制的，无限的 --preceding：在...之前 --following：在...之后
案例2：按照Department_Id分组，然后累计计算每组值的总和(并没有什么实际的意义) SELECT e.employee_id, e.last_name, e.department_id, e.salary, SUM(e.salary) OVER(PARTITION BY e.department_id ORDER BY e.last_name) accumulation_quantity FROM
理解over()函数 1.1、SQL语句中where子句后的order by 和分析函数中的order by，两个order by的执行时机 分析函数是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果： a) 两者一致：如果sql语句中的order by满足分析函数分析时要求的排序，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序; b) 两者不一致：如果sql语句中的order by不满足分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执行排序。
1.2、分析函数中的分组/排序/窗口 分析函数包含三个分析子句：分组(partition by)， 排序(order by)， 窗口(rows) 窗口就是分析函数分析时要处理的数据范围，就拿sum求和函数来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。 窗口子句在这里我只说rows方式的窗口，range方式和滑动窗口也不提。 窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性。 第一行是 unbounded preceding， 当前行是 current row， 最后一行是 unbounded following， 窗口子句不能单独出现，必须有order by子句时才能出现，如: last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following) 以上示例指定窗口为整个分组。而出现order by子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行!
当省略窗口子句时: a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行 b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组
如果省略分组,则把全部记录当成一个组: a) 如果这时存在order by则默认窗口是unbounded preceding and current row --当前组的第一行到当前行 b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following --整个组
FIRST_VALUE 返回组中数据窗口的第一个值 LAST_VALUE 返回组中数据窗口的最后一个值(默认统计范围是 rows between unbounded preceding and current row，到当前记录为止的最后一条记录)
案例： SELECT e.department_id, e.manager_id, e.employee_id, e.last_name, e.salary, LAST_VALUE(e.salary) OVER(PARTITION BY e.department_id /*order by e.salary asc*/ ) current_lV FROM employees e where e.department_id = 30 order by e.department_id, e.manager_id desc
结果：先进行SQL主句中的order by，分析函数中没有排序，分析函数直接取排序后的数据进行操作，得到的current_lV都是SQL主句order by排序后的最后一个值 DEPARTMENT_ID MANAGER_ID EMPLOYEE_ID LAST_NAME SALARY CURRENT_LV ------------- ---------- ----------- ------------------------- ---------- ---------- 30 114 115 Khoo 00 30 114 116 Baida 00 30 114 117 Tobias 00 30 114 119 Colmenares 00 30 114 118 Himuro 00 30 100 114 Raphaely 00
分析函数中加上子order by语句就大不一样了 SELECT e.department_id, e.manager_id, e.employee_id, e.last_name, e.salary, LAST_VALUE(e.salary) OVER(PARTITION BY e.department_id order by e.salary asc ) current_lV FROM employees e where e.department_id = 30 order by e.department_id, e.manager_ 结果： DEPARTMENT_ID MANAGER_ID EMPLOYEE_ID LAST_NAME SALARY CURRENT_LV ------------- ---------- ----------- ------------------------- ---------- ---------- 30 114 119 Colmenares 0 30 114 118 Himuro 0 30 114 117 Tobias 0 30 114 116 Baida 0 30 114 115 Khoo 0 30 100 114 Raphaely 00 SQL主句先选数据但是不执行主句中的order by，然后分析函数中的order by子句执行，并进行分析函数处理(窗口默认为第一行到当前行)，得到的last_value当然就是当前部门最低工资，最后再执行主SQL的order by
rank，dense_rank，row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。 row_number： row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 dense_rank： dense_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。 rank： rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
案例：将一个部门内的员工工资从高到低排序，并给他们进行排名，排名遇到相同的工资按三种不同的方式排名(1)相同的工资排名相同。。。 SELECT d.department_name, e.last_name, e.salary, rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank1, dense_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2, row_number() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank3 FROM employees e, departments d WHERE 1 = 1
AND e.department_id = d.department_ -- 没有什么难点，相同的数据指的是 同一个partition 分组内的salary字段。查看: 14872|回复: 13
over partition by与group by 的区别
论坛徽章:2
over partition by 与 group by 都是与统计类函数用，这两个有什么区别呢？
目前我只知道一个这样的区别：
比如有一张表saraly:CREATE TABLE SALARY AS SELECT 'A' NAME,10 DEPT,1000 SALARY FROM DUAL UNION ALL SELECT 'B',10,2000 FROM DUAL UNION ALL SELECT 'C' ,20,1500 FROM DUAL UNION ALL SELECT 'D',20,3000 FROM DUAL UNION ALL
SELECT 'E',10,1000 FROM DUAL;
NAME DEPT SALARY
A& && && &10& &&&1000
B& && && &10& &&&2000
C& && && &20& &&&1500
D& && && &20& &&&3000
E& && && &10& &&&1000& &
用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资：
select name,dept,salary,sum(salary) over (partition by dept) total_&&
name& && & dept& && && &salary& && &tatal_salary
A& & & & 10& & & & 1000& & & & 4000
B& & & & 10& & & & 2000& & & & 4000
E& & & & 10& & & & 1000& & & & 4000
C& & & & 20& & & & 1500& & & & 4500
D& & & & 20& & & & 3000& & & & 4500
用goup by 就没办法做到这点，只能查询到每个部门的总工资：
select dept,sum(salary) total_salary from salary group by dept
dept& && &&&total_salary
10& & & & 4000
20& & & & 4500
另外over partion by 还可以做到查询每位员工占部门总工资的百分比：
select name,dept,salary,salary*100/sum(salary) over (partition by dept)
name& && & dept& && && &salary& &&&percent
A& & & & 10& & & & 1000& & & & 25
B& & & & 10& & & & 2000& & & & 50
E& & & & 10& & & & 1000& & & & 25
C& & & & 20& & & & 1500& & & & 33.3
D& & & & 20& & & & 3000& & & & 66.7
用group by 也没办法做到这个.不知道我的理解正不正确，请各位朋友指点，特别是over partition by 与group by 的更多区别请各位一起分享，谢谢！
20& & & & 4500
论坛徽章:0
over partition by 功能很强大，我都不知道有此功能，学习学习
论坛徽章:0
学习了。。。
论坛徽章:125
论坛徽章:8
谁能继续介绍一下他们之间的区别？
论坛徽章:3
论坛徽章:0
论坛徽章:394
over 是分析函数
group是分组
论坛徽章:0
又进步了，不过我还想请教over(&&partition by col
& && && && && && && && && && && && && && && & order by col)&&中这个两同时和不同时出现的区别
论坛徽章:0
从别人的博客上弄的例子,大家一起学习下:
select deptno,ename,sal,
& && &sum(sal) over (partition by deptno order by ename) 部门连续求和,& & --各部门的薪水&连续&求和
& &&&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
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号over&partition&by与group&by&的区别
over partition by与group by 的区别
group by 只能得到分组后的统计数据，over partition by
不仅可以得到分组后的统计数据，还可以同时显示明细数据。
group by 是在where子句之后；over partition by 是from子句之前。
over partition by 与 group by 都是与统计类函数用，这两个有什么区别呢？
目前我只知道一个这样的区别：
比如有一张表saraly:CREATE TABLE SALARY AS SELECT 'A' NAME,10 DEPT,1000
SALARY FROM DUAL UNION ALL SELECT 'B',10,2000 FROM DUAL UNION ALL
SELECT 'C' ,20,1500 FROM DUAL UNION ALL SELECT 'D',20,3000 FROM
DUAL UNION ALL
SELECT 'E',10,1000 FROM DUAL;
NAME DEPT SALARY
用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资：
select name,dept,salary,sum(salary) over (partition by dept)
name&&&&&&
dept&&&&&&&&
salary&&&&&
tatal_salary
1000&&&&&&&
2000&&&&&&&
1000&&&&&&&
1500&&&&&&&
3000&&&&&&&
用goup by 就没办法做到这点，只能查询到每个部门的总工资：
select dept,sum(salary) total_salary from salary group by
dept&&&&&&&
total_salary
另外over partion by 还可以做到查询每位员工占部门总工资的百分比：
select name,dept,salary,salary*100/sum(salary) over (partition by
name&&&&&&
dept&&&&&&&&
salary&&&&
1000&&&&&&&
2000&&&&&&&
1000&&&&&&&
1500&&&&&&&
3000&&&&&&&
用group by 也没办法做到这个.不知道我的理解正不正确，请各位朋友指点，特别是over partition by 与group
by 的更多区别请各位一起分享，谢谢！
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。