hive 行列转换

 

-- 列转行
select
     name
    ,str[0] as id
    ,str[1] as age
from (
    select
         name
        ,split(name_tmp,',') as str
    from (
        select 'a,10|b,11|c,12' as name
    ) t1
    lateral view explode(split(name,'\\|')) b AS name_tmp
) t2
;

+-----------------+-----+------+--+
|      name       | id  | age  |
+-----------------+-----+------+--+
| a,10|b,11|c,12  | a   | 10   |
| a,10|b,11|c,12  | b   | 11   |
| a,10|b,11|c,12  | c   | 12   |
+-----------------+-----+------+--+

 

-- 行转列
with tmp as (
    select '1' as user_id, '001' as newlabelid union all
    select '1' as user_id, '002' as newlabelid union all
    select '1' as user_id, '002' as newlabelid
)
select 
     user_id
    ,collect_set(cast(newlabelid as string))  as user_id_set
    ,collect_list(cast(newlabelid as string)) as user_id_list
from tmp
group by user_id
;

+----------+----------------+----------------------+--+
| user_id  |  user_id_set   |     user_id_list     |
+----------+----------------+----------------------+--+
| 1        | ["001","002"]  | ["001","002","002"]  |
+----------+----------------+----------------------+--+

 

-- IDE导出
select 
     t2.rn
    ,concat_ws('|', collect_set(concat(t2.line))) as line -- 行的分隔符
from (
    select
         concat_ws(',',t1.id,t1.name) as line -- 列的分隔符
        ,row_number() over() % 2      as rn   -- 最终输出的数据有几行
    from (
        select
             nvl(cast(t0.id as string),'')   as id
            ,nvl(cast(t0.name as string),'') as name
        from (
            select '广东' as id, 40 as name union all
            select '广西' as id, 20 as name union all
            select '山东' as id, 30 as name
        ) t0
        order by name desc -- 输出排序
    ) t1
) t2
group by t2.rn
;

+-----+--------------+--+
| rn  |     line     |
+-----+--------------+--+
| 0   | 山东,30        |
| 1   | 广东,40|广西,20  |
+-----+--------------+--+

 

-- 分组并合并成一个字段
select
     name
    ,flag1
    ,flag2
    ,concat_ws(',',flag1) as flag3
    ,concat_ws(',',flag2) as flag4
from (
    select
         name
        ,collect_set(cast(flag as string))  as flag1
        ,collect_list(cast(flag as string)) as flag2
    from (
        select 'a' as name, 1 as flag union all
        select 'a' as name, 1 as flag union all
        select 'a' as name, 2 as flag
    ) t1
    group by name
) t2
;

 

 

select
     strs
    ,strs[0] as k
    ,strs[1] as id
    ,strs[2] as age
from (
    select
        split(str_new,',') as strs
    from (
        select
            explode(split(str,'\\|')) as str_new
        from(
            select
                concat(
                     key,',',a_id,',',a_age,'|'
                    ,key,',',b_id,',',b_age,'|'
                    ,key,',',c_id,',',c_age
                ) as str
            from (
                select '001' as key,'a' as a_id,1 as a_age,'b' as b_id,2 as b_age,'c' as c_id,3 as c_age
            ) t1
        ) t2
    ) t3
)t4
;

+------------------+------+-----+------+--+
|       strs       |  k   | id  | age  |
+------------------+------+-----+------+--+
| ["001","a","1"]  | 001  | a   | 1    |
| ["001","b","2"]  | 001  | b   | 2    |
| ["001","c","3"]  | 001  | c   | 3    |
+------------------+------+-----+------+--+