行转列与列转行

前言

今天同学在公司业务中遇到了行转列和列转行的问题，为了防止忘记，还是写一篇博客记录一下吧。

行转列
1.1 相关函数介绍
1.2 行转列案例
1.3 CONCAT与CONCAT_WS的区别
列转行
2.1 相关函数介绍
2.2 列转行案例
总结

行转列

1.1 相关函数介绍

CONCAT_WS(separator,str1,str2,…):
是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
COLLECT_SET(col):
函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。

1.2 行转列案例

如下图，是我们class表的源数据，一个人对应多门科目，现在我们需要按个人分组，并展示这个人的所有学科。

接下来我们需要使用行转列函数进行操作：

select name,concat_ws(',',collect_Set(classes)) from classes group by name;
#因为CONCAT_WS中只能存array类型的，所以需要先把classes字段使用collect_set转为array类型。

效果如下：

1.3 CONCAT与CONCAT_WS的区别

1.CONCAT
CONCAT()函数可以连接一个或者多个字符串。
CONCAT只接受原始参数! ! 例如上文的行转列就不可以使用CONCAT。
CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
2.CONCAT_WS
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

列转行

相关函数介绍

EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

列转行案例

如下图，是我们class表的源数据，按个人分组，显示个人科目，现在我们需要按每行展示个人的一门学科。  接下来我们需要使用列转行函数进行操作：

select name,class from classes lateral view explode(split(classes,',')) num as class;

#因为EXPLODE只能将array或者map结构的函数拆分，而我们classes字段在创建时设置的是string格式，所以需要结合split函数将其转换为array类型。

效果如下：

总结

此次复习行转列和列转行也巩固了对之前知识的理解，以后有时间会更新之前才面试中遇到的SQL题。