postgresql整理
来源:互联网 发布:ipv6免费上网软件 编辑:程序博客网 时间:2024/06/05 15:42
数据类型:
整型: 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的范围:
- postgresql整理
- PostgreSQL 注入整理
- postgresql最强整理资料
- PostgreSQL笔记整理
- postgresql 部分函数整理
- Postgresql逻辑结构整理
- 选择 PostgreSQL 的理由(整理)
- (整理)PostgreSQL 存储过程
- PostgreSQL的SQL命令整理
- 自己整理的PostgreSQL 函数
- [整理]postgresql基本使用命令
- postgresql 使用整理:分表与触发器
- PostgreSQL的window函数应用整理
- postgresql 常用语句的整理(更新)
- postgresql编译用包分析整理
- postgreSQL
- PostgreSQL
- PostgreSQL
- UTF的字节序和BOM
- jQuery遍历对象/数组/集合
- Oracle存储过程和程序包
- AccelerometerPlayActivity
- jquerymobile之collapsible可折叠块题目内容动态显示
- postgresql整理
- IOS-路径大全
- Add Binary —— Leetcode(重做)
- 陈怡暖:市场空头回吐,银价支撑反弹
- 称砝码问题
- Java并发编程——待完善
- 让TEXT朗读起来
- IOS 手势之左右滑动
- iOS 开发小技巧