快捷導(dǎo)航

SQL實(shí)戰(zhàn)之行列互轉(zhuǎn)

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

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

一. 行轉(zhuǎn)列

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

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

1.1 CASE WHEN/IF

最常見的就是 CASE WHEN 了，不過為了代碼簡(jiǎn)潔我們使用 IF 函數(shù)，代碼如下：

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

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

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

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

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 總結(jié)

以上就是3種行轉(zhuǎn)列的方法，還有一種是生成 struct 結(jié)構(gòu)的方式，在次我就不贅述了，實(shí)用性當(dāng)然是第1種方便了，其他2種可以適當(dāng)裝個(gè)13。

二. 行轉(zhuǎn)列

數(shù)據(jù)如下：

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, '數(shù)學(xué)' 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

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

select t1.name, subject, grade
from (    
select name        
, str_to_map(concat('語文', ':', chinese, ';', '數(shù)學(xué)', ':', 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;