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

原创 2016年08月30日 16:01:29

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

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

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

SELECT DEPTNO 部门编号,COUNT(*) AS 部门人数,SUM(sal) 部门工资总和 FROM emp GROUP BY deptno ORDER BY SUM(sal) DESC;
结果就不贴出来了,可以看到的是整个EMP表里面的数据分成了三条,每条数据针对一个部门,对于分组函数而言,它不关心每个分组里面到底是由谁构成的,或者是相对于个体,分组函数是站在一个宏观的角度上看待数据的;

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

SELECT deptno 部门名称 ,ename 员工姓名, sal 员工工资, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) 所在部门工资排序  FROM emp;
说到这,大家估计开始琢磨到底什么区别了,同样,以求部门工资总和为例:

-- 使用分组函数GROUP BY方式
SELECT deptno 部门编号,SUM(sal) 部门工资总和 FROM emp GROUP BY deptno;
执行结果为:
下面再看一下:

-- 使用分析函数 PARTITION BY方式
SELECT deptno 部门编号,SUM(SAL) OVER (partition by sal) 部门工资总和 FROM emp;
输出结果为:


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

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也是看成整体,但是重视个体!



Oracle分组函数

Oracle分组函数
  • DEMAisSB
  • DEMAisSB
  • 2016年11月24日 15:11
  • 3496

oracle总结之四———分组函数

分组函数的介绍分组函数作用于一组数据,并对一组数据返回一个值.常见的分组函数有: 函数名称 ...
  • fengruifang
  • fengruifang
  • 2011年04月15日 16:05
  • 5159

oracle_常用分组函数

oracle_常用分组函数 ①分组函数 1.max(column):求最大值,对数据类型没有要求,任意数据类型都可以 2.min(column):求最小值,对数据类型没有要求,任意数据类型都可以 3....
  • wzg775192833
  • wzg775192833
  • 2014年11月06日 17:06
  • 1407

Oracle第六章 分组函数习题答案

第六章 分组函数习题答案
  • qq_36411874
  • qq_36411874
  • 2017年06月23日 22:24
  • 823

傅老师课堂:Oracle高级查询之OVER (PARTITION BY ..)

为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。 注:标题中的红色order by是说明在使用该方法的时候必须要带上order by。 一、rank()/dense_...
  • ayou2008
  • ayou2008
  • 2012年01月05日 17:29
  • 27320

Oracle 之 OVER (PARTITION BY ..) 及开窗函数

oracle的分析函数over 及开窗函数 一:分析函数over Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是 对于每个组返回多行,...
  • icanlove
  • icanlove
  • 2014年11月14日 13:24
  • 667

oracle分析函数over partition by 和group by的区别

http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html partition by的用法
  • Yuli_li
  • Yuli_li
  • 2016年06月24日 14:51
  • 5834

oracle over (partition by )用法

oracle的分析函数over 及开窗函数   eg:  相关解析: 表t_pi_part  字段  id  code   name value 1  222     a value 2...
  • xiuhaijuanqiang
  • xiuhaijuanqiang
  • 2012年02月10日 13:51
  • 32259

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

作为合格的dba,这一块还是需要看懂和熟练应用的,
  • fengeh
  • fengeh
  • 2014年05月03日 21:10
  • 1349

oracle按照2个字段分区 over(partition by A,B order by C)

整理需求 笔者的业务理解能力一般,需求按照我的理解转化为数据库逻辑之后大致这个样子的:查询出TB_LOG中不同的SYSTEM_ID对应的每日的最新一条数据, 同时连接TB_LOG, TB_INFO...
  • yangjiegang8
  • yangjiegang8
  • 2017年08月24日 12:48
  • 535
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:分组函数group by和Oracle中分析函数partition by的用法以及区别
举报原因:
原因补充:

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