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对。