SQL实战之行列互转

一. 行转列

    Hive中某表存放用户不同科目考试成绩，多行存放，看起来不美观，想要在一行中展示用户所有科目成绩，数据如下：

有多种方式，我将一一列举：

1.1 CASE WHEN/IF

    最常见的就是 CASE WHEN 了，不过为了代码简洁我们使用 IF 函数，代码如下：

select uid
    , max(if(subject = 'chn', score, null)) as chn
    , max(if(subject = 'eng', score, null)) as eng
    , max(if(subject = 'math', score, null)) as math
from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
group by uid;

1.2 Get_Json_Object

    可以将用户的所有成绩先聚合成一个大Json字符串，然后使用 get_json_onject 获取Json中相应字段即可，代码如下：

select t1.uid
      , get_json_object(t1.st, '$.chn') as chn
      , get_json_object(t1.st, '$.eng') as eng
      , get_json_object(t1.st, '$.math') as math
from (
    select uid
        , concat('{', concat_ws(',', collect_set(concat('"', subject, '"', ':', '"', score, '"'))), '}') as st
    from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
    group by uid
) t1;

1.3 Str_To_Map

    还可以将用户的成绩生成一个 map，通过 map['field'] 的方式获取字段数值，代码如下： 

select t1.uid
, t1.st['chn'] as chn
, t1.st['eng'] as eng
, t1.st['math'] as math
from (
    select uid
        , str_to_map(concat_ws(';', collect_set(concat_ws(':', subject, score))), ';', ':') as st
    from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
    group by uid
) t1;

1.4 总结

    以上就是3种行转列的方法，还有一种是生成 struct 结构的方式，在次我就不赘述了，实用性当然是第1种方便了，其他2种可以适当装个13。

二. 行转列

数据如下：

2.1 UNION ALL

    union all 是常用方法，代码如下：

select name, '语文' as subject, chinese as grade
from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic)
union all
select name, '数学' as subject, math as grade
from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic)
union all
select name, '物理' as subject, pyhsic as grade
from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic);

2.2 EXPLODE

    先将数据生成 map ，然后再用 explode 函数炸开它，代码如下：

select t1.name, subject, grade
from (    
select name        
, str_to_map(concat('语文', ':', chinese, ';', '数学', ':', math, ';', '物理', ':', pyhsic), ';', ':') as lit    
from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) 
as t (name, chinese, math, pyhsic)) t1    
lateral view explode(t1.lit) tmp as subject, grade;

2.3 总结

    以上就是我介绍的2种列转行方式，建议大家使用第1种方式，主打一个快捷省事。