1.什么是行转列&列转行问题？

首先，行转列&列转行问题其实是两个问题：行转列问题和列转行问题，并且这两种问题的解决思路也是不一样的。 常见的行转列问题大概有两种形式，如下：

 形式一

形式二

对于行转列问题，其实是对多行进行聚合，所以一般会涉及到聚合操作，也就是会使用group by，然后根据题目要求来选择聚合函数。 常见的列转行问题也有两种形式，如下：

形式一

形式二

tips

• 对于列转行问题，其实就是行转列的反向操作，一般需要针对某列的结果进行”炸开“，所以需要掌握常见的炸裂函数的使用。
• 对于形式一，其实就是将“分数”这一列拆成多行，属于一到多。
• 对于形式二，其实就是将“分数”和“学生学号”这两列拆成多行，属于多到多。
• PS：无论是形式一还是形式二，都需要掌握炸裂函数的使用！

2.炸裂函数介绍

UDTF函数介绍：UDTF函数，全称为User-Defined Table-Generating Functions，即用户定义的表生成函数。它是一种由用户自定义的函数，用于从原始表中的一行数据生成多行数据。 UDTF其实是一类函数，但是我们平时用的最多的还是explode()这个函数，因此也称炸裂函数，所以把explode()这个函数作为UDTF的代名词。下面给出一些UDTF的使用案例：

-- explode(array()):返回一列，命名为item
select explode(array("a", "b", "c")) as item;

-- explode(map()):返回两列，分别命名为key和value
select explode(map("a", 1, "b", 2, "c", 3)) as (key, value);
 
-- posexplode(array()):不仅会返回数组元素，还会返回其索引(0开始)
select posexplode(array("a", "b", "c")) as (pos, item);

通常情况下，我们并不是单纯地去使用UDTF，而是去和Lateral View去配合使用。

Lateral View可以将UDTF应用到源表的每行数据，将每行数据转换为一行或多行，并将源表中每行的输出结果与该行连接起来，形成一个虚表。下面给出一个示例：

select
    id,
    name,
    hobbies,
    hobby
from person lateral view explode(hobbies) tmp as hobby;
-- hobbies可以是一个[数组数据类型]
-- tmp其实就是使用explode(hobbies)炸出来的虚拟表的表名，然后hobby是给这个虚拟表的列进行的命名

-- 如果最终会炸出来两列，则最后的from应该这样写：
from person lateral view explode(hobbies) tmp as hobby, new;

-- 在分析这个代码的时候，我们应该将下面一行看成是一个整体，要从这个整体中去select内容
-- tmp可以看成是一种固定格式，写代码的时候最好不要删掉
from person lateral view explode(hobbies) tmp as hobby;

-- 其实我们可以将lateral view类似的比作是LEFT JOIN这个样子，怎么理解呢？
-- 就是让当前的表person去和炸出来的表做一个连接，连接的字段和关系其实就是explode里面的字段，然后这个炸出来的表我们记为tmp，然后这个表的列名我们也可以去指定，最终从这个连接完之后的表中去select数据即可！

3.行转列问题的解决思路&例题

思路总结

对于行转列问题，其实是对多行进行聚合，所以一般会涉及到聚合操作，也就是会使用group by，然后根据题目要求来选择聚合函数。

聚合方式一般有两种：

• 常规聚合函数(例如max()等等) + case when end 条件判断语句 
  数组聚合函数，例如collect_list()或者collect_set()，一般需要搭配concat_ws()一起使用 

行转列问题的解决思路，根据问题的不同，可以有以下三种： 

1. group by + max()聚合函数 + case when end判断语句 
2. group by + collect_list()/collect_set()聚合函数 + concat_ws()拼接函数 
3. 有些情况下，没有可以用于聚合的字段，可能需要使用【UNION ALL】直接拼接最终的结果，或者需要我们先用case when或者其他方法(“人工造出来一列用于聚合！“)。 

下面给出三个例子(例题中的数据需要自己写with语句构造)，来说明三种思路的具体操作。

例题1

题目要求：将源表数据转成目标数据(把星座和血型一样的人归类到一起，中间用"|"来分割)

解题思路： 

既然是将星座和血型一样的人归类到一起，首先先按照星座和血型进行分组(group by)，然后对同一组的人进行聚合操作。 

本题要求在相同分组的人之间插入"|"，显然就是得用到concat_ws()字符串函数！

select
    constellation_name, blood_type,
    concat_ws('|', collect_set(name)) as name
from constellation
group by constellation_name, blood_type
-- COLLECT_SET(name) 会将每个用户的姓名收集到一个唯一值的集合中，然后 CONCAT_WS函数将这个集合中的值用"|"连接起来，形成一个以|分隔的字符串。

补充：关于字符串函数concat()、concat_ws、collect_set

• concat 函数用于连接两个或多个字符串； 
• concat_ws 函数是带有分隔符的字符串连接函数，用于连接多个字符串并在它们之间插入指定的分隔符； 
• concat 和 concat_ws 不是聚合函数，而是普通的字符串函数。它们用于处理单个字符串或多个字符串，并返回字符串结果。虽然它们可以用在查询中，但它们并不执行行级别的聚合操作，而是直接对给定的字符串参数执行操作； 
• 虽然concat_ws不是聚合函数，不能够对组内的值直接进行聚合，但是可以先使用collect_set对组内的值进行预处理(聚合)，再将结果作为参数传递给concat_ws。

例题2

题目要求：将Products表的数据转成Result表的数据。

解题思路： 直接按照product_id进行聚合即可~

select
    product_id
    ,sum(case when store = 'store1' then price else null end) as store1
    ,sum(case when store = 'store2' then price else null end) as store2
    ,sum(case when store = 'store3' then price else null end) as store3
from Products
group by product_id
 
-- 注意点：因为原始数据本身就是数值，因此可以使用SUM()这种聚合函数来对数值型数据进行操作的。
-- 而且在不同的产品id下面，对应的store的取值是不同的，所以这里的SUM不会真的进行求和，只是使用聚合函数作为桥梁，能够正常将组内的结果统计出来。

例题3

题目要求：将源表数据转成目标数据(输出每个大洲的姓名，姓名按照字典顺序排序)。

解题思路：

本题的需求和例题2是类似的，都是类似于转置的问题。只不过本题的题目与例题2有些不同：原表格没有基准id可以直接用，需要自己构造(使用窗口函数，自己构造row_number())。 

row_number() + group by+max(if)，显然本题仍然是需要对原表格进行聚合，然后使用聚合函数 + case when来通过不同的取值得到不同的结果，创建新行。 

问题是根据什么进行聚合呢？我们理想的结果是将Jack、Xi和Pascal分为一组然后使用sum + case when进行聚合处理。这就需要使用row_number()来生成一列基准id，然后基于基准id按照上述思路进行分组聚合即可。

with a as (
select
row_number() over (partition by continent order by name) as id
,name
,continent
from student
)

select
max(case continent when 'America'then name else null end) as America
,max(case continent when 'Europe'then name else null end) as Europe
,max(case continent when 'Asia'then name else null end) as Asia
from a
group by id

4.列转行问题的解决思路&例题

思路总结 

• 首先，要想解决列转行问题，必须掌握炸裂函数，这一块在前面第二节已经系统介绍过了。
• 其次，列转行问题通常要用到split函数()！下面对其进行介绍。 
• 所以：列转行问题 = split() + 炸裂函数 的使用 。

关于split函数：

1. 在 Hive 中，SPLIT() 函数用于拆分字符串，并且其输出类型是一个数组(ARRAY)。 
2. SPLIT() 函数接受两个参数，第一个参数是要拆分的字符串，第二个参数是用于指定分隔符的正则表达式 pattern 。 

1. pattern 是用于指定分隔符的字符串，它可以是一个简单的字符，也可以是一个复杂的正则表达式，这取决于你想要如何对输入的字符串 str 进行分割。

例题1

题目要求：将下图的左边内容转变为右边内容

解题思路：使用split，先将类别按照逗号分隔符进行拆分，生成数组，再将生成的数组作为参数传给explode()进行数据炸裂操作。

select
    title
    ,category_new
from movie lateral view explode(split(category, ',')) tmp as category_new