行转列聚合
一、开始之前
在进行操作之前,你需要知道以下的几个函数:
1、CONCAT(string A/col, string B/col…):
返回输入字符串连接后的结果,支持任意个输入字符串。
2、CONCAT_WS(separator, str1, str2,...):
它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。
3、COLLECT_SET(col):
函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生 array 类型字段。
二、准备数据
假设你拥有如下的用户数据
name | sex | age |
---|---|---|
Titan | 男 | 18 |
Goodman | 男 | 18 |
Cooper | 男 | 10 |
Missy | 女 | 10 |
Penny | 女 | 27 |
三、需求分析
把相同性别的同龄人归类到一起,输出如下的结果
info | name |
---|---|
女-10 | Missy |
女-27 | Penny |
男-10 | Cooper |
男-18 | Titan,Goodman |
四、创建Hive表并导入数据
1、建立user_info表,将数据导入到表中
CREATE table user_info( name string, sex string, age string ) row format delimited fields terminated by "\t"; load data local inpath './users.txt' into table user_info;
2、根据需求,查询数据,使用CONCAT拼接字段,用COLLECT_SET将多行转为去重列表
SELECT t1.info, CONCAT_WS(',', COLLECT_SET(t1.name)) name FROM ( SELECT name, CONCAT_WS('-', sex, age) info FROM user_info ) t1 GROUP BY t1.info;
最后,查看输出结果
info | name |
---|---|
女-10 | Missy |
女-27 | Penny |
男-10 | Cooper |
男-18 | Titan,Goodman |
列转行拆分
一、开始之前
在进行操作之前,你需要知道以下的几个函数:
1、EXPLODE(col):
将 hive 一列中复杂的 array 或者 map 结构拆分成多行。
2、LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:用于和 split, explode 等 UDTF 一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
二、准备数据
假设你拥有这样的电影数据
movie | category |
---|---|
《疑犯追踪》 | ["悬疑","动作","科幻","剧情"] |
《Lie to me》 | ["悬疑","警匪","动作","心理","剧情"] |
《战狼 2》 | ["战争","动作","灾难"] |
三、需求分析
将每个电影的分类拆分出来,展开数据
movie | category_name |
---|---|
《疑犯追踪》 | 悬疑 |
《疑犯追踪》 | 动作 |
《疑犯追踪》 | 科幻 |
《疑犯追踪》 | 剧情 |
《Lie to me》 | 悬疑 |
《Lie to me》 | 警匪 |
《Lie to me》 | 动作 |
《Lie to me》 | 心理 |
《Lie to me》 | 剧情 |
《战狼 2》 | 战争 |
《战狼 2》 | 动作 |
《战狼 2》 | 灾难 |
四、创建 movie_info 表并导入数据
create table movie_info( movie string, category array<string> ) row format delimited fields terminated by "\t" collection items terminated by ","; hive (db_titan)> load data local inpath 'movies.txt' into table movie_info; Loading data to table db_titan.movie_info Table db_titan.movie_info stats: [numFiles=1, totalSize=135] OK Time taken: 0.556 seconds
五、根据需求,将分类列表拆分,实现列转行
select movie, category_name from movie_info lateral view explode(category) table_tmp as category_name;
movie | category_name |
---|---|
《疑犯追踪》 | 悬疑 |
《疑犯追踪》 | 动作 |
《疑犯追踪》 | 科幻 |
《疑犯追踪》 | 剧情 |
《Lie to me》 | 悬疑 |
《Lie to me》 | 警匪 |
《Lie to me》 | 动作 |
《Lie to me》 | 心理 |
《Lie to me》 | 剧情 |
《战狼 2》 | 战争 |
《战狼 2》 | 动作 |
《战狼 2》 | 灾难 |
文章评论