分组函数group by和Oracle中分析函数partition by的用法以及区别

转载 2016年08月31日 09:57:34
今天有个同事给我打电话问我题目列出的这个问题,在日常开发中,确实它们俩都是与分组有关的,但是需要注意的是一个是分组函数另一个是分析函数,讲解用到的表就以Oracle中schema的scott的EMP表和DEPT表为例做讲解:

       老总下命令说:需要统计下公司里每个部门的人数以及每个部门的工资发放的工资总和;先分析下统计每个部门的人数和工资总和,指的是将不同部门的人员分别放到不同的地方,然后再将不同部门的人数一个个数出来,以及工资一个个加出来,也就是说表里面的数据是零散的,通过分组函数分组后展示出来的是以组为单位的几个数据块,需要注意的是分组完成之后的数据块是一个整体为单位的,所以对于这个整体不允许使用比如针对块里单个对象做处理

,正式因为如此,很多初学的人都会犯这种类型的错误,好的,上SQL;


  1.     SELECT DEPTNO 部门编号,COUNT(*) AS 部门人数,SUM(sal) 部门工资总和 FROM emp GROUP BY deptno ORDER BY SUM(sal) DESC;  

结果就不贴出来了,可以看到的是整个EMP表里面的数据分成了三条,每条数据针对一个部门,对于分组函数而言,它不关心每个分组里面到底是由谁构成的,或者是相对于个体,分组函数是站在一个宏观的角度上看待数据的;

好的,那么又一个需求来了,如果需要显示每个部门的员工姓名、工资、该员工在部门里的工资排序,部门的工资总和呢?那么这个时候使用partition by就要方便的多,看代码:


  1. SELECT deptno 部门名称 ,ename 员工姓名, sal 员工工资, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) 所在部门工资排序  FROM emp; 

     
说到这,大家估计开始琢磨到底什么区别了,同样,以求部门工资总和为例:


  1. -- 使用分组函数GROUP BY方式  
  2. SELECT deptno 部门编号,SUM(sal) 部门工资总和 FROM emp GROUP BY deptno; 

     
执行结果为:
下面再看一下:


  1. -- 使用分析函数 PARTITION BY方式  
  2. SELECT deptno 部门编号,SUM(SAL) OVER (partition by sal) 部门工资总和 FROM emp;  

输出结果为:


对于这个结果大家可能会比较奇怪,其实这也正是这两个函数的区别,如果还是觉得不清晰,再看下面这个更具体的例子:


  1.     SELECT deptno 部门名称 ,ename 员工姓名, sal 员工工资, SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) 所在部门工资总和  FROM emp;  

输出结果:


通过上面三个例子可以得出以下结论:

1、字面意思上来看,分组函数是对于整体而言的,分析函数是用来分析个体而言的,呈现的结果也验证了这一点;

2、对于GROUP BY而言,是从整体宏观上把握分组后的数据模块,对于PARTITION BY而言,它虽然也是讲数据分组,但是它是从每个数据模块的个体出发,进行相应操作的,比如上述工资总和,比如每个部门有100个员工,用了它之后会有100条求和累加数据显示出来;

总之一句话,GROUP BY将分组后的块看成一个整体,忽略个体;PARTITION BY也是看成整体,但是重视个体!

相关文章推荐

POI实战-java开发excel详解之常用操作-下拉列表

4.4 下拉列表 POI对下拉列表稍优于JXL,jxl纯java下build下拉列表会出现下拉列表数量限度(之前我曾经碰到过此类问题)。两个下拉列表原理:首先创建一列隐藏数据,然后将此列中的数据bu...

POI java excel 生成下拉列表

近日在工作中遇到使用POI生成excel模板的需求,数据库取出所有类型生成excel下拉列表供用户选择。两种方案,第一种的缺点是列表数据量不能超过128。 方案一 ```ruby require ‘r...

分组函数group by和Oracle中分析函数partition by的用法以及区别

今天有个同事给我打电话问我题目列出的这个问题,在日常开发中,确实它们俩都是与分组有关的,但是需要注意的是一个是分组函数另一个是分析函数,讲解用到的表就以Oracle中schema的scott的EMP表...

[Oracle]: Oracle分析函数 分组(partition by)

转自:http://www.cnblogs.com/linjiqin/archive/2012/04/04/2431975.htmlAnother 传送门: http://blog.csdn.net/...
  • hwj528
  • hwj528
  • 2016年09月30日 17:28
  • 124

Oracle-SQL-group by-报错:不是单组分组函数

报错:不是单组分组函数 实例:select deptno,count(empno) from emp; 报错:不是单组分组函数 原因: 1,  如果程序中使用了分组函数,则有两种情况可以使用: 程序中...

oracle分析函数系列之sum(col1) over(partition by col2 order by col3):实现分组汇总或递增汇总 .

语法:sum(col1) over(partition by col2 order by col3 ) 准备数据: DEPT_ID ENAME ...

Postgresql中的分组函数(group by 和 having)

在通过了WHERE过滤器之后,生成的输出表可以继续用GROUP BY 子句进行分组,然后用HAVING子句删除一些分组行。  Sql代码   SELECT select_lis...
  • dahuzix
  • dahuzix
  • 2015年09月21日 22:55
  • 2385

group by分组函数之rollup与cube用法

ROLLUP,是GROUP BY子句的一种扩展,可以为每个分组返回小计记录以及为所有分组返回总计记录。 CUBE,也是GROUP BY子句的一种扩展,可以返回每一个列组合的小计记录,同时在末尾加...

OLAP函数系列1:SqlServer中分组函数GROUP BY 的扩展:all, cube,roolup与grouping

GROUP BY,ALL,CUBE,ROLLUP,GROUPING

oracle的分析函数over(Partition by...) 及开窗函数

http://www.cnblogs.com/sumsen/archive/2012/05/30/2525800.html oracle的分析函数over 及开窗函数 一:分析函数over O...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:分组函数group by和Oracle中分析函数partition by的用法以及区别
举报原因:
原因补充:

(最多只允许输入30个字)