GreenPlum常用数值计算函数

GreenPlum常用数值计算函数：

测试代码：

eda_gp=> select abs(-);
 abs  
------
 
( row)

eda_gp=> select ceil(-);
 ceil 
------
  -
( row)

eda_gp=> select ceiling(-);
 ceiling 
---------
     -
( row)

eda_gp=> select exp(e);
ERROR:  column "e" does not exist
LINE : select exp(e);
                   ^
eda_gp=> select exp();
        exp         
--------------------
 
( row)

eda_gp=> select ln(e);
ERROR:  column "e" does not exist
LINE : select ln(e);
                  ^
eda_gp=> select ln();
 ln 
----
  
( row)

eda_gp=> select log();
 log 
-----
   
( row)

eda_gp=> select log(,);
        log         
--------------------
 
( row)

eda_gp=> select mod(,);
 mod 
-----
   
( row)

eda_gp=> select pi();
        pi        
------------------
 
( row)

eda_gp=> select power(,);
 power 
-------
   
( row)

eda_gp=> select radians();
      radians      
-------------------
 
( row)

eda_gp=> select floor(-);
 floor 
-------
   -
( row)

eda_gp=> select round(,);
 round  
--------
 
( row)

eda_gp=> select sign();
 sign 
------
    
( row)

eda_gp=> select sign(-);
 sign 
------
   -
( row)

eda_gp=> select sqrt();
       sqrt       
------------------
 
( row)

eda_gp=> select cbrt();
 cbrt 
------
    
( row)

eda_gp=> select trunc(,);
 trunc 
-------
 
( row)
           

其他常用函数：

（1）序列号生成函数—generate_series,该函数生成多行数据，默认间隔为1。

eda_gp=> select generate_series(,);
 generate_series 
-----------------
               
               
               
               
               
               
( rows)

eda_gp=> select sum(num) from generate_series(,,)  num;
 sum  
------
 
( row)

eda_gp=> create table test_gen as select generate_series(,) as id,'hell0'::text as name distribute by(id);
ERROR:  syntax error at or near "distribute"
LINE : ... generate_series(,) as id,'hell0'::text as name distribute...
                                                             ^
eda_gp=> create table test_gen as select generate_series(,) as id,'hell0'::text as name distributed by(id);
SELECT 
eda_gp=> select * from test_gen;
 id | name  
----+-------
   | hell0
   | hell0
   | hell0
   | hell0
   | hell0
   | hell0
( rows)
           

（2）字符串列转行函数—string_agg

eda_gp=> select * from test_String;
 id |  str   
----+--------
   | hello
   | world
   | system
   | green
   | plum
( rows)

eda_gp=> select id,string_agg(str,'|') from test_string group by id;
 id |     string_agg     
----+--------------------
   | green|plum
   | hello|world|system
( rows)

eda_gp=> select id,string_agg(str,'|' order by str) from test_string group by id;
 id |     string_agg     
----+--------------------
   | hello|system|world
   | green|plum
( rows)
           

（3）字符串行转列函数—regexp_split_to_table

eda_gp=> select * from test_string2;
 id |     string_agg     
----+--------------------
   | hello|system|world
   | green|plum
( rows)

eda_gp=> select id,regexp_split_to_table(str,E'\\|') str from test_string2;
ERROR:  column "str" does not exist
LINE : select id,regexp_split_to_table(str,E'\\|') str from test_st...
                                        ^
eda_gp=> select id,regexp_split_to_table(string_agg,E'\\|') str from test_string2;
 id |  str   
----+--------
   | green
   | plum
   | hello
   | system
   | world
( rows)
           

（4）hash函数—md5,hashbpchar

eda_gp=> select md5('helloworld');
               md5                
----------------------------------
 fc5e038d38a57032085441e7fe7010b0
( row)

eda_gp=> select hashbpchar('helloworld');
 hashbpchar 
------------
  
( row)