postgresql整理

浏览数：31 / 时间：2015年06月12日

数据类型:

整型：  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的范围：

技术分享


郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： postgresqlpostgresql

相关文章

随机文章

您可能还喜欢

您可能还喜欢