SQL实战之行列互转

一. 行转列

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

有多种方式，我将一一枚举：
1.1 CASE WHEN/IF

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

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

复制代码

1.2 Get_Json_Object

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

1. select t1.uid
2.       , get_json_object(t1.st, '$.chn') as chn
3.           , get_json_object(t1.st, '$.eng') as eng
4.           , get_json_object(t1.st, '$.math') as math
5. from (
6.         select uid
7.                 , concat('{', concat_ws(',', collect_set(concat('"', subject, '"', ':', '"', score, '"'))), '}') as st
8.         from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
9.         group by uid
10. ) t1;

复制代码

1.3 Str_To_Map

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

1. select t1.uid
2. , t1.st['chn'] as chn
3. , t1.st['eng'] as eng
4. , t1.st['math'] as math
5. from (
6.         select uid
7.                 , str_to_map(concat_ws(';', collect_set(concat_ws(':', subject, score))), ';', ':') as st
8.         from (values (1, 'math', 87), (1, 'chn', 98), (1, 'eng', 85)) as t (uid, subject, score)
9.         group by uid
10. ) t1;

复制代码

1.4 总结

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


二. 行转列

数据如下：

2.1 UNION ALL

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

1. select name, '语文' as subject, chinese as grade
2. from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic)
3. union all
4. select name, '数学' as subject, math as grade
5. from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic)
6. union all
7. select name, '物理' as subject, pyhsic as grade
8. from values ('张三', 74, 83, 93), ('李四', 74, 84, 94) as t (name, chinese, math, pyhsic);

复制代码

2.2 EXPLODE 

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

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

复制代码

2.3 总结

        以上就是我介绍的2种列转行方式，发起各人使用第1种方式，主打一个快捷省事。


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。