【Hive】从长格式表到宽格式表的转换

前言

    使用sql代码作分析的时候，几次遇到需要将长格式数据转换成宽格式数据，一般使用left join或者case when实现，代码看起来冗长，探索一下，可以使用更简单的方式实现长格式数据转换成宽格式数据。

长宽格式数据

举个栗子

宽格式数据：每个变量单独成一列为宽格式数据，例如变量name、age等。

长格式数据：长数据中变量的ID没有单独列成一列，而是整合在同一列。

需求描述

    某电商数据库中存在一张客户信息表user_info，记录着客户属性数据和消费数据，需要将左边长格式数据转化成右边宽格式数据。

需求实现

做以下说明

当然，其他数据库语句可以同等替换上面的函数。

需求实现思路

步骤一：将客户信息转化成map格式的数据

u001  {"age":"25","education":"master","first_buytime":"2018/1/3","name":"Sulie","regtime":"2018/1/2","sex":"male"}	
u002  {"age":"30","education":"Bachelor","first_buytime":"2018/5/5","name":"LuBan","regtime":"2018/3/4","sex":"male"}	
u003  {"age":"27","education":"PhD","first_buytime":"2018/5/4","name":"ZhenJi","regtime":"2018/4/3","sex":"female"}

步骤二：将map格式数据中的key与value提取出来，key就是每一列变量名，value就是变量值

user_no  name    sex   age  education  regtime  first_buytime	
u001    Sulie   male    25   master    2018/1/2  2018/1/3	
u002    LuBan   male    30   Bachelor  2018/3/4  2018/5/5	
u003    ZhenJi  female  27      PhD    2018/4/3  2018/5/4

需求实现代码

步骤一实现

select	
     user_no,	
     str_to_map(concat_ws(',',collect_set(concat_ws(':', message, detail)))) message1	
     from user_info	
     group by user_no	
     order by user_no

    

    collect_set形成的集合是无序的，若想得到有序集合，可以使用sort_array对集合元素进行排序。

select	
     user_no,	
     str_to_map(concat_ws(',',sort_array(collect_set(concat_ws(':', message, detail))))) message1	
     from user_info	
     group by user_no	
     order by user_no

    上面代码用到了字符串连接函数、集合函数、排序函数以及字符串转化成map格式函数，可以每一步运行出来查看结果更容易理解。

步骤二实现

select 	
    user_no,	
    message1['name'] name,	
    message1['sex'] sex,	
    message1['age'] age,	
    message1['education'] education,	
    message1['regtime'] regtime,	
    message1['first_buytime'] first_buytime	
from 	
  (select	
      user_no,	
      str_to_map(concat_ws(',',collect_set(concat_ws(':', message, detail)))) message1	
      from user_info	
      group by user_no	
      order by user_no	
   ) a

上面代码就是提取map格式数据中的key与value，即列名['key']得到value值。

总结

    长格式数据转换成宽格式数据，首先将数据转化成map格式数据，然后使用列名['key']得到每一个key的value。当然，也可以使用case when函数实现以及left join函数实现。