1 序列生成函数
生成多行数据
#select * from generate_series(6,10);
---6
7
8
9
10
可以创建测试表数据,很方便
#create table xh as select generate_series(1,100) as id,'erlonglong'::text as name distributed by(id);
2 字符串列转行
原始数据为:
id str
1 hello
1 world
2 greenplum
2 datebase
2 system
#select id,string_agg(str,'|') from lianxi group by id; ---2 greenplum|datebase|system
1 hello|world
#select id,string_agg(str,'|' order by str) from lianxi group by id; ---2 datebase|greenplum|system
1 hello|world
3 字符串列转行
原始数据为:
id str
2 database|greenplum|system
1 hello|world
#select id,regexp_split_to_table(str,E'\\|') str from lianxi; ---2 database
2 greenplum
2 system
1 hello
1 world