【Hive---20】实际案例之行列转换 『 多行转多列 | 多行转单列 | 多列转多行 | 单列转多行』

行列转换只要弄清是什么样的数据可以进行行列转换就会变的很简单。

1. 多行转多列

1.1 数据特征

情况一：若给出文字，文字描述出现“…的…”

• 若给出数据，满足以下2个条件，则可以进行多行转多列：
  1. 某字段的值等份的相等，则根据该字段分组；或者多个字段联合的值等份的相等，则根据这些字段联合分组
  2. 存在每一组的某列对应相等。
     
• 含义：“…的…是…”。比如：“a的c是1”、“b的c是1”

1.2 代码实现

(1) 方式一：max(case…end) | max(if())

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 tb
group by col1;

-- 或者用 if() 代替 case...end
select 
	col1 as col1,
	max(if(col2="c"), col3, 0) as c,
	max(if(col2="d"), col3, 0) as d,
	max(if(col2="e"), col3, 0) as e
from tb
group by col1;

注意：

(2) 方式二：lateral view 侧视图

https://www.cnblogs.com/30go/p/8328869.html

1.3 变形例题

select
	DDate,
	count(if(shengfu="胜", 1, null)) as "胜",
	count(if(shengfu="负", 1, null)) as "负",
from tb
group by DDate;

1.4 变形例题

• 分析：
  1. 出现...的...字眼，故需先进行行列转换。
  2. score表中有学生id、课程id、课程分数，由此不需要join就可以完成行列转换
  3. 关于效率的思考
     
• 代码：

  -- 1. 先进多行转多列，得到：...的...形式。这里得到学生的成绩
  with tmp as (
  	select 
  		student_id,
  		max(if(cname=1, sc.score, 0)) as bio,
  		max(if(cname=2, sc.score, 0)) as pe,
  	from score
  	group by student_id;
  	having  pe < bio
  )
  select
  	sid,
  	sname
  from tmp
  left join student on student_id = sid
  where bio < pe;
  

2. 多行转单列

2.1 数据特征

满足以下1个条件，则可以进行多行转单列：

1. 某字段的值等份或不等份的相等，则根据该字段分组；或者多个字段联合的值等份或不等份的相等。然后就可以将其余每个字段的值合并
   

2.2 代码实现(cast()、collect_list()、concat_ws())

select 
	col1 as col1,
	col2 as col2,
	concat_ws(",", collect_list(cast(col3 as string))) as col3
from tb
group by col1, col2;

3. 多列转多行

3.1 数据特征

多列转多行就是多行转多列的逆过程：

3.2 代码实现(union all)

一个select转一列，然后将多个select用union all 拼接起来：

select col1, "c" as col2, col2 as col3 from tb;
union all
select col1, "d" as col2, col3 as col3 from tb;
union all
select col1, "c" as col2, col4 as col3 from tb;	 	

4. 单列转多行

3.1 数据特征

单列转多行就是多行转单列的逆过程：

3.2 代码实现(explode()、侧视图)

select
	col1,
	col2,
	tb2.col3 as col3
from tb lateral view explode(split(col3, ",")) tb2 as col3