SQL實戰(zhàn)之行列互轉

更新時間：2024年12月11日 08:37:25 作者：Luckyforever%-

本文介紹了在Hive中進行行轉列的幾種方法,包括使用CASE?WHEN/IF、Get_Json_Object、Str_To_Map以及UNION?ALL和EXPLODE函數,每種方法都有其適用場景,感興趣的可以了解一下

一. 行轉列

Hive中某表存放用戶不同科目考試成績，多行存放，看起來不美觀，想要在一行中展示用戶所有科目成績，數據如下：

有多種方式，我將一一列舉：

1.1 CASE WHEN/IF

最常見的就是 CASE WHEN 了，不過為了代碼簡潔我們使用 IF 函數，代碼如下：

select uid
	, max(if(subject = 'chn', score, null)) as chn
	, max(if(subject = 'eng', score, null)) as eng
	, max(if(subject = 'math', score, null)) as math
from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
group by uid;

1.2 Get_Json_Object

可以將用戶的所有成績先聚合成一個大Json字符串，然后使用 get_json_onject 獲取Json中相應字段即可，代碼如下：

select t1.uid
      , get_json_object(t1.st, '$.chn') as chn
	  , get_json_object(t1.st, '$.eng') as eng
	  , get_json_object(t1.st, '$.math') as math
from (
	select uid
		, concat('{', concat_ws(',', collect_set(concat('"', subject, '"', ':', '"', score, '"'))), '}') as st
	from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
	group by uid
) t1;

1.3 Str_To_Map

還可以將用戶的成績生成一個 map，通過 map['field'] 的方式獲取字段數值，代碼如下：

select t1.uid
, t1.st['chn'] as chn
, t1.st['eng'] as eng
, t1.st['math'] as math
from (
	select uid
		, str_to_map(concat_ws(';', collect_set(concat_ws(':', subject, score))), ';', ':') as st
	from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
	group by uid
) t1;

1.4 總結

以上就是3種行轉列的方法，還有一種是生成 struct 結構的方式，在次我就不贅述了，實用性當然是第1種方便了，其他2種可以適當裝個13。

二. 行轉列

數據如下：

2.1 UNION ALL

union all 是常用方法，代碼如下：

select name, '語文' as subject, chinese as grade
from values ('張三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic)
union all
select name, '數學' as subject, math as grade
from values ('張三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic)
union all
select name, '物理' as subject, pyhsic as grade
from values ('張三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic);

2.2 EXPLODE

先將數據生成 map ，然后再用 explode 函數炸開它，代碼如下：

select t1.name, subject, grade
from (    
select name        
, str_to_map(concat('語文', ':', chinese, ';', '數學', ':', math, ';', '物理', ':', pyhsic), ';', ':') as lit    
from values ('張三', 74, 83, 93), ('李四', 74, 84, 94) 
as t (name, chinese, math, pyhsic)) t1    
lateral view explode(t1.lit) tmp as subject, grade;