1.1.2 实现原理

1.1.3 实现

1.2 多行转单列

1.1.1 需求

0 工作场景

实际工作场景中经常需要实现对于Hive中的表进行行列转换操作，例如当前ADS层的数据表，我们统计得到每个小时不同维度下的UV、PV、IP的个数，而现在为了构建可视化报表，得到每个小时的UV、PV的线图，观察访问趋势，我们需要构建如下的表结构：

趋势图如下：

1 案例实现

1.1 多行转多列

1.1.1 需求

原始数据表

目标结果表

1.1.2 实现原理

多行转多列主要利用case when 语句进行实现。

case when判断
功能

用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于Java中的switch case 功能

语法

（1）语法一

CASE
WHEN条件1THENVALUE1
WHEN条件2THENVALUE2
……
WHEN条件NTHENVALUEN
ELSE默认值
END

（2）语法二

CASE列
WHENV1THENVALUE1
WHENV2THENVALUE2
……
WHENVNTHENVALUEN
ELSE默认值
END

（3）测试

语法一：当id < 2显示a，当id = 2 显示b ,其他的显示c

select
id,
case
whenid <2then'a'
whenid =2then'b'
else'c'
end ascaseName
fromtb_url;

语法二：当id =1 显示a，当id = 2 显示b ,其他的显示c

select
id,
caseid
when1then'a'
when2then'b'
else'c'
end ascaseName
fromtb_url;

1.1.3 实现

创建原始数据表，加载数据

--切换数据库

usedb_function;

--建表
create tablerow2col1(
  col1string,
  col2string,
  col3int
)row formatdelimitedfields terminated by'\t';

--加载数据到表中
load data localinpath'/export/data/r2c1.txt'into tablerow2col1;

SQL实现转换

select
col1ascol1,
max(casecol2when'c'thencol3else0end)asc,
max(casecol2when'd'thencol3else0end)asd,
max(casecol2when'e'thencol3else0end)ase
from
row2col1
group by
col1;

1.2 多行转单列

1.1.1 需求

原始数据表

目标数据表

1.2.2实现原理

利用Hive中的concat或caoncat_ws与collect_list或collect_set进行实现

concat

功能：用于实现字符串拼接，不可指定分隔符
语法

concat(element1,element2,element3……)

3.测试

selectconcat("it","cast","And","heima");

+-----------------+

| itcastAndheima |

+-----------------+

4 特点：如果任意一个元素为null，结果就为null

selectconcat("it","cast","And",null);

+-------+

| NULL |

+-------+

concat_ws

功能：用于实现字符串拼接，可以指定分隔符
语法

concat_ws(SplitChar，element1，element2……)

3.测试

selectconcat_ws("-","itcast","And","heima");

+-------------------+

| itcast-And-heima |

+-------------------+

4 特点：任意一个元素不为null，结果就不为null

selectconcat_ws("-","itcast","And",null);

+-------------+

| itcast-And |

+-------------+

collect_list

功能：用于将一列中的多行合并为一行，不进行去重
语法

collect_list（colName）

3.测试

selectcollect_list(col1)fromrow2col1;

+----------------------------+

| ["a","a","a","b","b","b"] |

+----------------------------+

4. 特点

collect_list不会去重，会忽略null值

collect_set

功能：用于将一列中的多行合并为一行，并进行去重
语法

collect_set（colName）

3.测试

selectcollect_set(col1)fromrow2col1;

+------------+

| ["b","a"] |

+------------+

4 .特点

会去重，会忽略NULL值

1.1.3 实现

创建原始数据表，加载数据

--切换数据库
usedb_function;

--建表
create tablerow2col2(
  col1string,
  col2string,
  col3int
)row formatdelimitedfields terminated by'\t';

--加载数据到表中
load data localinpath'/export/data/r2c2.txt'into tablerow2col2;

2.SQL实现转换

select
col1,
col2,
concat_ws(',', collect_list(cast(col3as string)))ascol3
from
row2col2
group by
col1, col2;

1.3 多列转多行

1.3.1 需求

原始数据表

目标结果表

1.3.2 实现原理

union

功能：将多个select语句结果合并为一个，且结果去重且排序
语法

select_statement

UNION[DISTINCT]

select_statement

UNION[DISTINCT]

select_statement ...

3.测试

select'b','a','c'

union

select'a','b','c'

union

select'a','b','c';

4 特点

对数据进行去重,重复的数据不进行合并，相当于union distinct ,效率较低

union all

功能：将多个select语句结果合并为一个，且结果不去重不排序
语法

select_statementUNION ALLselect_statementUNION ALLselect_statement ...

3.测试

select'b','a','c'

union all

select'a','b','c'

union all

select'a','b','c';

4. 特点

对数据不进行去重，效率要高

1.3.3 实现

创建原始数据表，加载数据

--切换数据库
usedb_function;

--创建表
create tablecol2row1
(
col1string,
col2int,
col3int,
col4int
)row formatdelimitedfields terminated by'\t';

--加载数据
load data localinpath'/export/data/c2r1.txt' into tablecol2row1;

SQL实现转换

selectcol1,'c'ascol2,col2ascol3fromcol2row1
UNION ALL
selectcol1,'d'ascol2,col3ascol3fromcol2row1
UNION ALL
selectcol1,'e'ascol2,col4ascol3fromcol2row1;

1.4单列转多行

1.4.1 需求

原始数据表

目标结果表

1.4.2 实现原理

利用UDTF函数lateral view + explode函数

explode

功能：用于将一个集合或者数组中的每个元素展开，将每个元素变成一行（类似于flatmap()函数）
语法

explode( Map | Array)

3.测试

selectexplode(split("a,b,c,d",","));

4.特点

UDTF函数，将一变多，类似于spark算子flatmap()，一般结合split()函数使用

1.4.3 实现

1.创建原始数据表，加载数据

--切换数据库
usedb_function;

--创建表
create tablecol2row2(
  col1string,
  col2string,
  col3string
)row formatdelimitedfields terminated by'\t';

--加载数据
load data localinpath'/export/data/c2r2.txt'into tablecol2row2;

2.SQL实现转换

select
col1,
col2,
lv.col3ascol3
from
col2row2
lateralview
explode(split(col3,',')) lvascol3;

2 小结

行列转换是数据开发者的基本技能，也是面试重点，在数据开发中处处可见，本文对常见的几种情况进行了总结，包括了多行转多列，多行转单列，多列转多行，单列转多行的几种情况，并给出了解决思路和方法，读者可根据本文给出的思路灵活应对业务中遇到的问题。

原文链接：https://blog.csdn.net/godlovedaniel/article/details/117397614