行转列
- concat(‘字符串’,‘字符串’):拼接
- concat_ws(‘分隔符’,‘字符串’,‘字符串’):以分隔符拼接字符串,忽略为空的字符串,但若分隔符是null,则返回值也是null
- collect_set(字段):把字段值去重合并,转换成数组
select collect_set(name) from table_1
select concat_ws('-',collect_set(name)) from table_1
select concat_ws(',',xingzuo,grade) fenzu,concat_ws('|',collect_set(name)) value from table_1 group by concat_ws(',',xingzuo,grade)
列转行
- explode(字段):将字段值(array、map类型)拆分成多
- lateral view explode(字段) 表别名 as 字段别名:将字段拆成多行后命名为别名,拆分后原表中的其他字段可以按找对应关系进行扩展
select movie,explode(movie_type) movie_type_explode from table_2
报错
select movie,movie_type_explode from table_2 lateral view explode(movie_type) table_tmp as movie_type_explode