原标题:【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 male25 master 2018/1/2 2018/1/3
u002 LuBan male30 Bachelor 2018/3/4 2018/5/5
u003 ZhenJi female 27PhD 2018/4/3 2018/5/4
需求实现代码
步骤一实现
select
user_no,
str_to_map(concat_ws(',',collect_set(concat_ws(':', message, detail)))) message1
fromuser_info
groupbyuser_no
orderbyuser_no
collect_set形成的集合是无序的,若想得到有序集合,可以使用sort_array对集合元素进行排序。
select
user_no,
str_to_map(concat_ws(',',sort_array(collect_set(concat_ws(':', message, detail))))) message1
fromuser_info
groupbyuser_no
orderbyuser_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
fromuser_info
groupbyuser_no
orderbyuser_no
) a
上面代码就是提取map格式数据中的key与value,即列名['key']得到value值。
总结
长格式数据转换成宽格式数据,首先将数据转化成map格式数据,然后使用列名['key']得到每一个key的value。当然,也可以使用case when函数实现以及left join函数实现。返回搜狐,查看更多
责任编辑: