频道栏目
首页 > 数据库 > 其他综合 > 正文
统计函数与分组查询
2016-09-18 09:16:03         来源:ShunXiangL的博客  
收藏   我要投稿

1、分组统计查询

1.1 统计函数(分组函数)

  在之前学习过一个COUNT()函数,此函数的功能是用于统计一张表中的数据量,那么实际上这就属于一种统计函数,在SQL语法中,定义了五个常用的统计函数:COUNT()、SUM()、AVG()、MAX()、MIN()。

范例:要求查询出公司总人数、每月支付的总工资、以及公司的平均工资、最高工资、最低工资。

SELECT COUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM emp;

范例:求出公司雇佣雇员之中,最早和最晚的雇佣日期。

SELECT MIN(hiredate),MAX(hiredate) FROM emp;

  针对于以上的几个统计函数,还有一些非常重要的说明:
说明一: 给出的五个函数之中,如果内统计的数据表之中没有任何数据存在,那么只有COUNT()函数返回数据。

SELECT COUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM bonus;

  发现只有 COUNT() 函数返回了0,其他的函数返回的数据都是NULL,所以得出结论:COUNT()函数不管表中是否有内容,永远都会有一个具体的数据返回。

说明二: 关于COUNT()函数使用的问题
  在现在已知使用的 COUNT() 函数采用的方式 “COUNT(*)” , 实际上也可以使用 “COUNT(字段)” 来完成,例如,现在观察如下代码。

SELECT COUNT(*),COUNT(empno) FROM emp;

面试题: 请说出”COUNT(*)”,”COUNT(DISTINCT 字段)”还有”COUNT(字段)”的区别?

SELECT COUNT(*),COUNT(empno),COUNT(DISTINCT job),COUNT(comm) FROM emp;

这里写图片描述

  COUNT(*):可以直接准确的返回表中的数据;
  COUNT():如果此字段上的内容不为NULL,其结果与COUNT()一致,如果有NULl数据,则NULL不统计;
  COUNT(DISTINCT 字段):重复的数据不统计。
 

1.2、分组统计

  在学习分组统计之前,首先来思考一个问题,什么情况下可能会出现分组的问题?
  例如:生活之中,男女厕所是分开的,如果说现在要求男生和女生个一组,按照每个部门分组进行拔河比赛。所以所谓的分组一定是要求有重复,如果换回到数据库之中,就意味者列上存在重复。但是也不排除一个人一组,只是这样做的意义不大。但是分组之后就应该可以进行统计操作了,就可以使用统计函数了。
  如果要想在SQL之中实现分组,可以使用如下语法完成,增加一个 GROUP BY 子句。

SELECT [DISTINCT] 列 [别名] | 统计函数 
FROM 表名称 [别名], 表名称 [别名], .....
[WHERE 条件(s)]
[GROUP BY 分组字段]
[ORDER BY 字段 [ASC | DESC], 字段 [ASC | DESC], ....]

范例:要求查询出每种职位的雇佣人数、平均工资,应该按照 job 分组。

SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;

这里写图片描述

范例:按照部门编号分组,求出每个部门的人数,平均工资,最高及最低工资
。应该按照deptno分组。<喎"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwcmUgY2xhc3M9"brush:sql;"> SELECT deptno,COUNT(deptno),AVG(sal),MAX(sal),MIN(sal) FROM emp GROUP BY deptno;

这里写图片描述

  以上实现了基本的分组操作,但是进行分组统计查询的时候也是会存在若干限制的。

限制一:统计函数如果不结合GROUP BY 使用时,在 SELECT 子句之中, 只能够单组使用,不能够出现任何的其他字段。

// 正确代码
SELECT COUNT(empno) FROM emp;

// 错误代码,不是单独的分组函数
SELECT COUNT(empno),ename FROM emp;

这里写图片描述

限制二:如果查询之中出现了 GROUP BY 子句时,SELECT 子句只允许出现分组与统计函数,其他字段都不允许出现。

// 正确代码
SELECT job,COUNT(empno)
FROM emp
GROUP BY job;

// 错误代码,不是GROUP BY 表达式
SELECT job,COUNT(empno),ename
FROM emp
GROUP BY job;

这里写图片描述

限制三:统计函数允许嵌套,但是嵌套之后的统计查询的 SELECT 子句之中不允许再出现任何字段,包括分组字段,例如:要求查询出平均工资最高的职位中的最高工资

// 正确代码
SELECT MAX(AVG(sal)) 
FROM emp
GROUP BY job;

// 错误代码,不是单组分组函数
SELECT job,MAX(AVG(sal)) 
FROM emp 
GROUP BY job;

这里写图片描述

  以上的操作都只是针对于雇员(emp)一张表的分组统计,那么如果现在有这样一个要求。

范例:查询出每个部门的名称、雇员人数、平均工资、平均服务年限。
  · 确定所需要的数据表:
    " - dept表:部门名称;
    | - emp表:各个统计信息(empno,sal,hiredate);
  · 确定已知的关联字段:
    | - 雇员和部门关联:emp.deptno=dept.deptno
第一步 : 转变一下操作的思路,如果现在给出的查询要求变化一下:查询出每个雇员的编号、部门名称、工资、雇佣日期。

SELECT e.empno,d.dname,e.sal,e.hiredate 
FROM emp e,dept d
WHERE e.deptno=d.deptno;

这里写图片描述

第二步 : 分组的前提条件,列上存在重复,只要是存在重复能否都分组,什么结构叫表?包含行和列两个信息的结构都成为表,那么以上以上的查询结果是否满足一张表的形式? YES,所以现在如果把查询结果当作一张临时表,那么现在就可以直接为临时表进行分组了。

SELECT d.dname,COUNT(e.empno),AVG(e.sal),
    AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12) avgyear 
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;

这里写图片描述

第三步: 可是现在题目要求是所有部门,在dept表中有四个部门,但是现在只显示出了三个部门,所以应该考虑出现外连接。

SELECT d.dname,COUNT(e.empno),AVG(e.sal),
    AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12) avgyear 
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.dname;

这里写图片描述

  所以通过以上的结论,可以发现,进行分组操作的时候并不一定非要针对实体表进行分组统计,针对于临时表(查询结果)也进行分组。

范例: 统计出公司每个工资等级人数,平均工资
  · 确定所需要的数据表:
    " - salgtrade表:工资等级;
    | - emp表:统计信息;
  · 确定已知的关联字段:
    | - 雇员和工资等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

第一步: 换一个思路,首先查询出雇员的编号,工资,工资等级。

SELECT e.empno,e.sal,s.grade
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal;

这里写图片描述

第二步: 针对重复的数据进行分组,此处还是临时表数据。

SELECT COUNT(e.empno),AVG(e.sal),s.grade
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade;

这里写图片描述

  以上的所有的操作都只针对于一个单独的字段实现了分组,那么在分组操作之中,也定义了可以针对于多个字段进行分组(多个字段的内容都是重复的),此时的语法结构如下。

SELECT [DISTINCT] 列 [别名], 列 [别名], ....| 统计函数 
FROM 表名称 [别名], 表名称 [别名], .....
[WHERE 条件(s)]
[GROUP BY 分组字段, 分组字段, 分组字段, ....]
[ORDER BY 字段 [ASC | DESC], 字段 [ASC | DESC], ....]

范例: 查询出每个部门的编号、名称、位置、部门人数、最高工资、最低工资
  · 确定所需要的数据表:
    | - dept表:每个部门的编号,名称,位置;   
    | - emp表:统计信息;
  · 确定已知的关联字段:
    | - 雇员和部门: emp.deptno=dept.deptno;
    
第一步: 转变思路,要求查询出每个雇员的编号,工资,部门编号,名称,位置。

SELECT e.empno,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno;

这里写图片描述

第二步: 在以上的查询结果之中可以发现,deptno,dname,loc 三个字段都是同时重复者,所以在这种情况下,就可以使用多字段分组,而使用多字段分组的最大好处是可以在 SELECT 子句之中出现多点字段信息。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc;

这里写图片描述

范例: 要求平均工资高于1500的职位名称、雇员人数、平均工资。
第一步: 查询出每个职位名称,雇员人数,平均工资

SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;

这里写图片描述

第二步: 现在要的不是全部信息,应该是对查询结果进行过滤,首相会想到使用 WHERE 子句,于是下面习惯性的编写程序。

// 错误代码
SELECT job,COUNT(empno),AVG(sal)
FROM emp
WHERE AVG(sal)>1500
GROUP BY job;

这里写图片描述

  发现现在在 WHERE 子句上出现了错误信息,直接告诉用户 WHERE 无法直接使用分组函数。为什么呢?
  为了解释此问题,下面思考这样的一个实际生活场景:国家每年都啊要挑选年满18周岁的青少年应征入伍,每个地区征的兵不会在本地服役,而在征兵完成之后,要统计每个部队新兵的人数,平均学历,平均身高,后来国家需要为国旗班挑选病原,那么请问,以上的若干操作,如果换到SQL之中,各个子句该如何分配呢?
   · FROM 子句:全体中国公民;
   · WHERE 子句:年满18周岁;
   · GROUP BY 子句:按照籍贯分组,以分配部队;
   · 为国旗班挑选兵源?肯定挑选平均身高185的部队进行挑选,那么此步是在 GROUP BY 之后;

这里写图片描述

   可以发现 WHERE 子句是不可能满足分组之后的数据筛选的,于是此时有了一个新的子句: HAVING

SELECT [DISTINCT] 列 [别名], 列 [别名], ....| 统计函数 
FROM 表名称 [别名], 表名称 [别名], .....
[WHERE 条件(s)]
[GROUP BY 分组字段, 分组字段, 分组字段, ....]
[HAVING 条件(s)]
[ORDER BY 字段 [ASC | DESC], 字段 [ASC | DESC], ....]

范例:利用 HAVING 进行分组之后的数据过滤

SELECT job,COUNT(empno),AVG(sal)
FROM emp
HAVING AVG(sal)>1500
GROUP BY job;

这里写图片描述

问题: 如何区分使用 WHERE 和 HAVING   

  · WHERE 是在 GROUP BY 子句执行之前使用,目的是针对于全部数据筛选,WHERE 不能使用统计函数;
  · HAVING 是在 GROUP BY 子句执行之后使用,目的是针对于分组后的数据筛选,HAVING 可以使用统计函数。
      
  

1.4、练习

1、显示非销售人员工作名称以及从从事同一工作雇员的月工资总和,并且要满足从事同一工作的雇员的月工资合计大于 $5000,输出结果按月工资的合计升序排序。

第一步: 显示非销售人员(SALESMAN)

SELECT *
FROM emp
WHERE job<>'SALESMAN';

这里写图片描述

第二步: 从事同一工作雇员的月工资总和,按照职位分组。

SELECT job,sum(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job;

这里写图片描述

第三步: 针对分组后的数据再次筛选,使用HAVING 子句

SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000;

这里写图片描述

第四步: 输出结果按月工资的合计升序排列,使用 ORDER BY,而且 ORDER BY 是在最后执行的,可以使用 SELECT 子句之中定义的别名。

SELECT job,SUM(sal) sum
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY sum ASC;

这里写图片描述
 

2、查询出所有领取佣金和不领取佣金的雇员人数,平均工资。

  首先对于习惯性的思维,应该使用 comm 字段进行分组统计。

SELECT comm,COUNT(empno),AVG(sal)
FROM emp
GROUP BY comm;

这里写图片描述
  
  但是发现,现在没有得到一个预期的结果,它是把 comm 的每个值都分组了,所以现在发现直接出答案不太可能。
  那么现在换一个思路,先不考虑分组,考虑两种查询:
   · 查询一:查询出所有领取佣金的雇员人数、平均工资、不用分组,WHERE 直接筛选

SELECT '领取佣金' info, COUNT(empno),AVG(sal) 
FROM emp 
WHERE comm IS NOT NULL;

这里写图片描述

   · 查询二:查询出所有不领取佣金的雇员人数、平均工资、不用分组,WHERE 直接筛选

SELECT '不领取佣金' info, COUNT(empno),AVG(sal) 
FROM emp 
WHERE comm IS NULL;

这里写图片描述

  现在发现以上两个查询返回的最终结构都一样,可以考虑将查询连接一下。

SELECT '领取佣金' info, COUNT(empno),AVG(sal) 
FROM emp 
WHERE comm IS NOT NULL
    UNION
SELECT '不领取佣金' info, COUNT(empno),AVG(sal) 
FROM emp 
WHERE comm IS NULL;

这里写图片描述
 

1.4、总结

到现在为止,对于 SQL语法 的基本结构已经彻底了解了,再次回顾一下执行顺序:

FROM 子句:确定数据来源,而且这个来源可能是一张表,也可能是一种查询(临时表);

WHERE 子句:针对显示的数据进行条件过滤,可以编写多个条件;

GROUP BY 子句:对数据进行分组,分组是针对于 WHERE 筛选之后的数据分组;

HAVING 子句:针对于分组之后的数据进行再次的过滤,可以利用统计函数过滤;

SELECT 子句:确定要显示的数据列或者是统计函数;

ORDER BY 子句:对所有数据的显示结果进行排序,并且可以使用 SELECT 子句定义的别名。

点击复制链接 与好友分享!回本站首页
上一篇:PetShop上扒下来的SqlServerHelper和OracleHelper
下一篇:Redis探索之旅(12)-Redis主从架构复制原理
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站