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
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_salary from salary;
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) percent from salary;
name dept salary percent
A 10 1000 25
B 10 2000 50
E 10 1000 25
C 20 1500 33.3333333333333
D 20 3000 66.6666666666667
用group by 也没办法做到这个.不知道我的理解正不正确,请各位朋友指点,特别是over partition by 与group by 的更多区别请各位一起分享,谢谢!
分享到:
相关推荐
介绍了 1.NOT EXISTS, 2.GROUP BY ... [HAVING ...] 3.count(*) over(partition by ...) 做查询的效率,以及各自的使用方法
SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col) FROM tmp1 ORDER BY col; 2、SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause ) 例如: --聚合函数 SELECT col, sum(value) FROM tmp1 ...
第五篇 分析函数简述 分析函数的语法结构比较复杂,但多数函数都具有相同的语法...Partition没啥说的,功能强大参数少,主要用于分组,可以理解成select中的group by。不过它跟select语句后跟的group by 子句并不冲突。
可以完成对分组的数据进行增加排序,partition by可以与以上三个函数联合使用。 利用partition by按照班级名称分组,学生id排序 select s.id, s.name, cid, c.name, row_number() over(partition by c.name order ...
有些事情始终是需要坚持下去的。...row_number() OVER(ORDER BY ID)-row_number() OVER(PARTITION BY val ORDER BY ID) x FROM LCY ) GROUP BY val,x ORDER BY MIN(ID); 第二种rank(): SELECT val,COUNT(*
开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。 Window Function又称为窗口函数...
rank() over (partition by t.organcode order by t.score desc) ranknum from exam_user_exam t) where ranknum 1.8.11.2 说明 获取每个组织,考试成绩前三名。 1.9 存储过程 1.9.1 定义 存储过程是一组为了完成...
A set of memory addresses that are mapped to physical memory addresses by the system. In a 32-bit operation system, there is normally a linear array of 2^32 addresses representing 4,294,967,269 byte ...
HINT This command is superseded by Synchronous Modeling commands. To run this legacy command, set UGII_DMX_NX502=1. HELP DSN_face_edit ACTIONS STANDARD END_OF_MENU AFTER UG_EDIT_CUT SEPARATOR ...
If you have a partition created by WYSE DOS 2.11 or 3.1, you must delete the DOS partitions from your hard disk. See the procedure for repartitioning hard disks in Chapter 4 of the Microsoft MS-DOS ...
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段...
Contents Page 0 Introduction.................................................................................................................................................................
ret = sysfs_create_group(&dev->pdev->dev.kobj, &vfe_attribute_group); #ifdef CONFIG_ES dev->early_suspend.level = EARLY_SUSPEND_LEVEL_DISABLE_FB + 1; dev->early_suspend.suspend = vfe_early_suspend...
ret = sysfs_create_group(&dev->pdev->dev.kobj, &vfe_attribute_group); #ifdef CONFIG_ES dev->early_suspend.level = EARLY_SUSPEND_LEVEL_DISABLE_FB + 1; dev->early_suspend.suspend = vfe_early_suspend...
ro.frp.pst=/dev/block/by-name/frp PRODUCT_PROPERTY_OVERRIDES += \ persist.sys.usb.config=mass_storage,adb \ ro.adb.secure=0 \ ro.sys.mutedrm=true \ rw.logger=0 # A33 Media PRODUCT_...
ro.frp.pst=/dev/block/by-name/frp PRODUCT_PROPERTY_OVERRIDES += \ persist.sys.usb.config=mass_storage,adb \ ro.adb.secure=0 \ ro.sys.mutedrm=true \ rw.logger=0 # A33 Media PRODUCT_...