hive sql的行列转换

一行转多行

-- 测试数据
create table test.test_1row_to_nrow(
	col1 string,
    col2 string,
    col3 string
)
;
insert into test.test_1row_to_nrow values
(a,b,'1,2,3'),
(c,d,'4,5,6')
;
-- lateral view explode(split())
select col1, col2, col3_new 
from test.test_1row_to_nrow a 
lateral view explode(split(col3,',')) b AS col3_new
;

多行转一行

-- 测试数据
create table test.test_nrow_to_1row(
	col1 string,
    col2 string,
    col3 string
)
;
insert into test.test_nrow_to_1row values
(a,b,1),
(a,b,2),
(a,b,3),
(c,d,4),
(c,d,5),
(c,d,6)
;
-- 需要的话 还可以在 collect_set 里面用上 concat
select col1,col2,concat_ws(',',collect_set(col3)) as bian
from test.test_nrow_to_1row  
group by col1,col2
;

多行转多列

• 方法一

  -- max(case when) 的方式
  -- 测试数据
  create table test.test_nrow_to_ncol(
  	brand string,
      city string,
      amt double,
      qty double
  )
  ;
  insert into test.test_nrow_to_ncol values
  ('T','东北',199999.08,3000),
  ('T','东南',199999.08,3000),
  ('T','西北',199999.08,3000),
  ('T','西南',199999.08,3000)
  ;
  select brand, 
  	max(case when ciry='东北' then amt else 0 end) db_amt,
  	max(case when ciry='东南' then amt else 0 end) dn_amt,
      max(case when ciry='西北' then amt else 0 end) xb_amt,
  	max(case when ciry='西南' then amt else 0 end) xn_amt,
  	max(case when ciry='东北' then qty else 0 end) db_qty,
  	max(case when ciry='东南' then qty else 0 end) dn_qty,
      max(case when ciry='西北' then qty else 0 end) xb_qty,
  	max(case when ciry='西南' then qty else 0 end) xn_qty
  from (
  	select brand, city, sum(amt) amt, sum(qty) qty
  	from test.test_nrow_to_ncol
  	group by brand, city
  ) tmp 
  group by brand
  ;
  

• 方法二

  -- str_to_map 函数
  select brand, 
  	kv1['东北'] db_price, kv2['东北'] db_qty, 
  	kv1['东南'] dn_price, kv2['东南'] dn_qty,
  	kv1['西北'] xb_price, kv2['西北'] xb_qty,
  	kv1['西南'] xn_price, kv2['西南'] xn_qty
   from (
  	select brand
  		,str_to_map(concat_ws(',', collect_set(concat(area_name, '-', amt))),',','-') kv1
  		,str_to_map(concat_ws(',', collect_set(concat(area_name, '-', qty))),',','-') kv2
    	from (
  		select brand, city, sum(amt) amt, sum(qty) qty
  		from test.test_nrow_to_ncol
  		group by brand, city
  	) tmp
     group by brand
   ) t
  ;
  

多列转多行

• 方法一

  -- union all的方式
  -- 测试数据
  create table test.test_ncol_to_nrow(
  	the_date string,
      uv int,
      new_uv int,
      vv
  )
  ;
  insert into test.test_ncol_to_nrow values
  ('2022-02-01',5000,200,60000),
  ('2022-02-02',5200,180,66600)
  ;
  select the_date, 'uv' as type, uv as value 
  from test.test_ncol_to_nrow
  UNION ALL
  select the_date, '新增uv' as type, new_uv as value 
  from test.test_ncol_to_nrow
  UNION ALL
  select the_date, '播放量' as type, vv as value 
  from test.test_ncol_to_nrow
  ;
  

• 方法二

  -- lateral view exploed(map()) 的方式
  select a.the_date
        ,b.type
        ,b.value
  from(
  	select *
  	from test.test_ncol_to_nrow
  ) a 
  lateral view explode(
  	map('uv',uv,'新增uv',new_uv,'播放量',vv)
  ) b as type, value
  ;
  

一列转多列

-- split[] 的方式 或者 map[]

多列转一列

-- concat() 函数