Hive行转列、列转行

目录

行转列

列转行

---

行转列

数据准备：

create table stu_score(
    stu_id string,
    english bigint,
    math bigint,
    science bigint
);

insert into stu_score values
    ('甲',100,90,95),
    ('乙',95,90,100),
    ('丙',65,99,88);

行转列： 主要用到 Leteral view explode

select 
     stu_id
    ,subject,score
from stu_score 
LATERAL VIEW explode (
    map(
    'english',english,
    'math',math,
    'science',science
        ) ) tmp  
    as subject,score;

列转行

数据准备：

-- 直接拿行转列的结果演示
create table stu_score_row  
as
    select 
         stu_id
        ,subject,score
    from stu_score 
    LATERAL VIEW explode (
        map(
        'english',english,
        'math',math,
        'science',science
            ) ) tmp  
        as subject,score;

列转行：主要用到 concat_wc 和 collect_list

select 
    stu_id
    ,concat_ws(',',collect_list(subject)) as subs
from stu_score_row
 group by stu_id
 ;

-- collect_list 不去重，collect_set 去重。 字段类型需要是String

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！