1.regexp_split_to_table以逗号切分,转为数据集
select regexp_split_to_table('飞机,火车,地铁,汽车', ',' )
2. 转为数组
select regexp_split_to_array('飞机,火车,地铁,汽车', ',' )
--取数组的第二个元素
select (regexp_split_to_array('飞机,火车,地铁,汽车', ',' ))[1]
3分组连接字符串
string_agg(expression, delimiter)
直接把一个表达式变成字符串
select nameid, string_agg(traffic,',') , update_time from test0001 group by nameid,update_time order by nameid,update_time;
--去重
select nameid, string_agg(distinct traffic,',') from test0001 group by nameid order by nameid ;
--排序
select nameid, string_agg(traffic,',' order by traffic) from test0001 group by nameid order by nameid ;
4 分组连接数组
array_agg(expression)
把表达式变成一个数组 一般配合 array_to_string() 函数使用
select nameid, array_agg(traffic ) from test0001 group by nameid order by nameid ;
--数组去重 、排序
select nameid, array_agg(distinct traffic order by traffic desc ) from test0001 group by nameid order by nameid ;
--按名称分组 ,按时间排序
select nameid, array_agg(traffic order by update_time asc) from test0001 group by nameid;
--按名称分组 ,按时间排序 ,取数组的第一个值
select nameid, (array_agg(traffic order by update_time asc))[1] from test0001 group by nameid;
5 数组转字符串
select nameid, array_to_string(array_agg(traffic),',') from test0001 group by nameid order by nameid ;