Hive宽表窄表互转
ckckck2025年1月11日41 浏览背景
在工作中经常会遇到高表转宽表，宽表转窄表的场景，在此做一些梳理。
宽表转窄表
传统思路
使用SQL代码作分析的时候，几次遇到需要将长格式数据转换成宽格式数据，一般使用
LEFT JOIN或者CASE WHEN实现，代码看起来冗长，探索一下，可以使用更简单的方式实现长格式数据转换成宽格式数据。
sql
SELECT year,
MAX(CASE WHEN month=1 THEN money ELSE 0 END) AS M1,
MAX(CASE WHEN month=2 THEN money ELSE 0 END) AS M2,
MAX(CASE WHEN month=3 THEN money ELSE 0 END) AS M3,
MAX(CASE WHEN month=4 THEN money ELSE 0 END) AS M4
FROM sale
GROUP BY year;需求描述
某电商数据库中存在一张客户信息表user_info，记录着客户属性数据和消费数据，需要将左边长格式数据转化成右边宽格式数据。
需求实现
涉及函数：
str_to_map,concat_ws,collect_set,sort_array实现思路：
- 将客户信息转化成
map格式的数据。collect_set形成的集合是无序的，若想得到有序集合，可以使用sort_array对集合元素进行排序。- 将
map格式数据中的key与value提取出来，key就是每一列变量名，value就是变量值。
sql
SELECT
user_no,
message1['name'] AS name,
message1['sex'] AS sex,
message1['age'] AS age,
message1['education'] AS education,
message1['regtime'] AS regtime,
message1['first_buytime'] AS first_buytime
FROM
(SELECT
user_no,
str_to_map(concat_ws(',', sort_array(collect_set(concat_ws(':', message, detail))))) AS message1
FROM user_info
GROUP BY user_no
ORDER BY user_no
) a;窄表转宽表
长宽格式数据之间相互转换使用到的函数，可以叫做表格生成函数
需求描述
某电商数据库中存在表user_info1，以宽格式数据记录着客户属性数据和消费数据，需要将左边user_info1宽格式数据转化成右边长格式数据。
需求实现
步骤：
- 将宽格式客户信息转化成
map格式的数据。- 使用
explode函数将map格式数据中的元素拆分成多行显示。
sql
SELECT user_no, explode(message1)
FROM
(SELECT user_no,
map('name', name, 'sex', sex, 'age', age, 'education', education, 'regtime', regtime, 'first_buytime', first_buytime) AS message1
FROM user_info1
) a;总结
不管是将长格式数据转换成宽格式数据还是将宽格式数据转换成长格式数据，都是先将数据转换成
map格式数据。
- 长格式数据转换成宽格式数据：先将长格式数据转换成
map格式数据，然后使用列名['key']得到每一个key的value。- 宽格式数据转换成长格式数据：先将宽格式数据转换成
map格式数据，然后使用explode函数将map格式数据中的元素拆分成多行显示。顺便说一句，R语言中也是通过类似的方法实现长宽格式之间相互转换的。
end