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

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

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

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
  • 6042

Oracle 分析函数与聚合函数(重点理解)

一、Oracle分析函数的语法与作用:   rank ( )             over ( [query_partition_clause]order_by_clause ) dense_r...
  • myflysun
  • myflysun
  • 2017年04月22日 21:14
  • 855

group by 与 partition by的区别

group by 与 partitionby的区别 一、首先创建一个表 CREATE TABLE [dbo].[tb_Student]( [ID] [int] NOTNULL, [...
  • MsdnWoo
  • MsdnWoo
  • 2015年12月16日 22:39
  • 1701

oracle数据库--关于使用分组函数以及group by的注意点

一 报错:不是单组分组函数 如果程序中使用了分组函数,则有两种情况可以使用: 1 程序中存在group by,并指定了分组条件,这样可以将分组条件一起查询出来 2 如果不使用分组的...
  • u012411414
  • u012411414
  • 2015年07月15日 01:42
  • 1406

group by与partition by用法

group by与partition by用法详解
  • cyl937
  • cyl937
  • 2014年02月25日 20:25
  • 1498

oracle 分组函数与group by正确用法详解与详细解释

oracle数据库 分组函数与group by正确用法详解与详细解释 1.查询时同时查询了分组函数列和非分组函数列就需要使用group by,但是仅仅查询分组函数列可以不结合group by使用...
  • m0_37857602
  • m0_37857602
  • 2017年08月31日 17:01
  • 321

group by 不是单组分组函数

在select子句中除了分组函数外没有其他列的时候,才可以用group by,否则group by子句中的列必须与select子句的列相同。 如: 正确:select a,b,sum(c) fro...
  • zhangzhen6960
  • zhangzhen6960
  • 2012年01月11日 11:04
  • 1123

sql实现oracle分析函数功能 over partition by

oracle、hive都比较好支持分析函数(如sum() over partition by),db2就一般,mysql、sqlite直接就不支持。 因此有必要学会用SQL实现分析函数。 sum()...
  • textboy
  • textboy
  • 2016年04月12日 15:12
  • 988

partition by 与group by 区别

今天看到一个老兄的问题, 大概如下: 查询出部门的最低工资的userid 号 表结构: D号      工资      部门 userid  salary   dept   1      2000  ...
  • java_greenhand
  • java_greenhand
  • 2011年03月24日 13:39
  • 3964
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:分组函数group by和Oracle中分析函数partition by的用法以及区别
举报原因:
原因补充:

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