一、行转列
1.相关函数
- CONCAT(string A/col, string B/col…)
- 返回输入字符串连接后的结果,支持任意个输入字符串;
- CONCAT_WS(separator, str1, str2,...)
- 它是一个特殊形式的 CONCAT()。
- 第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。
- 如果分隔符是 NULL,返回值也将为 NULL。
- 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
- 注意: CONCAT_WS must be "string or array<string>"
- COLLECT_SET(col)
- 函数只接受基本数据类型
- 它的主要作用是将某字段的值进行去重汇总,产生 Array 类型字段。
- COLLECT_LIST(col)
- 函数只接受基本数据类型
- 它的主要作用是将某字段的值汇总(可以有重复),产生 Array 类型字段。
2.数据准备
现有表及数据类型:person_info(name string, constellation string, blood_type string)
name | constellation | blood_type |
---|---|---|
悟空 | 白羊座 | A |
大海 | 射手座 | A |
小宋 | 白羊座 | B |
八戒 | 白羊座 | A |
凤姐 | 射手座 | A |
苍苍 | 白羊座 | B |
3.需求及实现
把星座和血型一样的人归类到一起,结果如下:
射手座,A 大海|凤姐
白羊座,A 悟空|八戒
白羊座,B 小宋|苍苍
实现:
SELECT
t1.c_b,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
name,
CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1
GROUP BY t1.c_b
二、列转行
1.函数说明
- EXPLODE(col)
- 将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
- LATERAL VIEW
- 用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
- 解释:用于和 split, explode 等 UDTF 一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
- 更详细的可移步看:Lateral View 与 explode() 结合使用
2.数据准备
有数据表及类型:movie_info(movie string,category string)
movie | category |
---|---|
《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
《战狼 2》 | 战争,动作,灾难 |
3.需求及实现
将电影分类中的数组数据展开。结果如下:
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼 2》 战争
《战狼 2》 动作
《战狼 2》 灾难
实现:
SELECT
movie,
category_name
FROM movie_info
lateral view explode(split(category,",")) movie_info_tmp AS category_name;