hive与postgresql 之爆炸函数

炸裂函数regexp_split_to_table
select regexp_split_to_table('飞机，火车，地铁，汽车',  '，' )                    以逗号切分，转为数据集
select regexp_split_to_array('飞机，火车，地铁，汽车', '，' )                     转为数组


select (regexp_split_to_array('飞机，火车，地铁，汽车',  '，' ))[1]               取数组的第二个元素
select regexp_split_to_table('F:\QH本部文件\一套表部署相关\test.sh','\\')         正则匹配


array_agg(expression)       把表达式变成一个数组 一般配合 array_to_string() 函数使用
select nameid, array_agg(traffic ) from dbscheme.test0001 group by nameid order by nameid ;   变为数组


string_agg(expression, delimiter)    直接把一个表达式变成字符串
select nameid, string_agg(traffic,',') , update_time from dbscheme.test0001   
group by nameid,update_time order by  nameid,update_time;                     //相同id 的连接到一起，逗号分隔


select nameid, array_to_string(array_agg(traffic),',') from dbscheme.test0001 group by nameid order by nameid ; .数组转字符串

+----------+----------------------+--+
|   a.id   |        a.tim         |
+----------+----------------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  |
| f,b,c,d  | 1:10,2:20,3:30,4:40  |
+----------+----------------------+--+

select id,tim,single_tim 
from atlasdemo.a 
lateral view explode(split(tim,',')) t as single_tim
+----------+----------------------+-------------+--+
|    id    |         tim          | single_tim  |
+----------+----------------------+-------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 2:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 3:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 4:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 5:00        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 1:10        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 2:20        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 3:30        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 4:40        |
+----------+----------------------+-------------+--+

select id,tim,single_id_index,single_id 
from atlasdemo.a 
lateral view posexplode(split(id,',')) t as single_id_index, single_id;
+----------+----------------------+------------------+------------+--+
|    id    |         tim          | single_id_index  | single_id  |
+----------+----------------------+------------------+------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 0                | a          |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 1                | b          |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 2                | c          |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 3                | d          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 0                | f          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 1                | b          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 2                | c          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 3                | d          |
+----------+----------------------+------------------+------------+--+

select id,tim,single_id,single_tim from atlasdemo.a 
lateral view posexplode(split(id,',')) t as single_id_index, single_id
lateral view posexplode(split(tim,',')) t as single_yim_index, single_tim
where single_id_index = single_yim_index;
+----------+----------------------+------------+-------------+--+
|    id    |         tim          | single_id  | single_tim  |
+----------+----------------------+------------+-------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  | a          | 2:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | b          | 3:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | c          | 4:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | d          | 5:00        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | f          | 1:10        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | b          | 2:20        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | c          | 3:30        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | d          | 4:40        |
+----------+----------------------+------------+-------------+--+