行转列
表名 ods.ods_user_from ,
字段 prov ,city
1、concat(字段a,’-’,字段b) ,concat函数参数有一个为NULL 返回 NULL
select concat(prov,'-',city) from ods.ods_user_from
2.collect_set(),collect_list(),可以根据字段A分组,获取字段B的数据列表,
collect_list()返回字段B全部数据,collect_set()返回字段B去重后的数据,它们返回的是 array 格式。
select prov,collect_list(city) from ods.ods_user_from group by prov limit 100;
3.concat_ws(‘分隔符’,字段A,字段B,字段C) , 或者
concat_ws(‘分隔符’,collect_set(字段B)) (这个形式是将数组转为字符串格式) ,返回 String 格式,如果分隔符为 NULL,则结果为 NULL
select prov ,concat_ws(':',collect_set(city)) list01
from (
select city,prov
from ods.ods_user_from
limit 1000 )a
group by prov
列换行
(1)explode() 参数类型是 array 或者 map ,
(2)split(字段B,‘分割符’) 返回一个 array-数组 类型。
(3)lateral view , lateral view是Hive中提供给UDTF的结合,它可以解决UDTF不能添加额外的 select列的问题,lateral view其实就是用来和想类似explode这种UDTF函数联用的,lateral view会将UDTF生成的结果放到一个虚拟表中,然后这个虚拟表会和原表输入行进行join(当然这个join 是虚拟的,不用我们写出来)来达到连接UDTF外的select字段的目的。 UDTF(一进多出)。
格式一:
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*
lateral view在UDTF前使用,表示连接UDTF所分裂的字段。
UDTF(expression):使用的UDTF函数,例如explode()。
tableAlias:表示UDTF函数转换的虚拟表的名称。
columnAlias:表示虚拟表的虚拟字段名称,如果分裂之后有一个列,则写一个即可;如果分裂之后有多个列,按照列的顺序在括号中声明所有虚拟列名,以逗号隔开。
格式二:
from basetable (lateral view)*
在from子句中使用,一般和格式一搭配使用,这个格式只是说明了lateral view的使用位置。
from子句后面也可以跟多个lateral view语句,使用空格间隔就可以了。
举例使用:在 3 的结果上
select prov,city
from (
select prov ,concat_ws(':',collect_set(city)) list01
from (
select city,prov
from ods.ods_user_from
limit 1000 )a
group by prov
) a
LATERAL VIEW explode( split(a.list01,':')) city_table as city;