hive多行转多列

查看：https://blog.csdn.net/cwfreebird/article/details/91355730

方案一： sum case when

select brand,  	max(case when area_name='东北' then total_price_actual else 0 end) db_price, 	max(case when area_name='华北' then total_price_actual else 0 end) hb_price,     max(case when area_name='华东' then total_price_actual else 0 end) hd_price, 	max(case when area_name='华南' then total_price_actual else 0 end) hn_price, 	max(case when area_name='西南' then total_price_actual else 0 end) xn_price, 	max(case when area_name='西北' then total_price_actual else 0 end) xb_price, 	max(case when area_name='东北' then qty else 0 end) db_qty, 	max(case when area_name='华北' then qty else 0 end) hb_qty,     max(case when area_name='华东' then qty else 0 end) hd_qty, 	max(case when area_name='华南' then qty else 0 end) hn_qty, 	max(case when area_name='西南' then qty else 0 end) xn_qty, 	max(case when area_name='西北' then qty else 0 end) xb_qty from ( 	select brand, area_name, sum(total_price_actual) total_price_actual, sum(qty) qty 	from hive_temp_vipvop.vop_xstore_retail  	group by brand, area_name ) tmp  group by brand  

方案二：

select brand,  	kv1['华北'] hb_price, kv2['华北'] hb_qty,  	kv1['华东'] hd_price, kv2['华东'] hd_qty,  	kv1['东北'] db_price, kv2['东北'] db_qty,  	kv1['华南'] hn_price, kv2['华南'] hn_qty,  	kv1['西南'] sn_price, kv2['西南'] xn_qty, 	kv1['西北'] xb_price, kv2['西北'] xb_qty  from ( 	select brand, str_to_map(concat_ws(',', collect_set(concat(area_name, '-', total_price_actual))),',','-') kv1, 	 str_to_map(concat_ws(',', collect_set(concat(area_name, '-', qty))),',','-') kv2   	from ( 		select brand, area_name, sum(total_price_actual) total_price_actual, sum(qty) qty 		from hive_temp_vipvop.vop_xstore_retail  		group by brand, area_name 	) tmp    group by brand  ) t  

CONCAT 函数

CONCAT() 函数可将两个或多个字符串连接成一个字符串，

语法如下： CONCAT ( input_string1, input_string2 [, input_stringN ] );

collect_set 函数

collect_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

concat_ws 函数

concat_ws()函数用于在拼接的时候指定分隔符，CONCAT_WS(separator,str1,str2,…)

str_to_map 函数

str_to_map(text[, delimiter1, delimiter2]). 使用两个分隔符将文本拆分为键值对。Delimiter1将文本分成K-V对，Delimiter2分割每个K-V对。