HIVE行转列和列转行

行转列

select dt_month,valid_num,unvalid_num from test.test_xw_rowtocol

数据如下

转换要求:行转列需保留列名,如下图

方式一:采用union all的形式

select  
  dt_month
 ,'valid_num'      as type
​ ,sum(valid_num)   as num 
​from  temp.temp_xw_rowtocol
​group by dt_month​
 union all
select  
  dt_month
 ,'unvalid_num'      as type
​ ,sum(unvalid_num)   as num 
​from  temp.temp_xw_rowtocol
​group by dt_month​
方式二:使用lateral view和str_to_map
select
  a.dt_month
 ,add_t.type
 ,add_t.num
from temp.temp_xw_rowtocol a
lateral view explode(str_to_map(concat('valid_num=',valid_num
                                      ,'&unvalid_num=',unvalid_num
                                      ),'&','='
                                )
                    ) add_t as type,num
方式三:思路和方式二一致,稍微简单点,缺点只能含explode函数(列名称默认key和value也可自定义)

select 
   explode(str_to_map(concat('valid_num=',valid_num
                             ,'&unvalid_num=',unvalid_num
                            ),'&','='
                       )
           )                
from temp.temp_xw_rowtocol a
;
ps:方式一在指标少时方便使用,当行转列的指标比较多时代码量会比较大,维护困难

      方式二极大的减少重复的代码量,需要熟悉lateral view和str_to_map,concat等函数使用,concat主要拼接key-value形式存储的字符串,如在一个项目开发中使用一个sql处理出十多个指标,后期需要列存储各指标值时,使用方式二就可以使代码减少数10倍,极大提升代码可读性和可维护性,同时key值可以自定义。

列转行

通过group by

select
  a.dt_month
 ,sum(case when type = 'valid_num' then num end)    as valid_num
 ,sum(case when type = 'unvalid_num' then num end)  as unvalid_num
from temp.temp_xw_coltorow a

=======================

select t2.name,
       t3.subject,
       t3.score
  from (
        select name,
               max(case when subject = 'a' then score else 0 end) a,
               max(case when subject = 'b' then score else 0 end) b
          from (
                select 'zhangsan' name,
                       'a' subject,
                       100 score
             union all select 'zhangsan' name,
                       'b' subject,
                       90 score
             union all select 'lisi' name,
                       'a' subject,
                       95 score
             union all select 'lisi' name,
                       'b' subject,
                       85 score
               )t1
         group by name
       )t2 lateral view  explode(str_to_map(concat('a=',a,'&b=',b),'&','=')) t3 as subject,
       score
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值