列转行
数据文件内容 player.txt
詹姆斯|小皇帝|2003|骑士,热火,湖人|3
保罗|三炮|2005|黄蜂,快船,火箭|0
韦德|闪电侠|2003|热火,骑士,公牛|3
安东尼|甜瓜|2003|掘金,尼克斯,雷霆,火箭|0
需求内容
将选秀年份相同且冠军数量相同的球员姓名用+拼接展示
2003,0|安东尼
2003,3|詹姆斯+韦德
2005,0|保罗
涉及函数解析
CONCAT_WS
CONCAT(string A/col, string B/col…)：
返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS 是一个特殊形式的 CONCAT()()。
CONCAT_WS(separator, str1, str2,...)
第一个参数剩余参数间的分隔符。
分隔符可以是与剩余参数一样的字符串。
如果分隔符是 NULL，返回值也将为 NULL。
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
分隔符将被加到被连接的字符串之间;
COLLECT_SET
COLLECT_SET(col)：
函数只接受基本数据类型，
它的主要作用是将某字段的值进行去重汇总，
产生array类型字段。
开始实现
创建表接收数据
CREATE TABLE NBA_PLAYER(
player_name string comment "球员姓名",
nickname string comment "外号",
begin_year varchar(4) comment "选秀年份",
join_team string comment "效力过的球队",
champion_count int comment "总冠军次数"
)ROW FORMAT DELIMITED FIELDS TERMINATED BY "|";
把数据从本地加载进数据表中
LOAD DATA LOCAL INPATH '/home/hadoop/player.txt'
OVERWRITE INTO TABLE NBA_PLAYER ;
拼接字段函数 CONCAT_WS
SELECT CONCAT_WS(",",begin_year,CAST(champion_count AS STRING)) group_info,player_name
FROM NBA_PLAYER;
-- 查询结果
2003,3 詹姆斯
2005,0 保罗
2003,3 韦德
2003,0 安东尼
完成列转行 COLLECT_SET
SELECT A.group_info,
CONCAT_WS("+",COLLECT_SET(A.player_name))
FROM (
SELECT CONCAT_WS(",",begin_year,CAST(champion_count AS STRING)) group_info,
player_name
FROM NBA_PLAYER
) A
GROUP BY A.group_info;
-- 查询结果
2003,0 安东尼
2003,3 詹姆斯+韦德
2005,0 保罗
简单例子：把选秀年份相同球员找到并放到一起+分割展示
SELECT begin_year,
CONCAT_WS("+",COLLECT_SET(player_name))
FROM NBA_PLAYER
GROUP BY begin_year;
-- 查询结果
2003 詹姆斯+韦德+安东尼
2005 保罗
行转列
需求内容
统计每个球队效力过球员人数
涉及函数解析
- explode（array<string>）
explode函数以array类型数据输入，然后对数组中的数据进行迭代，返回多行结果，一行一个数组元素值
ARRAY函数是将一列输入转换成一个数组输出。
Hive的表生成函数只是生成了一种数据的展示方式，
而无法在表中产生一个其他的列。
因此这块需要使用LATERAL VIEW 功能来进行处理。
LATERAL VIEW将explode生成的结果当做一个视图来处理。
- LATERAL VIEW
# 使用方法
LATERAL VIEW udtf(expression) tableAlias AS columnAlias
# 用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
LATERAL VIEW 会将UDTF生成的结果放到一个虚拟表中
然后这个虚拟表会和输入行进行join
来达到连接UDTF外的select字段的目的
- split
# 使用方法:
split(string str, string pat)
返回值: array
按照pat字符串分割str，会返回分割后的字符串数，
正则表达式中的特殊符号作为分隔符时，需做转义 (前缀加上\)
实现 字符串转数组 -> 行转列 -> 分组统计
SELECT player_name,
join_team_name
FROM (
SELECT player_name,
split(join_team,",") AS array_join_team
FROM NBA_PLAYER
) A
LATERAL VIEW EXPLODE(array_join_team) tmpTable AS join_team_name;
--- 查询结果
詹姆斯 骑士
詹姆斯 热火
詹姆斯 湖人
保罗 黄蜂
保罗 快船
保罗 火箭
韦德 热火
韦德 骑士
韦德 公牛
安东尼 掘金
安东尼 尼克斯
安东尼 雷霆
安东尼 火箭
SELECT join_team_name ,
COUNT(*)
FROM (
SELECT player_name,
join_team_name
FROM (
SELECT player_name,
split(join_team,",") AS array_join_team
FROM NBA_PLAYER
) A
LATERAL VIEW EXPLODE(array_join_team) tmpTable AS join_team_name
) B
GROUP BY join_team_name;
--- 查询结果
公牛 1
尼克斯 1
快船 1
掘金 1
湖人 1
火箭 2
热火 2
雷霆 1
骑士 2
黄蜂 1
