返回
首页
业界
电商
创业
访谈
手机
移动
报告
运营
建站
互联网+
系统
教程
搜索

Postgresql常用函数及使用方法大全(看一篇就够了)

2022-11-17 09:20:30
目录
前言1.格式转换1.1 格式转换符显示转换1.2 利用数据类型显示转换1.3 格式转换函数显示转换1.4 转换案例2.数学计算2.1 数学运算操作符2.2 数学运算函数3.逻辑计算3.1 逻辑操作符3.2 比较操作符3.3 比较谓词3.4 比较函数4.字符串及相关匹配函数5.时间与日期函数5.1时间类操作符5.2 时间、日期类函数6.数组函数6.1 数组操作符6.2 数组函数7.范围函数7.1 范围操作符7.2 范围函数8.聚集函数8.1 常用函数8.2 统计类函数8.3 有序集聚集函数8.4 有序数据集8.5 假想集聚集函数(排序)8.6 分组操作9.条件类函数10.窗口函数11.查看内部所有函数总结

前言

写在前面，当我们在用postgresql数据库进行数据取数时，难免会遇到一些数据处理上的场景，本文会针对一些常用的内置函数进行总结。

-- 查看postgresql的版本(以下任一语句都可以实现)
select version();
show server_version;

1.格式转换

1.1>

利用双冒号可以直接进行格式转换，语法如下：

字段名或数值::数据类型

例子如下：

-- 将文本'123'转为int8类型
SELECT
    '123' :: int8 num;
-- 将文本类型字段t1转为int8类型
SELECT
    t1 :: int8 
    from temp;

1.2>

利用数据类型进行转换，语法如下：

数据类型数值

例子：

-- 将文本'123'转为int8类型
SELECT
    int8'123' num;

1.3>

利用数据转换函数cast进行转换，语法如下：

cast(字段名或数值 as 数据类型)

例子：

-- 将文本'123'转为int8类型
SELECT
    cast('123' as int4) num;
-- 将文本字段t1转为int类型
SELECT CAST( t1 AS INT ) t1_c 
FROM
    TEMP;

1.4> 
-- 文本转整数
SELECT CAST
    ( '123' AS int4 );
-- 文本转浮点数字
SELECT CAST
    ( '123.34' AS DECIMAL );
SELECT CAST
    ( '123.34' AS NUMERIC );
-- 数字转文本
SELECT CAST
    ( 123 AS VARCHAR );--可变字符串
SELECT CAST
    ( - 123 AS CHAR ( 2 ) );-- 固定字符串，进行截断，将-123转为'-1'
SELECT CAST
    ( - 123 AS CHAR ( 6 ) );-- 固定字符串，进行空格填充，将-123转为'-123  '
SELECT CAST
    ( 124.94 AS TEXT );--可变字符串,将124.94转为'124.94'
SELECT
    to_char( 124.94, '999D9' );--将124.94转为'124.9',遵循四舍五入
SELECT
    to_char( 124.94, 'FM999.99' );--将124.94转为'124.94'
SELECT
    to_char( - 124.94, 'FM9999999.99' );--将-124.94转为'-124.94'
SELECT
    to_char( - 124.94, 'FM9999999.990' );--将-124.94转为'-124.940'
SELECT
    to_char( 124, '00000' );--左端用零补齐凑够5位，将124转为'00124'
SELECT
    to_char( 124, '99999' );--左端用空格补齐凑够5位，将124转为'  124'
SELECT
    to_char( - 124.945, 'FM999' );--只显示整数部分，遵循四舍五入
-- 时间戳(timestamp)转日期(date)
SELECT CAST
    ( now( ) AS DATE );--普通日期模式
-- 时间戳(timestamp)转文本
SELECT CAST
    ( now( ) AS TEXT );--不指定输出格式
SELECT
    to_char( now( ), 'yyyy-mm-dd' );--指定输出格式;
-- 文本转日期(date)
SELECT
    to_date( '2012-01-01', 'yyyy-mm-dd' );
-- 文本转时间戳(TIMESTAMP)
SELECT
    to_timestamp( '2012-01-01 12:02:01', 'yyyy-mm-dd HH24:MI:SS' );

2.数学计算

2.1>
操作符描述例子结果
+加2 + 35
-减2 - 3-1
*乘2 * 36
/除（整数除法截断结果）4 / 22
%模（取余）5 % 41
^指数（从左至右结合）2.0 ^ 3.08
|/平方根|/ 25.05
||/立方根||/ 27.03
!阶乘5 !120
!!阶乘（前缀操作符）!! 5120
@绝对值@ -5.05
&按位与91 & 1511
|按位或323
#按位异或17 # 520
~按位求反~1-2
<<按位左移1 << 416
>>按位右移8 >> 22

2.2>
函数返回类型描述例子结果
abs(x)和输入相同绝对值abs(-12.43)12.43
cbrt(dp)double立方根cbrt(27.0)3
ceil(dp or numeric)和输入相同不小于参数的最近的整数ceil(-42.8)-42
ceiling(dp or numeric)和输入相同不小于参数的最近的整数（ceil的别名）ceiling(-95.3)-95
degrees(dp)dp把弧度转为角度degrees(0.5)28.6478897565412
div(y numeric, x numeric)numericy/x的整数商div(9,4)2
exp(dp or numeric)和输入相同指数exp(1.0)2.71828182845905
floor(dp or numeric)和输入相同不大于参数的最近的整数floor(-42.8)-43
ln(dp or numeric)和输入相同自然对数ln(2.0)0.693147180559945
log(dp or numeric)和输入相同以10为底的对数log(100.0)2
log10(dp or numeric)和输入相同以10为底的对数log10(100.0)2
log(b numeric, x numeric)numeric以b为底的对数log(2.0, 64.0)6.0000000000
mod(y, x)和参数类型相同y/x的余数mod(9,4)1
pi()dp“π”常数pi()3.14159265358979
power(a dp, b dp)dp求a的b次幂power(9.0, 3.0)729
power(a numeric, b numeric)numeric求a的b次幂power(9.0, 3.0)729
radians(dp)dp把角度转为弧度radians(45.0)0.785398163397448
round(dp or numeric)和输入相同圆整为最接近的整数round(42.4)42
round(v numeric, s int)numeric圆整为s位小数数字round(42.4382, 2)42.44
scale(numeric)integer参数的精度（小数点后的位数）scale(8.41)2
sign(dp or numeric)和输入相同参数的符号（-1, 0, +1）sign(-8.4)-1
sqrt(dp or numeric)和输入相同平方根sqrt(2.0)1.4142135623731
trunc(dp or numeric)和输入相同截断（向零靠近）trunc(42.8)42
trunc(v numeric, s int)numeric截断为s位小数位置的数字trunc(42.4382, 2)42.43

3.逻辑计算

3.1>

postgresql中的逻辑操作符，有以下三种：

    ANDORNOT

    3.2>
    操作符描述
    <小于
    >大于
    <=小于等于
    >=大于等于
    =等于
    <> or !=不等于

    !=操作符在分析器阶段被转换成<>

    3.3>
    谓词描述
    a BETWEEN x AND y在x和y之间
    a NOT BETWEEN x AND y不在x和y之间
    a BETWEEN SYMMETRIC x AND y在对比较值排序后位于x和y之间
    a NOT BETWEEN SYMMETRIC x AND y在对比较值排序后不位于x和y之间
    a IS DISTINCT FROM b不等于，空值被当做一个普通值
    a IS NOT DISTINCT FROM b等于，空值被当做一个普通值
    expression IS NULL是空值
    expression IS NOT NULL不是空值
    expression ISNULL是空值（非标准语法）
    expression NOTNULL不是空值（非标准语法）
    boolean_expression IS TRUE为真
    boolean_expression IS NOT TRUE为假或未知
    boolean_expression IS FALSE为假
    boolean_expression IS NOT FALSE为真或者未知
    boolean_expression IS UNKNOWN值为未知
    boolean_expression IS NOT UNKNOWN为真或者为假

    3.4>
    函数描述例子例子结果
    num_nonnulls(VARIADIC “any”)返回非空参数的数量num_nonnulls(0, NULL, 1 ,2 ,3)4
    num_nulls(VARIADIC “any”)返回空参数的数量num_nulls(0, NULL, 1 ,2 ,3)1

    4.字符串及相关匹配函数

    函数返回类型描述例子结果
    string>text串接‘Hello’ || ‘Word’‘HelloWord’
    string || non-string or non-string || stringtext使用一个非字符串输入的串接'Value: ’ || 42Value: 42
    bit_length(string)int串中的位数bit_length(‘Hello’)40
    char_length(string) or character_length(string)int串中字符数char_length(‘Hello’)4
    lower(string)text将字符串转换为小写形式lower(‘Hello’)hello
    overlay(string placing string from int [for int])text替换子串,for后面是指替换的位数overlay(‘Hexxx,word’ placing ‘llo’ from 3 for 4)Helloword
    position(substring in string)int定位指定子串位置，可利用值是否大于0来判断是否包含子串position(‘lo’ in ‘hello’)4
    substring(string [from int] [for int])text提取子串substring(‘hello’ from 1 for 3)hel
    substring(string from pattern)text提取匹配POSIX正则表达式的子串substring(‘hello’ from ‘^…’)hel
    substr(string, from [, count])text提取子串substr(‘Hello’, 1, 3)hel
    trim([leading | trailing | both] [characters] from string)text从string的开头、结尾或者两端（both是默认值）移除只包含characters（默认是一个空格）中字符的最长字符串trim(both ‘Hes’ from ‘sHehelloeHs’)hello
    trim([leading | trailing | both] [from] string [, characters] )texttrim()的非标准版本trim(both from ‘hhHellohh’, ‘h’) 或trim(‘hhHellohh’, ‘h’)Tom
    upper(string)text将字符串转换成大写形式upper(‘hello’)HELLO
    concat(str “any” [, str “any” [, …] ])text串接所有参数的文本表示。NULL 参数被忽略。concat(‘abcde’, 2, NULL, 22)abcde222
    concat_ws(sep text, str “any” [, str “any” [, …] ])text将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略。concat_ws(‘,’, ‘abcde’, 2, NULL, 22)abcde,2,22
    left(str text, n int)text返回字符串中的前n个字符。当n为负时，将返回除了最后|n|个字符之外的所有字符。left(‘abcde’, 2)ab
    length(string)intstring中的字符数length(‘hello’)5
    length(string bytea, encoding name )intstring在给定编码中的字符数。string必须在这个编码中有效。length(‘hello’, ‘UTF8’)5
    lpad(string text, length int [, fill text])text将string通过前置字符fill（默认是一个空格）填充到长度length。如果string已经长于length，则它被（从右边）截断。lpad(‘hi’, 5, ‘ab’)abahi
    ltrim(string text [, characters text])text从string的开头删除最长的只包含characters（默认是一个空格）的串ltrim(‘zzzytest’, ‘xyz’)test
    regexp_match(string text, pattern text [, flags text])text[]返回一个POSIX正则表达式与string的第一个匹配得到的子串。regexp_match(‘foobarbequetarz’, ‘(foo)(bar)’)一行：{foo,bar}
    regexp_matches(string text, pattern text [, flags text])setof text[]返回一个POSIX正则表达式与string匹配得到的子串regexp_matches(‘foobarbequetarz’, ‘.ar’, ‘g’)两行：{bar} {tar}
    regexp_replace(string text, pattern text, replacement text [, flags text])text替换匹配一个POSIX正则表达式的子串。regexp_replace(‘Hello’, ‘l+.’, ‘r’)Her
    regexp_split_to_array(string text, pattern text [, flags text ])text[]使用一个POSIX正则表达式作为分隔符划分string。regexp_split_to_array(‘hello world’, ‘\s+’)一行：{hello,world}
    regexp_split_to_table(string text, pattern text [, flags text]) setoftext使用一个POSIX正则表达式作为分隔符划分string。regexp_split_to_table(‘hello world’, ‘\s+’)两行：hello world
    repeat(string text, number int)text重复string指定的number次repeat(‘he’, 3)hehehe
    replace(string text, from text, to text)text将string中出现的所有子串from替换为子串toreplace(‘hello’, ‘ello’, ‘is’)his
    reverse(str)text返回反转的字符串reverse(‘abcde’)edcba
    right(str text, n int)text返回字符串中的最后n个字符。如果n为负，返回除最前面的|n|个字符外的所有字符。right(‘abcde’, 2)de
    rpad(string text, length int [, fill text])text将string通过增加字符fill（默认为一个空格）填充到长度length。如果string已经长于length则它会被截断。rpad(‘hi’, 5, ‘xy’)hixyx
    rtrim(string text [, characters text])text从string的结尾删除最长的只包含characters（默认是一个空格）的串rtrim(‘testxxzx’, ‘xyz’)test
    split_part(string text, delimiter text, field int)text按delimiter划分string并返回给定域（从1开始计算）split_part(‘you!hello!world!’, ‘!’, 2)hello
    strpos(string, substring)int指定子串的位置（和position(substring in string)相同，但是注意相反的参数顺序）strpos(‘hello’, ‘o’)5
    starts_with(string, prefix)bool如果string以prefix开始则返回真。starts_with(‘alphabet’, ‘alph’)t

    5.时间与日期函数

    5.1时间类操作符

    操作符例子结果
    +date>date ‘2001-10-05’
    +date ‘2001-09-28’ + interval ‘1 hour’timestamp ‘2001-09-28 01:00:00’
    +date ‘2001-09-28’ + time ‘03:00’timestamp ‘2001-09-28 03:00:00’
    +interval ‘1 day’ + interval ‘1 hour’interval ‘1 day 01:00:00’
    +timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’timestamp ‘2001-09-29 00:00:00’
    +time ‘01:00’ + interval ‘3 hours’time ‘04:00:00’
    -- interval ‘23 hours’interval ‘-23:00:00’
    -date ‘2001-10-01’ - date ‘2001-09-28’integer ‘3’ (days)
    -date ‘2001-10-01’ - integer ‘7’date ‘2001-09-24’
    -date ‘2001-09-28’ - interval ‘1 hour’timestamp ‘2001-09-27 23:00:00’
    -time ‘05:00’ - time ‘03:00’interval ‘02:00:00’
    -time ‘05:00’ - interval ‘2 hours’time ‘03:00:00’
    -timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’timestamp ‘2001-09-28 00:00:00’
    -interval ‘1 day’ - interval ‘1 hour’interval ‘1 day -01:00:00’
    -timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’interval ‘1 day 15:00:00’
    *900 * interval ‘1 second’interval ‘00:15:00’
    *21 * interval ‘1 day’interval ‘21 days’
    *double precision ‘3.5’ * interval ‘1 hour’interval ‘03:30:00’
    /interval ‘1 hour’ / double precision ‘1.5’interval ‘00:40:00’

    5.2>
    函数返回类型描述例子结果
    age(timestamp, timestamp)interval减去参数，生成一个使用年、月（而不是只用日）的“符号化”的结果age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’)43 years 9 mons 27 days
    age(timestamp)interval从current_date（在午夜）减去age(timestamp ‘1957-06-13’)，假如今日为2022-06-1465 years 1 day
    clock_timestamp()timestamp with time zone当前日期和时间（在语句执行期间变化）clock_timestamp()2022-06-14 19:06:54.034672+08
    current_datedate当前日期current_date2022-06-14
    current_timetime with time zone当前时间（一天中的时间），带时区current_time19:11:04.336139+08
    current_timestamptimestamp with time zone当前日期和时间（当前事务开始时），带时区current_timestamp2022-06-14 19:11:57.83455+08
    date_part(text, timestamp)double precision获得子域（等价于extract）date_part(‘hour’, timestamp ‘2022-06-14 20:38:40’)20
    date_part(text, interval)double precision获得子域（等价于extract）date_part(‘month’, interval ‘2 years 3 months’)3
    date_trunc(text, timestamp)timestamp截断到指定精度date_trunc(‘hour’, timestamp ‘2022-06-14 20:38:40’)2022-06-14 20:00:00
    date_trunc(text, timestamp with time zone, text)timestamp with time zone在指定的时区截断到指定的精度ddate_trunc(‘day’, timestamptz ‘2022-06-14 20:38:40+00’, ‘Australia/Sydney’)2022-06-14 22:00:00+08
    date_trunc(text, interval)interval截断到指定精度date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’)2 days 03:00:00
    extract(field from timestamp)double precision获得子域extract(MINUTE from timestamp ‘2022-06-14 20:38:40’)38
    extract(field from interval)double precision获得子域extract(month from interval ‘2 years 3 months’)3
    isfinite(date)boolean测试有限日期（不是+/-无限）isfinite(date ‘2022-06-14’)true（实际缩写为t）
    isfinite(timestamp)boolean测试有限时间戳（不是+/-无限）isfinite(timestamp ‘2022-06-14 21:28:30’)true（实际缩写为t）
    isfinite(interval)boolean测试有限间隔isfinite(interval ‘2 minutes’)true（实际缩写为t）
    justify_days(interval)interval调整间隔这样30天时间周期可以表示为月justify_days(interval ‘35 days’)1 mon 5 days
    justify_hours(interval)interval调整间隔这样24小时时间周期可以表示为日justify_hours(interval ‘27 hours’)1 day 03:00:00
    justify_interval(interval)interval使用justify_days和justify_hours调整间隔，使用额外的符号调整justify_interval(interval ‘1 mon -1 hour’)29 days 23:00:00
    localtimetime当前时间（一天中的时间），不带时区localtime19:21:14.958286
    localtimestamptimestamp当前日期和时间（当前事务的开始），不带时区LOCALTIMESTAMP2022-07-22 19:23:54.073462
    make_date(year int, month int, day int)date从年、月、日域创建日期make_date(2022, 7, 15)2022-07-15
    make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)interval从年、月、周、日、时、分、秒域创建intervalmake_interval(days => 10)10 days
    make_time(hour int, min int, sec double precision)time从时、分、秒域创建时间make_time(8, 15, 23.5)08:15:23.5
    make_timestamp(year int, month int, day int, hour int, min int, sec double precision)timestamp从年、月、日、时、分、秒域创建时间戳make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5
    make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])timestamp with time zone从年、月、日、时、分、秒域创建带时区的时间戳。如果没有指定timezone， 则使用当前时区。make_timestamptz(2022, 6, 14, 19, 30, 50.5)2022-06-14 19:30:50.5+08
    now()timestamp with time zone当前日期和时间（当前事务的开始），带时区now()2022-07-22 19:28:15.804042+08
    statement_timestamp()timestamp with time zone当前日期和时间（当前语句的开始），在一个事务的第一条命令期间返回值与CURRENT_TIMESTAMP相同statement_timestamp()2022-07-22 19:31:35.75589+08
    timeofday()text当前日期和时间（像clock_timestamp，但是作为一个text字符串）timeofday()Fri Jul 22 19:35:19.000959 2022 CST
    transaction_timestamp()timestamp with time zone当前日期和时间（当前事务的开始）；等同于CURRENT_TIMESTAMPtransaction_timestamp()2022-07-22 19:34:02.369665+08
    to_timestamp(double precision)timestamp with time zone把 Unix 时间（从 1970-01-01 00:00:00+00 开始的秒）转换成 timestampto_timestamp(1655211000)2022-06-14 20:50:00+08

    6.数组函数

    6.1>
    操作符描述例子结果
    =等于ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]t
    <>不等于ARRAY[1,2,3] <> ARRAY[1,2,4]t
    <小于ARRAY[1,2,3] < ARRAY[1,2,4]t
    >大于ARRAY[1,4,3] > ARRAY[1,2,4]t
    <=小于等于ARRAY[1,2,3] <= ARRAY[1,2,3]t
    >=大于等于ARRAY[1,4,3] >= ARRAY[1,4,3]t
    @>包含ARRAY[1,4,3] @> ARRAY[3,1,3]t
    <@被包含ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]t
    &&重叠（具有公共元素）ARRAY[1,4,3] && ARRAY[2,1]t
    ||数组和数组串接ARRAY[1,2,3] || ARRAY[4,5,6]{1,2,3,4,5,6}
    ||数组和数组串接ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]{{1,2,3},{4,5,6},{7,8,9}}
    ||元素到数组串接3 || ARRAY[4,5,6]{3,4,5,6}
    ||数组到元素串接ARRAY[4,5,6] || 7{4,5,6,7}

    6.2>
    函数返回类型描述例子结果
    array_append(anyarray, anyelement)anyarray向一个数组的末端追加一个元素array_append(ARRAY[1,2], 3){1,2,3}
    array_cat(anyarray, anyarray)anyarray连接两个数组array_cat(ARRAY[1,2,3], ARRAY[4,5]){1,2,3,4,5}
    array_ndims(anyarray)int返回数组的维度数array_ndims(ARRAY[[1,2,3], [4,5,6]])2
    array_dims(anyarray)text返回数组的维度的文本表示array_dims(ARRAY[[1,2,3], [4,5,6]])[1:2][1:3]
    array_fill(anyelement, int[], [, int[]])anyarray返回一个用提供的值和维度初始化好的数组，可以选择下界不为1array_fill(6, ARRAY[3], ARRAY[5])[5:7]={6,6,6}
    array_length(anyarray, int)int返回被请求的数组维度的长度array_length(array[1,2,3], 1)3
    array_lower(anyarray, int)int返回被请求的数组维度的下界array_lower(‘[0:2]={1,2,3}’::int[], 1)0
    array_position(anyarray, anyelement [, int])int返回在该数组中从第三个参数指定的元素开始或者第一个元素开始（数组必须是一维的）、第二个参数的第一次出现的下标array_position(ARRAY[‘sun’,‘mon’,‘tue’,‘wed’,‘thu’,‘fri’,‘sat’], ‘mon’)2
    array_positions(anyarray, anyelement)int[]返回在第一个参数给定的数组（数组必须是一维的）中，第二个参数所有出现位置的下标组成的数组array_positions(ARRAY[‘A’,‘A’,‘B’,‘A’], ‘A’){1,2,4}
    array_prepend(anyelement, anyarray)anyarray向一个数组的首部追加一个元素array_prepend(1, ARRAY[2,3]){1,2,3}
    array_remove(anyarray, anyelement)anyarray从数组中移除所有等于给定值的所有元素（数组必须是一维的）array_remove(ARRAY[1,2,3,2], 2){1,3}
    array_replace(anyarray, anyelement, anyelement)anyarray将每一个等于给定值的数组元素替换成一个新值array_replace(ARRAY[1,2,5,4], 5, 3){1,2,3,4}
    array_to_string(anyarray, text [, text])text使用提供的定界符和可选的空串连接数组元素array_to_string(ARRAY[1, 2, 3, NULL, 5], ‘,’, ‘*’)‘1,2,3,*,5’
    array_upper(anyarray, int)int返回被请求的数组维度的上界array_upper(ARRAY[1,8,3,7], 1)4
    cardinality(anyarray)int返回数组中元素的总数，如果数组为空则返回0cardinality(ARRAY[[1,2],[3,4]])4
    string_to_array(text, text [, text])text[]使用提供的定界符和可选的空串将字符串划分成数组元素string_to_array(‘a-b-c-d-e-g-’, ‘-’, ‘’){a,b,c,d,e,g,NULL}
    unnest(anyarray)setof anyelement将一个数组扩展成一组行unnest(ARRAY[1,2])2行：1 2

    7.范围函数

    7.1>
    操作符描述例子结果
    =等于int4range(1,5) = ‘[1,4]’::int4ranget
    <>不等于numrange(1.1,2.2) <> numrange(1.1,2.3)t
    <小于int4range(1,10) < int4range(2,3)t
    >大于int4range(1,10) > int4range(1,5)t
    <=小于等于numrange(1.1,2.2) <= numrange(1.1,2.2)t
    >=大于等于numrange(1.1,2.2) >= numrange(1.1,2.0)t
    @>包含范围int4range(2,4) @> int4range(2,3)t
    @>包含元素‘[2011-01-01,2011-03-01)’::tsrange @> ‘2011-01-10’::timestampt
    <@范围被包含int4range(2,4) <@ int4range(1,7)t
    <@元素被包含42 <@ int4range(1,7)f
    &&重叠（有公共点）int8range(3,7) && int8range(4,12)t
    <<严格左部int8range(1,10) << int8range(100,110)t
    >>严格右部int8range(50,60) >> int8range(20,30)t
    &<不超过右部int8range(1,20) &< int8range(18,20) t 
    &>不超过左部int8range(7,20) &> int8range(5,10)t
    -|-相邻numrange(1.1,2.2) -|- numrange(2.2,3.3)t
    +并numrange(5,15) + numrange(10,20)[5,20)
    *交int8range(5,15) * int8range(10,20)[10,15)
    -差int8range(5,15) - int8range(10,20)[5,10)

    7.2>
    函数返回类型描述例子结果
    lower(anyrange)范围的元素类型范围的下界lower(numrange(1.1,2.2))1.1
    upper(anyrange)范围的元素类型范围的上界upper(numrange(1.1,2.2))2.2
    isempty(anyrange)boolean范围为空？isempty(numrange(1.1,2.2))false
    lower_inc(anyrange)boolean下界包含在内？lower_inc(numrange(1.1,2.2))true
    upper_inc(anyrange)boolean上界包含在内？upper_inc(numrange(1.1,2.2))false
    lower_inf(anyrange)boolean下界无限？lower_inf(‘(,)’::daterange)true
    upper_inf(anyrange)boolean上界无限？upper_inf(‘(,)’::daterange)true
    range_merge(anyrange, anyrange)anyrange包含两个给定范围的最小范围range_merge(‘[1,2)’::int4range, ‘[3,4)’::int4range)[1,4)

    8.聚集函数

    8.1>
    函数参数类型返回类型部分模式描述
    array_agg(expression)任何非数组类型参数类型的数组No输入值（包括空）被连接到一个数组
    array_agg(expression)任意数组类型和参数数据类型相同No输入数组被串接到一个更高维度的数组中 （输入必须都具有相同的维度并且不能为空或者 NULL）
    avg(expression)smallint, int, bigint、real、double precision、numeric或interval对于任何整数类型参数是numeric，对于一个浮点参数是double precision，否则和参数数据类型相同Yes所有非空输入值的平均值（算术平均）
    bit_and(expression)smallint、int、bigint或bit与参数数据类型相同Yes所有非空输入值的按位与，如果没有非空值则结果是空值
    bit_or(expression)smallint, int, bigint, or bit与参数数据类型相同Yes所有非空输入值的按位或，如果没有非空值则结果是空值
    bool_and(expression)boolboolYes如果所有输入值为真则结果为真，否则为假
    bool_or(expression)boolboolYes至少一个输入值为真时结果为真，否则为假
    count(*) bigintYes输入的行数
    count(expression)anybigintYesexpression值非空的输入行的数目
    every(expression)boolboolYes等价于bool_and
    json_agg(expression)anyjsonNo将值，包含空值，聚集成一个 JSON 数组
    jsonb_agg(expression)anyjsonbNo把值，包含空值，聚合成一个 JSON 数组
    json_object_agg(name, value)(any, any)jsonNo将名字/值对聚集成一个 JSON 对象，值可以为空，但不能是名字。
    jsonb_object_agg(name, value)(any, any)jsonbNo把名字/值对聚合成一个 JSON 对象，值可以为空，但不能是名字。
    max(expression)任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组与参数数据类型相同Yes所有非空输入值中expression的最大值
    min(expression)任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组与参数数据类型相同Yes所有非空输入值中expression的最小值
    string_agg(expression, delimiter)(text, text) 或 (bytea, bytea)与参数数据类型相同No非空输入值连接成一个串，用定界符分隔
    sum(expression)smallint、int、 bigint、real、double precision、numeric、 interval或money对smallint或int参数是bigint，对bigint参数是numeric，否则和参数数据类型相同Yes所有非空输入值的expression的和
    xmlagg(expression)xmlxmlNo连接非空XML值

    8.2>
    函数参数类型返回类型部分模式描述
    array_agg(expression)任何非数组类型参数类型的数组No输入值（包括空）被连接到一个数组
    array_agg(expression)任意数组类型和参数数据类型相同No输入数组被串接到一个更高维度的数组中 （输入必须都具有相同的维度并且不能为空或者 NULL）
    avg(expression)smallint, int, bigint、real、double precision、numeric或interval对于任何整数类型参数是numeric，对于一个浮点参数是double precision，否则和参数数据类型相同Yes所有非空输入值的平均值（算术平均）
    bit_and(expression)smallint、int、bigint或bit与参数数据类型相同Yes所有非空输入值的按位与，如果没有非空值则结果是空值
    bit_or(expression)smallint, int, bigint, or bit与参数数据类型相同Yes所有非空输入值的按位或，如果没有非空值则结果是空值
    bool_and(expression)boolboolYes如果所有输入值为真则结果为真，否则为假
    bool_or(expression)boolboolYes至少一个输入值为真时结果为真，否则为假
    count(*) bigintYes输入的行数
    count(expression)anybigintYesexpression值非空的输入行的数目
    every(expression)boolboolYes等价于bool_and
    json_agg(expression)anyjsonNo将值，包含空值，聚集成一个 JSON 数组
    jsonb_agg(expression)anyjsonbNo把值，包含空值，聚合成一个 JSON 数组
    json_object_agg(name, value)(any, any)jsonNo将名字/值对聚集成一个 JSON 对象，值可以为空，但不能是名字。
    jsonb_object_agg(name, value)(any, any)jsonbNo把名字/值对聚合成一个 JSON 对象，值可以为空，但不能是名字。
    max(expression)任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组与参数数据类型相同Yes所有非空输入值中expression的最大值
    min(expression)任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组与参数数据类型相同Yes所有非空输入值中expression的最小值
    string_agg(expression, delimiter)(text, text) 或 (bytea, bytea)与参数数据类型相同No非空输入值连接成一个串，用定界符分隔
    sum(expression)smallint、int、 bigint、real、double precision、numeric、 interval或money对smallint或int参数是bigint，对bigint参数是numeric，否则和参数数据类型相同Yes所有非空输入值的expression的和
    xmlagg(expression)xmlxmlNo连接非空XML值

    8.3>
    函数参数类型返回类型部分模式描述
    corr(Y, X)double precisiondouble precisionYes相关系数
    covar_pop(Y, X)double precisiondouble precisionYes总体协方差
    covar_samp(Y, X)double precisiondouble precisionYes样本协方差
    regr_avgx(Y, X)double precisiondouble precisionYes自变量的平均值 （sum(X)/N）
    regr_avgy(Y, X)double precisiondouble precisionYes因变量的平均值 （sum(Y)/N）
    regr_count(Y, X)double precisionbigintYes两个表达式都不为空的输入行的数目
    regr_intercept(Y, X)double precisiondouble precisionYes由（X, Y）对决定的最小二乘拟合的线性方程的 y截距
    regr_r2(Y, X)double precisiondouble precisionYes相关系数的平方
    regr_slope(Y, X)double precisiondouble precisionYes由（X, Y）对决定的最小二乘拟合的线性方程的斜率
    regr_sxx(Y, X)double precisiondouble precisionYessum(X^2) - sum(X)^2/N（自变量的“平方和”）
    regr_sxy(Y, X)double precisiondouble precisionYessum(X*Y) - sum(X) * sum(Y)/N（自变量乘以因变量的“积之合”）
    regr_syy(Y, X)double precisiondouble precisionYessum(Y^2) - sum(Y)^2/N（因变量的“平方和”）
    stddev(expression)smallint、int、 bigint、real、double precision或numeric浮点参数为double precision，否则为numericYesstddev_samp的历史别名
    stddev_pop(expression)smallint、int、 bigint、real、double precision或numeric浮点参数为double precision，否则为numericYes输入值的总体标准偏差
    stddev_samp(expression)smallint、int、 bigint、real、double precision或numeric浮点参数为double precision，否则为numericYes输入值的样本标准偏差
    variance(expression)smallint、int、 bigint、real、double precision或numeric浮点参数为double precision，否则为numericYesvar_samp的历史别名
    var_pop(expression)smallint、int、 bigint、real、double precision或numeric浮点参数为double precision，否则为numericYes输入值的总体方差（总体标准偏差的平方）
    var_samp(expression)smallint、int、 bigint、real、double precision或numeric浮点参数为double precision，否则为numericYes输入值的样本方差（样本标准偏差的平方）

    8.4>
    函数直接参数类型聚集参数类型返回类型部分模式描述
    mode() WITHIN GROUP (ORDER BY sort_expression) 任何可排序类型与排序表达式相同No返回最频繁的输入值（如果有多个频度相同的值就选第一个）
    percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)double precisiondouble precision或者interval与排序表达式相同No连续百分率：返回一个对应于排序中指定分数的值，如有必要就在相邻的输入项之间插值
    percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)double precision[]double precision或者interval排序表达式的类型的数组No多重连续百分率：返回一个匹配fractions参数形状的结果数组， 其中每一个非空元素都用对应于那个百分率的值替换
    percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)double precision一种可排序类型与排序表达式相同No离散百分率：返回第一个在排序中位置等于或者超过指定分数的输入值
    percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)double precision[]任何可排序类型排序表达式的类型的数组No多重离散百分率：返回一个匹配fractions参数形状的结果数组， 其中每一个非空元素都用对应于那个百分率的输入值替换

    8.5>
    函数直接参数类型聚集参数类型返回类型部分模式描述
    rank(args) WITHIN GROUP (ORDER BY sorted_args)VARIADIC “any”VARIADIC “any”bigintNo假想行的排名，为重复的行留下间隔
    dense_rank(args) WITHIN GROUP (ORDER BY sorted_args)VARIADIC “any”VARIADIC “any”bigintNo假想行的排名，不留间隔
    percent_rank(args) WITHIN GROUP (ORDER BY sorted_args)VARIADIC “any”VARIADIC “any”double precisionNo假想行的相对排名，范围从 0 到 1
    cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any”VARIADIC “any”double precision No假想行的相对排名，范围从 1/N 到 1

    8.6>
    函数返回类型描述
    GROUPING(args…)integer整数位掩码指示哪些参数不被包括在当前分组集合中

    使用方法举例

    WITH test_table AS (
	SELECT UNNEST( ARRAY [ '财务', '行政', '销售', '财务', '行政', '行政' ] ) AS depart,
		UNNEST ( ARRAY [ 'A', 'B', 'A', 'C', 'D', 'C' ] ) AS NAME,
		UNNEST ( ARRAY [ 200, 100, 50, 30, 200, 100 ] ) AS donate 
	) SELECT
	depart,
	NAME,
	GROUPING ( depart, NAME ),
	SUM ( donate ),
	COUNT ( donate ) 
FROM
	test_table 
GROUP BY
	ROLLUP ( depart, NAME );

    9.条件类函数

    函数语法使用说明使用例子
    caseCASE>CASE子句可以用于任何表达式可以出现的地方。每一个condition是一个返回boolean结果的表达式。如果结果为真，那么CASE表达式的结果就是符合条件的result，并且剩下的CASE表达式不会被处理。如果条件的结果不为真，那么以相同方式搜寻任何随后的WHEN子句。如果没有WHEN condition为真，那么CASE表达式的值就是在ELSE子句里的result。如果省略了ELSE子句而且没有条件为真，结果为空。CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 'other’END
    coalesceCOALESCE(value [, …])返回它的第一个非空参数的值。当且仅当所有参数都为空时才会返回空。它常用于在为显示目的检索数据时用缺省值替换空值。COALESCE(description, short_description, ‘(none)’)
    nullifNULLIF(value1, value2)当value1和value2相等时，NULLIF返回一个空值。 否则它返回value1。NULLIF(value, ‘(none)’)
    greatestGREATEST(value [, …])从一个任意的数字表达式列表里选取最大的数值。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。greatest(2,5,1)
    leastGREATEST(value [, …])从一个任意的数字表达式列表里选取最小的数值。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。least(2,6,5)

    10.窗口函数

    函数返回类型描述
    row_number()bigint当前行在其分区中的行号，从1计
    rank()bigint带间隙的当前行排名；>
    dense_rank()bigint不带间隙的当前行排名； 这个函数计数同等组
    percent_rank()double precision当前行的相对排名： (rank- 1) / (总行数 - 1)
    cume_dist()double precision累积分布：(在当前行之前或者平级的分区行数) / 分区行总数
    ntile(num_buckets integer)integer从1到参数值的整数范围，尽可能等分分区
    lag(value anyelement [, offset integer [, default anyelement ]])和value的类型相同返回value，它在分区内当前行的之前offset个位置的行上计算；如果没有这样的行，返回default替代（必须和value类型相同）。offset和default都是根据当前行计算的结果。如果忽略它们，则offset默认是1，default默认是空值
    lead(value anyelement [, offset integer [, default anyelement ]])和value类型相同返回value，它在分区内当前行的之后offset个位置的行上计算；如果没有这样的行，返回default替代（必须和value类型相同）。offset和default都是根据当前行计算的结果。如果忽略它们，则offset默认是1，default默认是空值
    first_value(value any)same type as value返回在窗口帧中第一行上计算的value
    last_value(value any)和value类型相同返回在窗口帧中最后一行上计算的value
    nth_value(value any, nth integer)和value类型相同返回在窗口帧中第nth行（行从1计数）上计算的value；没有这样的行则返回空值

    11.查看内部所有函数

    如何查看postgresql中所有的函数名称，SQL语句如下：

    -- 查看所有函数名，返回类型，及参数个数
SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
JOIN pg_type ON (pg_proc.prorettype = pg_type.oid)
-- WHERE pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = '模式')

    总结

    到此这篇关于Postgresql常用函数及使用方法详解的文章就介绍到这了,更多相关Postgresql函数使用内容请搜索易采站长站以前的文章或继续浏览下面的相关文章希望大家以后多多支持易采站长站！

相关文章 大家在看