HIVE多行转多列 和 多列转多行
1、HIVE多行转多列
源数据样式
作者：zhengzaifeidelushang
把CAMERA_NO，RESULT_DATA两列转换为CAMERA_NO字段的数据为列名，RESULT_DATA字段对应CAMERA_NO的数据为值
方法一：利用str_to_map函数
alter table ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results_new add if not exists partition(date='{DATE}' ,hour='{HOUR}',minutes='{MINUTES}');
insert overwrite table ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results_new partition(date='{DATE}' ,hour='{HOUR}',minutes='{MINUTES}')
select
LOT_NO,
LOT_NAME,
MEASURE_NO,
info['S1'] AS S1,
info['S2'] AS S2,
info['S3'] AS S3,
info['S4'] AS S4,
CREATEDATE,
CREATETIME
from
(
select LOT_NO,LOT_NAME,MEASURE_NO,str_to_map(concat_ws(',',collect_set(concat_ws(':',CAMERA_NO,RESULT_DATA)))) as info,CREATEDATE,CREATETIME
FROM ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results
where date='{DATE}' and hour='{HOUR}' and minutes='{MINUTES}'
group by LOT_NO,LOT_NAME,MEASURE_NO,CREATEDATE,CREATETIME
) a
转换后结果为
方法二：利用case when函数
select LOT_NO,LOT_NAME,MEASURE_NO,max(case when CAMERA_NO='S1' then RESULT_DATA else null end) as S1,
max(case when CAMERA_NO='T1' then RESULT_DATA else null end ) as T1,CREATEDATE,CREATETIME
from ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results
group by LOT_NO,LOT_NAME,MEASURE_NO,CREATEDATE,CREATETIME
str_to_map(字符串参数, 分隔符1, 分隔符2)
使用两个分隔符将文本拆分为键值对。
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’
取用map里的字段，用[""]即可
可以直接转换取用，而不需要存储字段
select LOT_NO,LOT_NAME,MEASURE_NO,str_to_map(concat_ws(',',collect_set(concat_ws(':',CAMERA_NO,RESULT_DATA)))) as info,CREATEDATE,CREATETIME
FROM ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results
group by LOT_NO,LOT_NAME,MEASURE_NO,CREATEDATE,CREATETIME
LIMIT 1
查询结果为
concat()函数可以连接一个或者多个字符串
concat_ws(separator,str1,str2)第一个参数是其它参数的分割符，分割符的位置放在要连接的两个字符串之间。
group_concat()分组拼接函数
group_concat([DISTINCT] 要连接的字段 [order by asc/desc 排序字段] [Separator ‘分隔符’])
将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重
select * from tv
在这里插入图片描述
select username,collect_set(video_name) from tv group by username
例子：
方法一：利用str_to_map函数
select name
,info['语文'] as Chinese
,info['数学'] as Math
,info['英语'] as English
from (select name,str_to_map(concat_ws(',',collect_set(concat_ws(':',subject,cast(score as string))))) as info
from test
group by name
) a
方法二：利用case when函数
select name
,max(case when subject = '语文' then score else 0 end) as Chinese
,max(case when subject = '数学' then score else 0 end) as Math
,max(case when subject = '英语' then score else 0 end) as English
from test
group by name
2、HIVE多列转多行
将多列数据转换成瘦长的格式
select * from test
遍历数组中的每一列：
select col1,col2,name
from test
lateral view explode(col3) col3 as name;
将输入的一行数组或者map转换成列输出
语法：explode(array (or map))
SELECT explode(myCol) AS myNewCol FROM myTable;
lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
hive> select s.x,sp from test.dual s
lateral view explode(split(concat_ws(',','1','2','3','4','5','6','7','8','9'),
',')) t as sp;
x sp
a 1
b 2
a 3
from后面是表名，在表名后面加lateral view explode（行转列sql） ，还必须要起一个别名，我这个字段的别名为sp。然后再看看select后面的 s.*，就是原表的字段，我这里面只有一个字段，且为x
例子：
select top 100 SN,TestProject,FreValue from SFC1216J.dbo.T_AnalysisData
alter table dw.acoustics.2050_mea_f_t_analysisdata add if not exists partition(date='{DATE}' ,hour='{HOUR}');
insert overwrite table dw.acoustics.2050_mea_f_t_analysisdata partition(date='{DATE}' ,hour='{HOUR}')
select id,
test_id,
sn,
productnumber,
model,
processid,
routeid,
testproject,
frevalue,
frevalue_new,
case when testproject='F0' then '0'
else split(frevalue_new,'_')[0] end as fre_option,
case when testproject='F0' then split(frevalue_new,'_')[0]
else split(frevalue_new,'_')[1] end as fre_value,
ispass,
servertime
from ods.acoustics.sfc_2050_sfc1216j.2050_t_analysisdata
lateral view explode(split(frevalue, ';')) tmpTable as frevalue_new
where date='{DATE}' and hour='{HOUR}' and ServerTime>='2019-11-16'
例子：
select a.date
,b.label
,b.value
from (select *
from daily_report
) a
LATERAL VIEW explode (map(
'UV', uv
,'新增UV', newuv
,'视频存量', video
,'新增视频', newvideo
,'播放量', vv
,'会员数', vip_num
,'新增会员数', new_vip_num
)) b as label, value
作者：zhengzaifeidelushang
