一.Hive聚合运算 - GROUP BY
GROUP BY用于分组
- Hive基本内置聚合函数与GROUP BY一起使用
- 如果没有指定GROUP BY子句,则默认聚合整个表
- 除聚合函数这一列外,所选的其他列也必须包含在GROUP BY中,在前面查询的时候可以不加,不会报错,但是看不出来结果代表的意义
- GROUP BY支持使用CASE WHEN或表达式
select category, max(offervalue) from offers group by category;
-- group by使用表达式
select
if(category > 4000, 'GOOD', 'BAD') as newcat,
max(offervalue)
from offers
group by category if(category > 4000, 'GOOD', 'BAD');
Hive聚合运算 - HAVING
- HAVING:对GROUP BY聚合结果的条件过滤- 可以避免在GROUP BY之后使用子查询
- HAVING之后可以使用表达式,但不建议使用,会造成效率大大降低
-- 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;
Hive聚合运算 - 基础聚合 基础聚合函数
- max, min, count, sum, avg- max(distinct col1)、avg(col2)等- collect_set, collect_list:返回每个组列中的对象集/列表
注意事项 - 一般与GROUP BY一起使用
- 可应用于列或表达式
- 对NULL的count聚合为0,即过滤了NULL
二.窗口函数 - 概述
窗口函数是一组特殊函数
- 扫描多个输入行来计算每个输出值,为每行数据生成一行结果
- 可以通过窗口函数来实现复杂的计算和聚合
语法
Function (arg1,..., arg n) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_clause>])
- PARTITION BY类似于GROUP BY,未指定则按整个结果集
- 只有指定ORDER BY子句之后才能进行窗口定义,窗口定义的范围窗口,行窗口都需要根据整个数据的顺序决定的
- 可同时使用多个窗口函数
- 过滤窗口函数计算结果必须在外面一层
- 按功能可划分为:排序,聚合,分析
1. 窗口函数 - 排序
ROW_NUMBER()
- 对所有数值输出不同的序号,序号唯一连续 (1,2,3)
RANK()
- 对相同数值,输出相同的序号,下一个序号跳过(1,1,3)
DENSE_RANK()
- 对相同数值,输出相同的序号,下一个序号连续(1,1,2)
根据需要使用的不同功能,去选择使用的函数,使用方式一致,只不过功能不太一样。主要掌握上面三个。
NLITE(n)
- 将有序的数据集合平均分配到n个桶中(若不能均分一般第一个桶数据会多些), 将桶号分配给每一行,根据桶号,选取前或后 n分之几的数据
- 举例:若想查询订单记录的前1/3记录,可用NLITE(3)平均分成三份再套个查询语句使用where条件桶号=1即可实现
PERCENT_RANK()
- (目前排名- 1)/(总行数- 1),值相对于一组值的百分比排名
-- 窗口函数 排序类 给所有的员工薪资排序,从低到高,针对所有员工。
SELECT
name, dept_num, salary,
ROW_NUMBER() OVER (order by salary) AS row_num, //over() 括号内不写的话默认是按照整个行进行排序,这里还是需要按照薪资排序
RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS rank, //按照部门对部门内员工薪资排序
DENSE_RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS dense_rank,
PERCENT_RANK() OVER(PARTITION BY dept_num ORDER BY salary) AS percent_rank,
NLITE(2) OVER(PARTITION BY dept_num ORDER BY salary) AS ntile
FROM employee_contract
ORDER BY dept_num, salary;
2. 窗口函数 - 聚合
COUNT() 计数,可以和DISTINCT一起用
SELECT
COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS
BETWEEN 1 PRECEDING AND 1 FOLLOWING)
SUM():求和
AVG():平均值
MAX()/MIN(): 最大/小值
从Hive 2.1.0开始在OVER子句中支持聚合