一、GRPUP BY
作用:用于分组
注意事项:
- Hive基本内置聚合函数与GROUP BY 一起使用
- 如果没有指定GROUP BY 字句,则默认聚合整个表
- 除聚合函数外,所选的其他列也必须包含在group by 中
- GROUP BY支持使用CASE WHEN或表达式
示例
以表中城市分组
select city ,count(1) from emp_psn group by city;
以城市分组对phone列进行统计查询城市,注意的是group by后面要把列写全。多不行,漏也不行。
select emp_id,city,coount(phone) from emp_psn group by city,emp_id;
二、HAVING
作用:对 group by聚合结果的条件过滤
注意事项;
- 可以避免在GROUP BY之后使用子查询
- HAVING之后可以使用表达式,但不建议使用
示例
以age分组查询age<=1的
-- having使用
select sex_age.age from employee group by sex_age.age having count(*) <= 1;
-- 使用子查询代替having
select a.age from ( select count(*) as cnt, sex_age.age
from employee group by sex_age.age ) a where a.cnt <= 1;
三、基础聚合
基础聚合函数
max, min, count, sum, avg
max(distinct col1)、avg(col2)等
collect_set, collect_list:返回每个组列中的对象集/列表
注意事项
- 一般与GROUP BY一起使用
- 可应用于列或表达式
- 对NULL的count聚合为0
- select count(null) = 0