postgresql整理

数据类型:

整型： 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,+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,fill text])	text	通过填充字符fill(缺省时为空白)，把string填充为长度length。如果string已经比length长则将其截断。	rpad('hi','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,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,4,5}
array_append(anyarray,anyelement)	anyarray	向一个数组末尾附加一个元素	array_append(ARRAY[1,2],3)	{1,3}
array_prepend(anyelement,anyarray)	anyarray	向一个数组开头附加一个元素	array_prepend(1,ARRAY[2,3])	{1,3}
array_dims(anyarray)	text	返回一个数组维数的文本表示	array_dims(ARRAY[[1,[4,6]])	[1:2][1:3]
array_lower(anyarray,int)	int	返回指定的数组维数的下界	array_lower(array_prepend(0,ARRAY[1,3]),1)	0
array_upper(anyarray,int)	int	返回指定数组维数的上界	array_upper(ARRAY[1,4],1)	4
array_to_string(anyarray,text)	text	使用提供的分隔符连接数组元素	array_to_string(ARRAY[1,'~^~')	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,NULL) over(partition bydepname order by salary desc),* from empsalary;

偏移量函数，取滞后值,如lag(column_name,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的范围：

原文链接：https://www.f2er.com/postgresql/195219.html

注释

注释

猜你在找的Postgre SQL相关文章