Greenplum常用函数
字符串函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| string || string | text | 字符串连接 | ‘Post’ || ‘greSQL’ | PostgreSQL |
| length(string) | int | string 中字符串的数目 | length(‘jose’) | 4 |
| position(substring in string) | int | 指定的子字符串的位置 | position(‘om’ in ‘Thomas’) | 3 |
| substring(string [from int] [for int]) | text | 抽取子字符串 | substring(‘Thomas’ from 2 for 3) | hom |
| trim([leading | tailing | both] [characters] from string) | text | 从字符串string的开头 / 结尾 / 两边删除只包含characters中字符串（默认是一个空白）的最长的字符串 | trim(both ‘x’ from ‘xTomxx’) | Tom |
| lower(string) | text | 把字符串转为小写 | lower(‘TOM’) | tom |
| upper(string) | text | 把字符串转为大写 | upper(‘tom’) | TOM |
| overlay(string placing string from int [for int]) | text | 替换子字符串 | overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) | Thomas |
| replace(string text,from text , to text) | text | 把字符串string中出现的所有子字符串from替换成子字符串to | replace(‘abcdefabcdef’,‘cd’,‘XX’) | abXXefabXXef |
| split_part(string text,delimiter text,field int) | text | 根据delimiter分割string返回生成的第field个子字符串（1为基） | split_part(‘abc|def|ghi’,’|’,2) | def |
字符串拼接示例：
- select ‘green’ || ‘plum’ as dbname;
————
grennplum
以 | 为分隔符，将字符串分割：
- select split_part(col,’|’,1) , split_part(col,’|’,2)
from (values (‘hello|world’),(‘greenplum|database’)) t(col);
————
其中 Values 是 Greenplum 特有的语法，在这里可以将其看成一张表，表中有两行数据，表名为t，字段名为col，Values的用法如下：
- values (‘hello|world!’),(‘greenplum|database’);
————
hello|world!
greenplum|database
获取字符串的第2个字符之后的3个字符：
- select substr(‘hello world’,2,3);
————
ell
获取子串在字符串中的位置：
- select position(‘world’ in ‘hello world’);
————
7
时间函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| age(timestamp,timestamp) | interval | 减去参数后的“符号化”结果 | age(timestamp ‘2001-04-01’, timestamp ‘1957-06-13’) | 43 years 9 mons 27 days |
| age(timestamp) | interval | 从current_date减去参数后的结果 | age(timestamp ‘1957-06-13’) | 43 years 8 mons 3 days |
| current_date | date | 当前的日期 | ||
| current_time | time with time zone | 当前时间 | ||
| current_timestamp | timestamp with time zone | 当前事务开始时的时间戳 | ||
| date_part(text,timestamp) | double precision | 获取子域（等效于extract） | date_part(‘hour’,timestamp ‘2001-02-16 20:38:40’) | 20 |
| date_trunc(text,timestamp) | timestamp | 阶段成指定的精度 | date_trunc(‘hour’,timestamp ‘2001-02-16 20:38:40’) | 2001/2/16/20:00 |
| extract(field from timestamp) | double precision | 获取子域 | extract(hour from timestamp ‘2001-02-16 20:38:40’) | 20 |
| now() | timestamp witj time zone | 当前事务开始时的时间戳 |
时间加减：
- select ‘2011-10-01 10:0:0’::timestamp + interval ‘10 days 2 hours 10 seconds’;
————
2011-10-11 12:00:10
Interval 是一种表示时间间隔的一种数据类型。利用 interval 这种数据类型可以实现时间的加减，两个时间的时间差就是一个 interval 类型。获取当前时间：
- select now() , current_date, current_time, current_timestamp;
————
获取当月的第一天：
- select date_trunc(‘months’,now())::date;
————
2012-01-01
获取当前时间距离 2011-10-10 10:10:10 过了多少秒：
- select extract (epoch from now() - ‘2011-10-10 10:10:10’);
————
8403301.725044
数值计算函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| abs(x) | (与x相同) | 绝对值 | abs(-17.4) | 17.4 |
| ceil(dp 或 numeric) | (与输入相同) | 不小于参数的最小的整数 | ceil(-42.8) | -42 |
| ceiling(dp 或 numeric) | (与输入相同) | 不小于参数的最小整数(ceil 的别名) | ceiling(-95.3) | -95 |
| exp(dp 或 numeric) | (与输入相同) | 自然指数 | exp(1.0) | 2.718281828 |
| ln(dp 或 numeric) | (与输入相同) | 自然对数 | ln(2.0) | 0.693147181 |
| log(dp 或 numeric) | (与输入相同) | 以10为底的对数 | log(100.0) | 2 |
| log(b numeric , x numeric) | numeric | 以b为底数的对数 | log(2.0,64.0) | 6 |
| mod(y,x) | (与参数类型相同) | y/x的余数（模） | mod(9,4) | 1 |
| pi() | dp | “π”常量 | pi() | 3.141592654 |
| power(a numeric,b numeric) | numeric | a 的 b次幂 | power(9.0,3.0) | 729 |
| radians(dp) | dp | 把角度转为弧度 | radians(45.0) | 0.785398163 |
| random() | dp | 0.0~1.0之间的随机数 | random() | |
| floor(dp 或 numeric) | (与输入相同) | 不大于参数的最大整数 | floor(-42.8) | -43 |
| round(v numeric, s int) | numeric | 圆整为s位的小数 | round(42.4382,2) | 42.44 |
| sign(dp 或 numeric) | (与输入相同) | 参数的符号(-1,0,+1) | sign(-8.4) | -1 |
| sqrt(dp 或 numeric) | (与输入相同) | 平方根 | sqrt(4.0) | 2 |
| cbrt(dp) | dp | 立方根 | cbrt(27.0) | 3 |
| trunc(v numeric,s int) | numeric | 截断为s位小数 | trunc(42.4382) | 42.43 |
其他常用函数
1. 序列号生成函数 generate_series
该函数生成多行数据，从一个数字（start）到另外一个数字（end）按照一定的间隔，默认是1，生成一个结果集，具体的使用方法如下：
- select * from generate_series(6,10);
————
6
7
8
9
10
我们可以很方便地使用这个函数来创建一些测试表的数据：
- create tabke test_gen as select generate_series(1,10000) as id , ‘hello’ :: text as name distributed by (id);
我们还可以用 generate_series来做一些数值计算，比如，计算1~2000之建所有的奇数之和：
- select sum(num) from generate_series(1,2000,2) num;
————
1000000
2. 字符串列转行函数 string_agg
有时候我们需要讲一个列的字符串按照某个分隔符将其拼接起来：
- select * from test_string;
————
要按照id字段将字符串拼接起来，可以像下面这样使用string_agg来实现：
- select id,string_agg(str,’|’) from test_string group by id;
————
我们还可以先按照某一个字段做排序，再做拼接：
- select id,string_agg(str,’|’，order by str) from test_string group by id;
————
3.字符串行转列 regexp_split_to_table
把拼好的字符串重新拆分
- select * from test_string2;
———— - select id,regexp_split_to_table(str,E’\|’) str from test_string2;
4.hash函数 md5，hashbpchar
md5 的 hash算法的精确度是128位，返回值是一个字符串
- select md5(‘helloworld’);
————
Hashbpchar 的精确度是32位的，返回值是一个 integer 类型
- select hashbpchar(‘helloworld’);
————
252807993