目录
- 1 工作应用场景
- 2 行转列：多行转多列
- 2.1 需求
- 2.3 实现
- 3 行转列：多行转单列
- 3.1 需求
- 3.2 concat
- 3.3 concat_ws
- 3.4 collect_list
- 3.5 concat_set
- 3.6 实现
- 4 列转行：多列转多行
- 4.1 需求
- 4.2 union
- 4.3 union all
- 4.4 实现
- 5 列转行：单列转多行
- 5.1 需求
- 5.2 explode
- 5.3 实现
1 工作应用场景
实际工作场景中经常需要实现对于Hive中的表进行行列转换操作，例如当前ADS层的数据表，我们统计得到每个小时不同维度下的UV、PV、IP的个数，而现在为了构建可视化报表，得到每个小时的UV、PV的线图，观察访问趋势，我们需要构建如下的表结构：
在Hive中，我们可以通过函数来实现各种复杂的行列转换。
2 行转列：多行转多列
2.1 需求
➢ 原始数据表
➢ 目标结果表
4.2.2 case when判断
➢ 功能
用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于Java中的switch case 功能
➢ 语法
➢ 语法一
CASE
WHEN 条件1 THEN VALUE1
WHEN 条件2 THEN VALUE2
……
WHEN 条件N THEN VALUEN
ELSE 默认值
END➢ 语法二
CASE 列
WHEN V1 THEN VALUE1
WHEN V2 THEN VALUE2
……
WHEN VN THEN VALUEN
ELSE 默认值
END➢ 测试
➢ 语法一：当id < 2显示a，当id = 2 显示b ,其他的显示c
select
id,
case
when id < 2 then 'a'
when id = 2 then 'b'
else 'c'
end as caseName
from tb_url;
语法二：当id =1 显示a，当id = 2 显示b ,其他的显示c
select
id,
case id
when 1 then 'a'
when 2 then 'b'
else 'c'
end as caseName
from tb_url;
2.3 实现
➢ 创建原始数据表，加载数据
--切换数据库
use db_function;
--建表
create table row2col1(
col1 string,
col2 string,
col3 int
) row format delimited fields terminated by '\t';
--加载数据到表中
load data local inpath '/export/data/r2c1.txt' into table row2col1;
➢ SQL实现转换
select
col1 as col1,
max(case col2 when 'c' then col3 else 0 end) as c,
max(case col2 when 'd' then col3 else 0 end) as d,
max(case col2 when 'e' then col3 else 0 end) as e
from
row2col1
group by
col1;
3 行转列：多行转单列
3.1 需求
➢ 原始数据表
➢ 目标数据表
3.2 concat
➢ 功能：用于实现字符串拼接，不可指定分隔符
➢ 语法
concat(element1,element2,element3……)
➢ 测试
select concat(“it”,“cast”,“And”,“heima”);
±----------------+
| itcastAndheima |
±----------------+
➢ 特点：如果任意一个元素为null，结果就为null
select concat(“it”,“cast”,“And”,null);
±------+
| NULL |
±------+
3.3 concat_ws
➢ 功能：用于实现字符串拼接，可以指定分隔符
➢ 语法
concat_ws(SplitChar，element1，element2……)
➢ 测试
select concat_ws("-",“itcast”,“And”,“heima”);
±------------------+
| itcast-And-heima |
±------------------+
➢ 特点：任意一个元素不为null，结果就不为null
select concat_ws("-",“itcast”,“And”,null);
±------------+
| itcast-And |
±------------+
3.4 collect_list
➢ 功能：用于将一列中的多行合并为一行，不进行去重
➢ 语法
collect_list（colName）
➢ 测试
select collect_list(col1) from row2col1;
±---------------------------+
| [“a”,“a”,“a”,“b”,“b”,“b”] |
±---------------------------+
3.5 concat_set
➢ 功能：用于将一列中的多行合并为一行，并进行去重
➢ 语法
collect_set（colName）
➢ 测试
select collect_set(col1) from row2col1;
±-----------+
| [“b”,“a”] |
±-----------+
3.6 实现
➢ 创建原始数据表，加载数据
--切换数据库
use db_function;
--建表
create table row2col2(
col1 string,
col2 string,
col3 int
)row format delimited fields terminated by '\t';
--加载数据到表中
load data local inpath '/export/data/r2c2.txt' into table row2col2;➢ SQL实现转换
select
col1,
col2,
concat_ws(',', collect_list(cast(col3 as string))) as col3
from
row2col2
group by
col1, col2;
4 列转行：多列转多行
4.1 需求
⚫ 原始数据表
⚫ 目标结果表
4.2 union
➢ 功能：将多个select语句结果合并为一个，且结果去重且排序
➢ 语法
select_statement
UNION [DISTINCT]
select_statement
UNION [DISTINCT]
select_statement …
➢ 测试
select 'b','a','c'
union
select 'a','b','c'
union
select 'a','b','c';
4.3 union all
➢ 功能：将多个select语句结果合并为一个，且结果不去重不排序
➢ 语法
select_statement UNION ALL select_statement UNION ALL select_statement …
➢ 测试
select ‘b’,‘a’,‘c’
union all
select ‘a’,‘b’,‘c’
union all
select ‘a’,‘b’,‘c’;
4.4 实现
➢ 创建原始数据表，加载数据
–切换数据库
use db_function;
–创建表
create table col2row1
(
col1 string,
col2 int,
col3 int,
col4 int
) row format delimited fields terminated by ‘\t’;
–加载数据
load data local inpath ‘/export/data/c2r1.txt’ into table col2row1;
➢ SQL实现转换
select col1, ‘c’ as col2, col2 as col3 from col2row1
UNION ALL
select col1, ‘d’ as col2, col3 as col3 from col2row1
UNION ALL
select col1, ‘e’ as col2, col4 as col3 from col2row1;
5 列转行：单列转多行
5.1 需求
➢ 原始数据表
➢ 目标结果表
5.2 explode
➢ 功能：用于将一个集合或者数组中的每个元素展开，将每个元素变成一行
➢ 语法
explode( Map | Array)
➢ 测试
select explode(split(“a,b,c,d”,","));
5.3 实现
➢ 创建原始数据表，加载数据
--切换数据库
use db_function;
--创建表
create table col2row2(
col1 string,
col2 string,
col3 string
)row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/export/data/c2r2.txt' into table col2row2;➢ SQL实现转换
select
col1,
col2,
lv.col3 as col3
from
col2row2
lateral view
explode(split(col3, ',')) lv as col3;
