Hive SQL实现行转列跟列转行

在SQL实际操作中，往往会用到行列转换的问题，如：

        将同一类的数据整合放在一个数据集中，即行转列。

或：将一个数据集中的数据扩展分成多行，即列转行。

行转列

以下介绍如何实现SQL中的行列转换问题，首先进行数据准备：

-- 数据准备
--建表
create table row2col2(
                         col1 string,
                         col2 string,
                         col3 int
)；
--插入数据
insert into row2col2 values ('a','b',1),
('a','b',2),
('a','b',3),
('c','d',4),
('c','d',5),
('c','d',6);

需求1：把原表数据变成以下格式

a b [1,2,3]
c d [4,5,6]

知识点：

              collect_set(字段名): 把多个数据收集到一起,默认去重
              collect_list(字段名): 把多个数据收集到一起,默认不去重

代码实现：

select
    col1,
    col2,
    collect_list(col3)
from
    row2col2
group by
    col1, col2;

需求2: 把原表数据变成以下格式

a b '1-2-3'
c d '4-5-6'

知识点：

              concat_ws(分隔符,多个数据...)：把多个子串用指定分隔符拼接成一个大字符串

              注意: 如果拼接数据不是字符串可以使用cast转换

代码实现：

select
    col1,
    col2,
    concat_ws('-',collect_list(cast(col3 as string)))
from
    row2col2
group by
    col1, col2;

列转行

以下介绍如何实现SQL中的行列转换问题，首先进行数据准备：

-- 列转行
--创建表
create table col2row2(
                         col1 string,
                         col2 string,
                         col3 string
)；
--插入数据
insert into col2row2 values 
('a','b','1,2,3'),
('c','d','4,5,6');

知识点：

              split(字符串,分隔符)：把字符串按照指定分隔符切割

              explode(要炸开的字段)：炸裂函数

解决思路：

1、首先将单列数据先切割再使用炸裂函数炸开；

2、同时配合侧视图生成最后完整表。

  侧视图：

          原理：将UDTF（表生成函数）的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表

          格式：lateral view
                    select …… from 表名 lateral view UDTF(xxx) 侧视图名 as 字段名;

代码实现：

-- 单列数据先切割再炸开
select split(col3,',') from col2row2;
select explode(split(col3,',')) from col2row2;

-- 再去完成需求,列转行生成最后完整表
select col1,col2,tmp.col3
from col2row2
 lateral view explode(split(col3,',')) tmp as col3;

以上就是hive SQL中行列转换的具体解决方案了。