技术交流网站开发例子,前端面试,越秀网站建设方案,企业网站seo模板高级聚合函数
多进一出#xff08;多行输入#xff0c;一个输出#xff09;
普通聚合函数#xff1a;count、sum ...
1#xff09;collect_list#xff08;#xff09;#xff1a;收集并形成 list 集合#xff0c;结果不去重
select sex,collect_list(job)
from e…高级聚合函数
多进一出多行输入一个输出
普通聚合函数count、sum ...
1collect_list收集并形成 list 集合结果不去重
select sex,collect_list(job)
from employee
group by sex;
--女 [行政,研发,行政,前台]
--男 [销售,研发,销售,前台]
2collect_set收集并形成 set 集合结果去重
select sex,collect_set(job)
from employee
group by sex;
--女 [行政,研发,前台]
--男 [销售,研发,前台]
案例
1每个月的入职人数以及姓名
select month(replace(hire_date,/,-)) as month,count(*) cnt,collect_list(name) as name_list
from employee
group by month(replace(hire_date,/,-));
运行结果
month cnt name_list
4 2 [宋青书,周芷若]
6 1 [黄蓉]
7 1 [郭靖]
8 2 [张无忌,杨过]
9 2 [赵敏,小龙女] 炸裂函数UDTF
接受一行数据输出一行或多行数据。
TFTable-Genrating Functions表生成函数也就是说这个函数的结果是一张表。
1、常用 UDTF - explodearrayT a
explodearrayT a接受一个数组类型的参数它会把这一个数组炸裂成一个列多行。
语法
select explode(array(a,b,c))as item;
-- item
-- a
-- b
-- c
2、 常用 UDTF - explodeMapK,V m
返回多行2列keyvalue。
语法
注意不加别名时它默认的字段也是 key 和 value我们自定义多个字段名时需要加括号。
select explode(map(hadoop,1,spark,2)) as (key,value);
-- key value
-- hadoop 1
-- spark 2 3、常用 UDTF - posexplodearrayT a
接受一个数组 array pos 的意思是 position 也就是数组的下标。它返回多行两列一列为 pos索引 一列是 val值。
select posexplode(array(a,b,c));
-- pos val
-- 0 a
-- 1 b
-- 2 c
4、常用 UDTF - inlinearraystructf1:T1,...,fn:Tn a
它接受一个 结构体数组 返回多行多列列数结构体的属性数量。
注意每个结构体的属性数量必须一致。
select inline(array(named_struct(id,1,name,zs,age,15),named_struct(id,2,name,ls,age,17),named_struct(id,3,name,ww,age,23))) as (id,name,age);
运行结果 Lateral View常用
Lateral View 通常与UDTF 配合使用。它可以将UDTF应用到源表的每行数据UDTF会将每行数据转换为一行或多行Lateral View会将源表中每行的输出结果与该行连接起来形成一个虚拟表。 数据准备
create table movie_info(movie string, --电影名称category string --电影分类
)
row format delimited fields terminated by \t;insert overwrite table movie_info
values (《疑犯追踪》, 悬疑,动作,科幻,剧情),(《Lie to me》, 悬疑,警匪,动作,心理,剧情),(《战狼2》, 战争,动作,灾难);
函数演示
selectmovie,category_name
from
movie_info
lateral view
explode(split(category,,)) movie_info_tmp as category_name;
运行结果 select cate,count(*)
from (select movie,catefrom (select movie,split(category,,) catesfrom movie_info)t1 lateral view explode(cates) tmp as cate
)t2
group by cate;
运行结果 窗口函数
明天写
