hive 函数之二 窗口函数 与 分析函数
①排序函数
1. order by -- 全局排序
所有map端数据,只在一个reduce端进行,全局排序
2. sort by -- 对每一个reduce中进行局部排序
因此在使用sort by之前,需要先设置Reduce的数量>1(set mapreduce.job.reduces=n;)才会做局部排序
distribute by 是控制 map 的输出在reduer 中是如何划分的。
distribute by 会根据 KEY 的hash码 对 reduce 的个数 取模, 这样相同的KEY 会进入到同一个 reduce中
因此 distribute by 和 sort by 联合使用
②行列转换
1. 列转行
collect_list – 转为数组 不去重
collect_set – 转为数组 并去重
是hive提供的现成的UDAF函数
-- 列转行 collect_list 先将转化为不去重数组 concat_ws 再将数据遍历为指定字符遍历的字符串
select name, concat_ws(',',collect_list( concat(subject,":",result) ))
from tmp_stu
group by name
-- 行转列 collect_list 先将转化为去重数组 concat_ws 再将数据遍历为指定字符遍历的字符串
select name, concat_ws(',',collect_set( concat(subject,":",result) ))
from tmp_stu
group by name
2. 列转行
explode()
接受array(或map)作为输入,并将array(map)的元素作为单独的行输出。
UDTFs可以在 SELECT expression list 中使用,也可以作为 LATERAL VIEW 的一部分。 – 官方解释
注: 当SELECT expression list 使用UDTF函数的时候,hive只允许对拆分字段进行访问,不能与原字段组合。
2-1 LATERAL VIEW
Lateral View 用于和UDTF函数【如:explode,split】结合来使用。
lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一或者多行。lateral view再把结果组合,产生一个支持别名表的虚拟表。
主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。
LATERAL VIEW 主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能与原字段组合。
因此 lateral view与explode等udtf就是天生好搭档,explode将复杂结构一行拆成多行,然后再用lateral view做各种聚合。
-- 1. explode() 在 SELECT expression list 中使用, 其结果不能与tmp_stu2原始字段组合
select explode(split(cj,',')) AS myNewCol
from tmp_stu2
-- 2. explode() 与 LATERAL VIEW 联合适用 结果与tmp_stu2原始字段 name 组合
select name, cj_1
from tmp_stu2
lateral view explode(split(cj,',')) temp_view as cj_1
2-2. posexplode() 函数
posexplode() 与 explode() 的区别是: 行转列以后,还多一列数组元素对应下标的列
可以引申出一些其他的应用:
-- posexplode
select name, cj_1, cj_2
from tmp_stu2
lateral view posexplode(split(cj,',')) temp_view as cj_1, cj_2
-- posexplode 利用数组下标 产出对应 日期的多行
SELECT a1.id,
DATE_ADD(a1.start_date,a2.pos) AS data_date
FROM
( SELECT '1' as id ,
'2021-01-01' AS start_date,
'2021-01-31' AS end_date
) a1
LATERAL VIEW POSEXPLODE(SPLIT(SPACE(DATEDIFF(LEAST(a1.end_date,date_add(current_date(), -1)),a1.start_date)),'')) a2 AS pos,val
WHERE DATE_ADD(a1.start_date,a2.pos)<=date_add(current_date(), -1)
-- date_add(current_date(), -1) 昨日的日期
-- LEAST(n1, n2, n3) 获取中的最小值
-- DATEDIFF(d1, d2) 日期天数差值
-- SPACE(n1) 产生对应个数个空间
-- SPLIT() 产生数组 去列转行
③窗口函数
over()
SQL标准允许将所有聚合函数用做开窗函数,只需要在聚合函数后加over()即可。
– partition by对表进行分区然后聚合计算每个分区的数据
– order by子句可以对结果集按照指定的排序规则进行排序,并且在一个指定的范围内进行聚合运算。
over() 函数可以控制计算时的窗口大小。例如,over() 内部无参数时,默认将当前查询的所有结果作为输入。
over(partition by name) 每一行根据 name来区分窗口
over( order by id range between 1 preceding and 1 following ) range代表范围, preceding 向前, following向后
– 窗口范围当前行前后一行
over( order by id range between CURRENT ROW AND 1 following ) CURRENT ROW 代表 当前行
– 窗口范围当前行和后面一行
故:索引 over() 是伴随着聚合函数 进行聚合操作的。
使用窗口聚合函数
1-1. first_value() 按分组排序后,取范围内第一个值。
-- 根据name分组,result排序 取第一个值 赋值给组内每一行
select name, subject, result,
first_value(subject) over(partition by name order by cast(result as int) asc) first_value_asc,
first_value(subject) over(partition by name order by cast(result as int) desc) first_value_desc
from bpit_back_tmpdb.tmp_stu
1-2. last_value() last_value,按分组排序后,取范围内,最后一个值, 因为使用了默认定义框架:第一行到当前行的最后一个值。
-- 根据name分组,result排序 取第一个值 赋值给组内每一行
select name, subject, result,
first_value(subject) over(partition by name order by cast(result as int) asc) first_value_asc,
last_value(subject) over(partition by name order by cast(result as int) asc) last_value_desc
-- last_city的结果不是我们想要的。是因为使用了默认定义框架:第一行到当前行的最后一个值。
from bpit_back_tmpdb.tmp_stu
1-3. lag() 和 lead()
- lag(移动的字段, 移动的行数, 默认值) 向下移动
- lead(移动的字段, 移动的行数, 默认值) 向上移动
-- 分组 字段值移动 可以用来比较
select name, subject, result,
lag(result,1,'默认值') over(partition by name order by cast(result as int) asc) lag_1_value,
lead(result,1,'默认值') over(partition by name order by cast(result as int) asc) lead_1_value
from bpit_back_tmpdb.tmp_stu
1-4. ntile(n)
将每组平均分为n部分。
如果可以平均分配行数 则平均分配 到指定的数量(n)个桶中
如果不能平均分配,则优先分配较小编号的桶,并且各个桶中能放的行数最多相差1。
(1). -- 分组内 行数平均分配为2份
select name, subject, result,
ntile(2) over(partition by name order by cast(result as int) asc) ntile_value
from bpit_back_tmpdb.tmp_stu
(2). -- 课程总成绩平均分配成2份 与sum配合
select subject,
sum(cast(result as int)) as result,
ntile(2) over(order by sum(cast(result as int)) asc) ntile_value --over窗口为整体数据
from bpit_back_tmpdb.tmp_stu
group by subject
1-5. rank() , dense_rank()与 row_number()
排序方式不同:
- rank() 1,2,3,3,5
- dense_rank() 1,2,3,3,4
- row_number() 1,2,3,4,5
1-6. CUME_DIST() 和 PERCENT_RANK()
CUME_DIST 小于等于当前值的行数/分组内总行数
PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
注: rank值的排序方式是 1,2,3,3,5
-- CUME_DIST 全局排序 小于等于当前值的行数/分组内总行数
select name, subject, result,
CUME_DIST() over(order by cast(result as int) asc) as cume_1
from bpit_back_tmpdb.tmp_stu
where name='小丽'
-- CUME_DIST 分组排序 小于等于当前值的行数/分组内总行数
select name, subject, result,
CUME_DIST() over(partition by name order by cast(result as int) asc) as cume_2
from bpit_back_tmpdb.tmp_stu
…
-- PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
select name, subject, result,
PERCENT_RANK() over(order by cast(result as int) asc) as cume_1
from bpit_back_tmpdb.tmp_stu
where name='小丽'
④OLAP 多维度聚合函数
1-1. GROUPING SETS
grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
等价于将不同维度的GROUP BY结果集进行UNION ALL。
GROUPING__ID,表示结果属于哪一个分组集合。
-- 分别对 name,subject,name+subject 进行聚合
select name,
subject,
sum(cast(result as int)) as sum_result,
GROUPING__ID
from bpit_back_tmpdb.tmp_stu
group by name, subject
GROUPING SETS (name,subject,(name, subject))
-- 等价于
select name, subject, sum(result) as sum_result, 3 as GROUPING__ID from tmp_stu group by name, subject
union all
select name, null as subject, sum(result) as sum_result, 1 as GROUPING__ID from tmp_stu group by name
union all
select null as name, subject, sum(result) as sum_result, 2 as GROUPING__ID from tmp_stu group by subject
1-2. CUBE
根据GROUP BY的维度的所有组合进行聚合。 包括 对所有数据group
-- 对所有纬度聚合
select name,
subject,
sum(cast(result as int)) as sum_result,
GROUPING__ID
from bpit_back_tmpdb.tmp_stu
group by name, subject
with CUBE
-- 等价于
select null as name, null as subject, sum(result) as sum_result, 0 as GROUPING__ID from tmp_stu
union all
select name, subject, sum(result) as sum_result, 3 as GROUPING__ID from tmp_stu group by name, subject
union all
select name, null as subject, sum(result) as sum_result, 1 as GROUPING__ID from tmp_stu group by name
union all
select null as name, subject, sum(result) as sum_result, 2 as GROUPING__ID from tmp_stu group by subject
1-3. ROLLUP
是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合。
-- 比如:
select name,
subject,
sum(cast(result as int)) as sum_result,
GROUPING__ID
from bpit_back_tmpdb.tmp_stu
group by name, subject
with ROLLUP
结果包含上面cube中的 0, 1, 3 (带有最左侧 name的group 和 对所有数据group)