20160510 GreenPlum5 常用函数

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