当前位置: 首页> 汽车> 行情 > 数据场景练习

数据场景练习

时间:2025/7/10 16:52:11来源:https://blog.csdn.net/weixin_43875878/article/details/142101680 浏览次数: 0次

1 行列转换

(1) 列拆分为多行

         把指定字段按指定分隔符进行拆分为多行,然后其它字段直接复制.

select字段列,hobby2
from tbl
-- lateral view udtf(expression) tablealias as columnalias (‘,’ columnalias)
lateral view explode(split(hobby,';')) temp as hobby2

(2) 行扁平化

        把同一组的多行数据合并成为一行.

select id, name, age, split(concat_ws(':',collect_list(hobby)),':')[0] as hobby1,split(concat_ws(':',collect_list(hobby)),':')[1] as hobby2,split(concat_ws(':',collect_list(hobby)),':')[2] as hobby3
from tbl
group by id, name, age;

(3) 列转行

        如果数据一列有相同的值,按照指定的字段,将其中一列的字段内容变成不同的列,然后把多行数据转换为一行数据.

selectname,max(if(wek = '周一',work_hour,0)) as mon,max(if(wek = '周二',work_hour,0)) as tue,max(if(wek = '周三',work_hour,0)) as wed,max(if(wek = '周四',work_hour,0)) as thu,max(if(wek = '周五',work_hour,0)) as fi,max(if(wek = '周六',work_hour,0)) as sat,max(if(wek = '周日',work_hour,0)) as sun
from tbl
group by name

(4) 行转列

        把数据字段的字段名转换为一列,把数据行变为数据列.

select name, 'mon' as wek, mon as work_hour from tbl
union all
select name, 'tue' as wek, tue as work_hour from tbl
union all
select name, 'wed' as wek, wed as work_hour from tbl
union all
select name, 'thu' as wek, thu as work_hour from tbl
union all
select name, 'fri' as wek, fri as work_hour from tbl
union all
select name, 'sat' as wek, sat as work_hour from tbl
union all
select name, 'sun' as wek, sun as work_hour from tbl

关键字:数据场景练习

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: