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语句,值得去探究。

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

展开阅读全文

没有更多推荐了,返回首页