数据类型:
整型: smallint integer bigint
任意精度数值: numeric(precision,scale) 精度、刻度
浮点型: real
序号类型(自增): serial bigserial
字符类型:varchar(n) char(n) text
日期类型:timestamp8字节 2013-05-17 13:01:38.437925
Interval 12字节 555283:40:10
date 4字节 2013-05-17
time 8字节 13:01:53.890859
数组类型:integer[] 存储 array[21000,22000,23000,24000]
函数:
数学函数:
函数 | 返回类型 | 描述 | 例子 | 结果 |
abs(x) |
| 绝对值 | abs(-17.4) | 17.4 |
cbrt(double) |
| 立方根 | cbrt(27.0) | 3 |
ceil(double/numeric) |
| 不小于参数的最小的整数 | ceil(-42.8) | -42 |
degrees(double) |
| 把弧度转为角度 | degrees(0.5) | 28.6478897565412 |
exp(double/numeric) |
| 自然指数 | exp(1.0) | 2.71828182845905 |
floor(double/numeric) |
| 不大于参数的最大整数 | floor(-42.8) | -43 |
ln(double/numeric) |
| 自然对数 | ln(2.0) | 0.693147180559945 |
log(double/numeric) |
| 10为底的对数 | log(100.0) | 2 |
log(b numeric,x numeric) |
| numeric指定底数的对数 | log(2.0, 64.0) | 6.0000000000 |
mod(y, x) |
| 取余数 | mod(9,4) | 1 |
pi() | double | "π"常量 | pi() | 3.14159265358979 |
power(a double, b double) | double | 求a的b次幂 | power(9.0, 3.0) | 729 |
power(a numeric, b numeric) | numeric | 求a的b次幂 | power(9.0, 3.0) | 729 |
radians(double) | double | 把角度转为弧度 | radians(45.0) | 0.785398163397448 |
random() | double | 0.0到1.0之间的随机数值 | random() |
|
round(double/numeric) |
| 圆整为最接近的整数 | round(42.4) | 42 |
round(v numeric, s int) | numeric | 圆整为s位小数数字 | round(42.438,2) | 42.44 |
sign(double/numeric) |
| 参数的符号(-1,0,+1) | sign(-8.4) | -1 |
sqrt(double/numeric) |
| 平方根 | sqrt(2.0) | 1.4142135623731 |
trunc(double/numeric) |
| 截断(向零靠近) | trunc(42.8) | 42 |
trunc(v numeric, s int) | numeric | 截断为s小数位置的数字 | trunc(42.438,2) | 42.43 |
三角函数:
函数 | 描述 |
acos(x) | 反余弦 |
asin(x) | 反正弦 |
atan(x) | 反正切 |
atan2(x, y) | 正切 y/x 的反函数 |
cos(x) | 余弦 |
cot(x) | 余切 |
sin(x) | 正弦 |
tan(x) | 正切 |
字符串函数:
函数 | 返回类型 | 描述 | 例子 | 结果 |
string || string | text | 字串连接 | 'Post' || 'greSQL' | PostgreSQL |
char_length(string) | int | 字串中的字符个数 | char_length('jose') | 4 |
convert(string using conversion_name) | text | 使用指定的转换名字改变编码。 | convert('PostgreSQL' using iso_8859_1_to_utf8) | 'PostgreSQL' |
lower(string) | text | 把字串转化为小写 | lower('TOM') | tom |
overlay(string placing string from int [for int]) | text | 替换子字串 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
substring(string [from int] [for int]) | text | 抽取子字串 | substring('Thomas' from 2 for 3) | hom |
substring(string from pattern) | text | 抽取匹配 POSIX正则表达式的子字串 | substring('Thomas' from '...$') | mas |
trim([leading | trailing | both] [characters] from string) | text | 从字串string的开头/结尾/两边/删除只包含characters(缺省是一个空白)的最长的字串 | trim(both 'x' from 'xTomxx') | Tom |
upper(string) | text | 把字串转化为大写。 | upper('tom') | TOM |
btrim(string text [, characters text]) | text | 从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串 | btrim('xyxtrimyyx','xy') | trim |
length(string text) | int | string中字符的数目 | length('jose') | 4 |
lpad(string text, length int [, fill text]) | text | 通过填充字符fill(缺省时为空白),把string填充为长度length。如果string已经比length长则将其截断(在右边)。 | lpad('hi', 5, 'xy') | xyxhi |
ltrim(string text [, characters text]) | text | 从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。 | ltrim('zzzytrim','xyz') | trim |
replace(string text, from text, to text) | text | 把字串string里出现地所有子字串from替换成子字串to。 | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
rpad(string text, length int [, fill text]) | text | 通过填充字符fill(缺省时为空白),把string填充为长度length。如果string已经比length长则将其截断。 | rpad('hi', 5, 'xy') | hixyx |
rtrim(string text [, character text]) | text | 从字串string的结尾删除只包含character(缺省是个空白)的最长的字 | rtrim('trimxxxx','x') | trim |
split_part(string text, delimiter text, field int) | text | 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
strpos(string, substring) | text | 声明的子字串的位置。 | strpos('high','ig') | 2 |
substr(string, from [, count]) | text | 抽取子字串。 | substr('alphabet', 3, 2) | ph |
translate(string text, from text, to text) | text | 把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 | translate('12345', '14', 'ax') | a23x5 |
数据格式函数:
函数 | 返回类型 | 描述 | 例子 |
to_char(timestamp, text) | text | 把时间戳转换成字串 | to_char(current_timestamp, 'HH12:MI:SS') |
to_char(interval, text) | text | 把时间间隔转为字串 | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
to_char(int, text) | text | 把整数转换成字串 | to_char(125, '999') |
to_char(double precision, text) | text | 把实数/双精度数转换成字串 | to_char(125.8::real, '999D9') |
to_char(numeric, text) | text | 把numeric转换成字串 | to_char(-125.8, '999D99S') |
to_date(text, text) | date | 把字串转换成日期 | to_date('05 Dec 2000', 'DD Mon YYYY') |
to_timestamp(text, text) | timestamp | 把字串转换成时间戳 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
to_timestamp(double) | timestamp | 把UNIX纪元转换成时间戳 | to_timestamp(200120400) |
to_number(text, text) | numeric | 把字串转换成numeric | to_number('12,454.8-', '99G999D9S') |
日期/时间函数:
函数 | 返回类型 | 描述 | 例子 | 结果 |
age(timestamp, timestamp) | interval | 减去参数,生成一个使用年、月的"符号化"的结果 | age('2001-04-10', 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 | 现在的时间 |
|
|
current_timestamp | timestamp | 日期和时间 |
|
|
date_part(text, timestamp) | double | 获取子域(等效于extract) | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
date_part(text, interval) | double | 获取子域(等效于extract) | date_part('month', interval '2 years 3 months') | 3 |
date_trunc(text, timestamp) | timestamp | 截断成指定的精度 | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00+00 |
extract(field from timestamp) | double | 获取子域 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
extract(field from interval) | double | 获取子域 | extract(month from interval '2 years 3 months') | 3 |
localtime | time | 今日的时间 |
|
|
localtimestamp | timestamp | 日期和时间 |
|
|
now() | timestamp | 当前的日期和时间(等效于 current_timestamp) |
|
|
timeofday() | text | 当前日期和时间 |
|
|
EXTRACT、date_part函数支持的field:
域 | 描述 | 例子 | 结果 |
DAY | (月分)里的日期域(1-31) | EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); | 16 |
DOW | 每周的星期号(0-6;星期天是0) (仅用于timestamp) | EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); | 5 |
DOY | 一年的第几天(1 -365/366) (仅用于 timestamp) | EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); | 47 |
HOUR | 小时域(0-23) | EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); | 20 |
MICROSECONDS | 秒域,包括小数部分,乘以 1,000,000。 | EXTRACT(MICROSECONDS from TIME '17:12:28.5'); | 28500000 |
MILLISECONDS | 秒域,包括小数部分,乘以 1000。 | EXTRACT(MILLISECONDS from TIME '17:12:28.5'); | 28500 |
MINUTE | 分钟域(0-59) | EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); | 38 |
MONTH | 对于timestamp数值,它是一年里的月份数(1-12);对于interval数值,它是月的数目,然后对12取模(0-11) | EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); | 2 |
QUARTER | 该天所在的该年的季度(1-4)(仅用于 timestamp) | EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); | 1 |
SECOND | 秒域,包括小数部分(0-59[1]) | EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40'); | 40 |
WEEK | 该天在所在的年份里是第几周。 | EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); | 7 |
YEAR | 年份域 | EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); | 2001 |
数组函数;
函数 | 返回类型 | 描述 | 例子 | 结果 |
array_cat(anyarray, anyarray) | anyarray | 连接两个数组 | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
array_append(anyarray, anyelement) | anyarray | 向一个数组末尾附加一个元素 | array_append(ARRAY[1,2], 3) | {1,2,3} |
array_prepend(anyelement, anyarray) | anyarray | 向一个数组开头附加一个元素 | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
array_dims(anyarray) | text | 返回一个数组维数的文本表示 | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
array_lower(anyarray, int) | int | 返回指定的数组维数的下界 | array_lower(array_prepend(0, ARRAY[1,2,3]), 1) | 0 |
array_upper(anyarray, int) | int | 返回指定数组维数的上界 | array_upper(ARRAY[1,2,3,4], 1) | 4 |
array_to_string(anyarray, text) | text | 使用提供的分隔符连接数组元素 | array_to_string(ARRAY[1, 2, 3], '~^~') | 1~^~2~^~3 |
string_to_array(text, text) | text[] | 使用指定的分隔符把字串拆分成数组元素 | string_to_array('xx~^~yy~^~zz', '~^~') | {xx,yy,zz} |
聚合函数:
1. AVG 返回指定组中的平均值,空值被忽略。
例:select prd_no,avg(qty) from sales group by prd_no
2. COUNT 返回指定组中项目的数量。
例:select count(prd_no) from sales
3. MAX 返回指定数据的最大值。
例:select prd_no,max(qty) from sales group by prd_no
4. MIN 返回指定数据的最小值。
例:select prd_no,min(qty) from sales group by prd_no
5. SUM 返回指定数据的和,只能用于数字列,空值被忽略。
例:select prd_no,sum(qty) from sales group by prd_no
6. COUNT_BIG 返回指定组中的项目数量,与COUNT函数不同的是COUNT_BIG返回bigint值,而COUNT返回的是int值。
例:select count_big(prd_no) from sales
7. GROUPING 产生一个附加的列,当用CUBE或ROLLUP运算符添加行时,输出值为1.当所添加的行不是由CUBE或ROLLUP产生时,输出值为0.
例:select prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup
8. BINARY_CHECKSUM 返回对表中的行或表达式列表计算的二进制校验值,用于检测表中行的更改。
例:select prd_no,binary_checksum(qty) from sales group by prd_no
9. CHECKSUM_AGG 返回指定数据的校验值,空值被忽略。
例:select prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no
10. CHECKSUM 返回在表的行上或在表达式列表上计算的校验值,用于生成哈希索引。
11. STDEV 返回给定表达式中所有值的统计标准偏差。
例:select stdev(prd_no) from sales
12. STDEVP 返回给定表达式中的所有值的填充统计标准偏差。
例:select stdevp(prd_no) from sales
13. VAR 返回给定表达式中所有值的统计方差。
例:select var(prd_no) from sales
14. VARP 返回给定表达式中所有值的填充的统计方差。
例:select varp(prd_no) from sales
15.统计各部门的总薪水,平均薪水和部门的详细情况
SELECTsum(salary) OVER (PARTITION by depname),avg(salary) OVER (PARTITION bydepname),*from empsalary;
16.统计人员在所在部门的薪水排名情况
select rank()over(partition by depname ORDER BY salary),* from empsalary;
select row_number() over(PARTITION by depnameorder by salary desc),* from empsalary;
为每一行指派一个唯一的编号。使用此函数替代 NUMBER 函数。
select rank() over(partition by depname orderby salary desc),* from empsalary;
计算一个值在一组值中的排位。如果出现并列的情况,RANK 函数会在排名序列中留出空位。
select dense_rank() over(partition by depnameorder by salary desc),* from empsalary;
计算值在分区中的排位。对于并列的值,DENSE_RANK 函数不会在排名序列中留出空位。
select percent_rank() over(partition bydepname order by salary desc),* from empsalary;
根据rank()排序的第X行减去1除于组中总记录-1.
select cume_dist() over(partition by depnameorder by salary desc),* from empsalary;
计算某个值在一组行中的相对位置。
select ntile(3) over (partition by depnameorder by salary desc),* from empsalary;
从1到当前值,除以分组的的数量,尽可能使分布平均
select lag(salary,2,NULL) over(partition bydepname order by salary desc),* from empsalary;
偏移量函数,取滞后值,如lag(column_name,2,0)表示字段偏移量为2,没有则用default值代替,这里是0,不写默认是null
select first_value(salary) over(partition bydepname order by salary desc),* from empsalary;
从窗口第一行返回值。
注释
FIRST_VALUE函数使您不用自连接就能(依照某种排序)选择表中的第一个值。如果您希望使用第一个值作为计算的基准,此函数很有价值。
FIRST_VALUE函数从窗口提取第一个记录。然后针对第一个记录计算 expression 并返回结果。
如果指定 IGNORE NULL,则返回 expression 的第一个非 NULL 值。如果指定 RESPECT NULLS(缺省值),则会返回第一个值,无论其是否为 NULL。
FIRST_VALUE 函数与其它大多数集合函数的不同之处在于:该函数只能与窗口说明一起使用。
select last_value(salary) over (partition bydepname ),* from empsalary;
从窗口最后一行返回值。
注释
LAST_VALUE函数使您不用自连接就能(依照某种排序)选择表中的最后一个值。如果您希望使用最后一个值作为计算的基准,此函数很有价值。
LAST_VALUE函数在完成 ORDER BY 后从分区提取最后一个记录。然后针对最后一个记录计算 expression 并返回结果。
如果指定 IGNORE NULL,则返回 expression 的最后一个非 NULL 值。如果指定 RESPECT NULLS(缺省值),则会返回最后一个值,无论其是否为 NULL。
LAST_VALUE函数与其它大多数集合函数的不同之处在于:该函数只能与窗口说明一起使用。
select nth_value(salary,2) over (partition by depname order by salary),* fromempsalary;
返回窗口框架中的指定值,如nth_value(salary,2),则表示返回字段salary的第二个窗口函数值
主意last_value()和first_value()函数的运用,当使用order by 只会对当前行排序。例如:
修改为order by的范围: