直接上干货,HiveSQL高级进阶技巧,重要性不言而喻。掌握这10个技巧,你的SQL水平将有一个质的提升!
1.删除:
insert overwrite tmp
select * from tmp where id != '666';
2.更新:
insert overwrite tmp
select id,label,
if(id = '1' and label = 'grade','25',value) as value
from tmp where id != '666';
3.行转列:
-- Step03:最后将info的内容切分
select id,split(info,':')[0] as label,split(info,':')[1] as value
from
(
-- Step01:先将数据拼接成“heit:180,weit:60,age:26”
select id,concat('heit',':',height,',','weit',':',weight,',','age',':',age) as value
from tmp
) as tmp
-- Step02:然后在借用explode函数将数据膨胀至多行
lateral view explode(split(value,',')) mytable as info;
4.列转行1:
select
tmp1.id as id,tmp1.value as height,tmp2.value as weight,tmp3.value as age
from
(select id,label,value from tmp2 where label = 'heit') as tmp1
join
on tmp1.id = tmp2.id
(select id,label,value from tmp2 where label = 'weit') as tmp2
join
on tmp1.id = tmp2.id
(select id,label,value from tmp2 where label = 'age') as tmp3
on tmp1.id = tmp3.id;
5.列转行2:
select
id,tmpmap['height'] as height,tmpmap['weight'] as weight,tmpmap['age'] as age
from
(
select id,
str_to_map(concat_ws(',',collect_set(concat(label,':',value))),',',':') as tmpmap
from tmp2 group by id
) as tmp1;