Hive 行转列 一个有趣的案例

Hive 行转列 一个有趣的案例

1、业务场景：

今天，要使用Hive表做特征数据集，有这么一个需求，有 db.Table1 如下表，course 字段共有3个值，但这，并不是所有的学生都选择了这三门课程，为训练模型，要把这些窄表变成宽表，把没选择课程设为0分，输出结果如下db.Table2。SQL门外汉，差点难死，还好得到强大的小伙伴们的支持。

db.Table1

student_id	course	score
S1	A	90
S1	B	89
S1	C	93
S2	B	95
S2	C	86
S3	A	99
…	…	…

db.Table2

student_id	courseA	courseB	courseC
S1	90	89	93
S2	0	95	86
S3	99	0	0
…	…	…	…

2、Hive SQL 实现

select
  student_id,
  max(nvl(kv['A'], 0)) AS courseA,
  max(nvl(kv['B'], 0)) AS courseB,
  max(nvl(kv['C'], 0)) AS courseC

from (select student_id, str_to_map(concat_ws('=',course, cast(score as string)),' ','=') kv from  db.Table1) t

group by student_id

3、SQL分析：

1、相关函数：

cast()   -- 类型的显示转换，例如cast('100' as INT)，如转换失败，则cast()返回null
--------------------------------------------------------------------------
concat_ws(separator,str1,str2,…)   
      -- 表示，把字符串拼接，第一个参数是分割符，后面是要拼接的字符串
--------------------------------------------------------------------------
str_to_map('k1=v1 k2=v2 k3=v3',' ','=')  
      -- 表示把字符串转换成 map 形式
      -- 共有三个参数：
      -- 第一个参数，表示要转换的字符串，
      -- 第二个参数，表示字符串中每对 key-value 之间的分割符
      -- 第三个参数，表示每对key-value，key与value之间的分割符
      -- 上例表示，= 前面是key，后面是value
--------------------------------------------------------------------------
nvl(expr1, expr2)
      -- 空值转换函数
      -- 如果expr1为null，返回值为 expr2，否则返回expr1

2、具体思路：

1. 第一步，先把每行要转换的 key-value对，借助cast() 、concat_ws()、str_to_map() 转换成map形式。
2. 第二步，借助 group by 语句实现，在这个里面使用了max()，很显然，只有存在的值，是>=0的，其他都是0，使用可以使用max()。
3. 优点，总算是把功能实现了，尴尬…
4. 缺点，要已知所有的key，如果key比较多，写也挺麻烦的。小伙伴讲，可以使用 case when语句，值得去探究。

声明： 总结学习，有问题或不当之处，可以批评指正哦，谢谢。