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

目录前言1.格式转换1.1格式转换符显示转换1.2利用数据类型显示转换1.3格式转换函数显示转换1.4转换案例2.数学计算2.1数学运算操作符2.2数学运算函数3.逻辑计算3.1逻辑操作...

目录

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

3.逻辑计算

3.1 逻辑操作符

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

AND
OR
NOT

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)4num_nulls(VARIADIC “any”)返回空参数的数量num_nulls(0, NULL, 1 ,2 ,3)1

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

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

5.时间与日期函数

5.1时间类操作符

操作符例子结果+date ‘2001-09-28’ + integer ‘7’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 daysage(timestamp)interval从current_date（在午夜）减去age(timestamp ‘1957-06-13’)，假如今日为2022-06-1465 years 1 dayclock_timestamp()timestamp with time zone当前日期和时间（在语句执行期间变化）clock_timestamp()2022-06-14 19:06:54.034672+08current_datedate当前日期current_date2022-06-14current_timetime with time zone当前时间（一天中的时间），带时区current_time19:11:04.336139+08current_timestamptimestamp with time zone当前日期和时间（当前事务开始时），带时区current_timestamp2022-06-14 19:11:57.83455+08date_part(text, timestamp)double precision获得子域（等价于extract）date_part(‘hour’, timestamp ‘2022-06-14 20:38:40’)20date_part(text, interval)double precision获得子域（等价于extract）date_part(‘month’, interval ‘2 years 3 months’)3date_trunc(text, timestamp)timestamp截断到指定精度date_trunc(‘hour’, timestamp ‘2022-06-14 20:38:40’)2022-06-14 20:00:00date_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+08date_trunc(text, interval)interval截断到指定精度date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’)2 days 03:00:00extract(field from timestamp)double precision获得子域extract(MINUTE from timestamp ‘2022-06-14 20:38:40’)38extract(field from interval)double precision获得子域extract(month from interval ‘2 years 3 months’)3isfinite(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 daysjustify_hours(interval)interval调整间隔这样24小时时间周期可以表示为日justify_hours(interval ‘27 hours’)1 day 03:00:00justify_interval(interval)interval使用justify_days和justify_hours调整间隔，使用额外的符号调整justify_interval(interval ‘1 mon -1 hour’)29 days 23:00:00localtimetime当前时间（一天中的时间），不带时区localtime19:21:14.958www.cppcns.com286localtimestamptimestamp当前日期和时间（当前事务的开始），不带时区LOCALTIMESTAMP2022-07-22 19:23:54.073462make_date(year int, month int, day int)date从年、月、日域创建日期make_date(2022, 7, 15)2022-07-15make_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 daysmake_time(hour int, min int, sec double precision)time从时、分、秒域创建时间make_time(8, 15, 23.5)08:15:23.5make_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.5make_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+08now()timestamp with time zone当前日期和时间（当前事务的开始），带时区now()2022-07-22 19:28:15.804042+08statement_timestamp()timestamp with time zone当前日期和时间（当前语句的开始），在一个事务的第一条命令期间返回值与CURRENT_TIMESTAMP相同statement_timestamp()2022-07-22 19:31:35.75589+08timeofday()text当前日期和时间（像clock_timestamp，但是作为一个text字符串）timeofday()Fri Jul 22 19:35:19.000959 2022 CSTtransaction_timestamp()timestamp with time zone当前日期和时间（当前事务的开始）；等同于CURRENT_TIMESTAMPtransaction_timestamp()2022-07-22 19:34:02.369665+08to_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]])2array_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)3array_lower(anyarray, int)int返回被请求的数组维度的下界array_lower(‘[0:2]={1,2,3}’::int[], 1)0array_position(anyarray, anyelement [, int])int返回在该数组中从第三个参数指定的元素开始或者第一个元素开始（数组必须是一维的）、第二个参数的第一次出现的下标array_position(ARRAY[‘sun’,‘mon’,‘tue’,‘wed’,‘thu’,‘fri’,‘sat’], ‘mon’)2array_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)4cardinality(anyarray)int返回数组中元素的总数，如果数组为空则返回0cardinality(ARRAY[[1,2],[3,4]])4string_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.1upper(anyrange)范围的元素类型范围的上界upper(numrange(1.1,2.2))2.2isempty(anyrange)boolean范围为空？isempty(numrange(1.1,2.2))falselower_inc(anyrange)boolean下界包含在内？lower_inc(numrange(1.1,2.2))trueupper_inc(anyrange)boolean上界包含在内？upper_inc(numrange(1.1,2.2))falselower_inf(anyrange)boolean下界无限？lower_inf(‘(,)’::daterange)trueupper_inf(anyrange)boolean上界无限？upper_inf(‘(,)’::daterange)truerange_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_andjson_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_andjson_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 到 1cume_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 WHEN condition THEN result [WHEN …] [ELSE result] ENDCASE子句可以用于任何表达式可以出现的地方。每一个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’ENDcoalesceCOALESCE(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带间隙的当前行排名； 与该行的第一个同等行的row_number相同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返回在窗口帧中第一行上计算的valuelast_value(value any)和value类型相同返回在窗口帧中最后一行上计算的valuenth_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函数使用内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！