hive教程：group by having分组查询

作者：马育民 • • 阅读：10648

分组查询

如下面例子，根据性别分组，然后取出身高最高的记录

问题

查询不同部门的最高工资，如果是用人来分析查找，首先根据部门对数据进行分组，然后在每组中查找最高工资，如下图：

用sql语句进行查询，需要用到 分组查询，如下：

select max(sal),deptno from emp group by deptno;

语法

select 分组字段1，分组字段2,多行函数1,多行函数2 
from 表名 group by 分组字段1，分组字段2 
order by
注意：
  1. select 分组字段，必须与 group by 分组字段 一致，可以有多个分组字段
  2. select 分组字段后面可以 有多个 多行函数，但 不能 有 单行函数

例子

  1. 查询各个部门的最高工资，各个部门的人数
    select deptno,max(sal),count(*) from emp group by deptno;
  2. 查询各个职务的人数、最高工资
    select job,count(*) from emp group by job;
  3. 查询不同部门、不同职务的人数
    select deptno,job,count(*) from emp group by deptno,job
  4. 查询各个部门的平均工资
    select cast(avg(sal) as decimal(10,2))  ,deptno from emp group by deptno;

解释： decimal(10,2) 一共是10位数，其中2位是小数

分组条件查询

  1. 查询各个部门，职务不是“MANAGER”、“PRESIDENT”的平均工资
    select deptno,avg(sal) from emp where job not in ('MANAGER','PRESIDENT')  group by deptno;
    注意： where 在 group by 之前起作用，先筛选，然后再分组

having

问题

查询各个部门，职务不是“MANAGER”、“PRESIDENT”的平均工资，而且平均工资大于1500的记录

答案：

select deptno,avg(sal) from emp where job not in ('MANAGER','PRESIDENT')  group by deptno having avg(sal)>1500;

语法：

having是 分组后 的 数据筛选，允许使用 多行函数，必须和group by搭配使用。
在执行 having之前，已经执行完 多行函数

select 分组字段,多行函数 from 表名 group by 分组字段 having 多行函数 [比较运算符] 值

having与where的区别

  • where 在 group by 之前起作用，having在group by 之后起作用

    where是对字段进行筛选，having是对分组中的多行函数进行筛选

  • where后面 不能 有 多行函数，having后面可以有 多行函数

  • having 和group by搭配使用，where不需要

例子

  1. 查询不同部门、不同职务，且人数大于1的记录
    select deptno,job,count(*) from emp group by deptno,job having count(*)>1

问题

查询不同职务的平均工资，职务不包含“MANAGER”、“PRESIDENT”，一种用 where语句筛选，一种用 having语句筛选，并考虑 哪个效率高

  • where
    select job,avg(sal) from emp where job not in ('MANAGER','PRESIDENT') group by job
  • having
    select job,avg(sal) from emp group by job having job not in ('MANAGER','PRESIDENT')
  • where是 先 筛选 再 分组查询，having是 先 分组查询 再 筛选，所以是where效率高

总结

编写顺序

Select 列名,函数
From 表名
Where 条件
Group by 列名
Having 条件
Order by 列名

执行顺序

顺序：from -> where -> group by -> select -> having -> order by

练习

  1. 按部门统计，工资大于1200 的平均工资

  2. 统计各部门的平均工资，并且平均工资大于2000的记录

  3. 统计各部门下工资大于1200 的员工的平均工资，并且平均工资大于2000的

  4. 算出每个职位的员工数和最低工资

  5. 算出每个部门，每个职位的平均工资和平均奖金(平均值包括没有奖金) ，如果平均奖金大于300，显示“奖 金不错”，如果平均奖金100 到300，显示“奖金一般”，如果平均奖金小于100，显示“基本没有奖金”， 按部门编号降序，平均工资降序排列

  6. 列出员工表中每个部门的员工数，和部门编号

  7. 统计每个部门下，每个职位的平均奖金和总工资(也要算没奖金的人)

原文出处：/show_1IXlRrit42c.html

吉ICP备15003043号