PostgreSQL 常用函数

数字函数

abs
求绝对值

select abs(-20.5); -- 20.5

ceil
取上限

select ceil(14.2); -- 15

floor
取下限

select floor(14.8); -- 14

least
取最小值

select least(11, 4, 5);

mod
取余数

select mod(9, 4); -- 1

round
四舍五入

select round(11.2); -- 11
select round(11.8); -- 12

聚集函数

数据准备：

create table table9(
  id int4,
  num int4
);
insert into table9 (id, num) values (1, 12);
insert into table9 (id, num) values (1, 15);
insert into table9 (id, num) values (2, 11);
insert into table9 (id, num) values (3, 17);
insert into table9 (id, num) values (3, 14);

count
统计行数

select count(*) from table9; -- 5

avg
取均值

select id, avg(num) from table9 group by id;

max
取最大值

select id, max(num) from table9 group by id;

min
取最小值

select id, min(num) from table9 group by id;

sum
求和

select id, sum(num) from table9 group by id;

字符函数

数据准备：

create table table8(
  id int4,
  name text,
  line text
);
insert into table8 (id, name, line) values (1001, 'aladdin', 'xAddcwWf345ww');

char_length
字符数

select char_length(line) from table8; -- 13

concat
拼接字符串

select concat(id, name, '&&') from table8; -- 1001aladdin&&

concat_ws
拼接字符串，并指定分隔符

select concat_ws('_', id, name, line) from table8; -- 1001_aladdin_xAddcwWf345ww

substring
截取字符串

-- 从 3 开始，截取 2 个
select substring(line, 3, 2) from table8;

split_part
分割字符串

select split_part('aladdin.im', '.', 1); -- aladdin

upper
将字符串转大写

select upper(line) from table8;

lower
转小写

select lower(line) from table8; -- xaddcwwf345ww

ltrim
去除字符串左边空格

select ltrim('  xxdd  ');

rtrim
去除字符串右边空格

select rtrim('  xx  ');

position
字符串首次出现的位置

select position('Add' in line) from table8;

repeat
重复字符串

select repeat(name, 3) from table8; -- aladdinaladdinaladdin

replace
替换字符串

-- 将所有w替换成*
select replace(line, 'w', '*') from table8; -- xAddc*Wf345**

left
返回最左面 n 个字符

select left(line, 5) from table8;

right
返回最右面 n 个字符

select right(line, 3) from table8; -- 5ww

rpad
字符不满，用 * 补全

-- 字符不满 10 个，用 # 补满
select rpad(name, 10, '#') from table8;

日期函数

extract

select extract(year from now()); -- 2019
-- week 一年中第几周
-- doy 一年中第几天

四则运算

select now() + interval '1 year'; -- 2020-04-10 09:43:35...

age
时间间隔

select age('2018-5-23'::date, '2015-5-23'::date); -- 3 years 0 mons 0 days 0 hours 0 mins 0.00 secs

聚合函数

string_agg
将结果集的某个字段所有行连接成字符串

-- 第一个参数为合并字段，第二个参数为分隔符
-- 可以用于分组合并
select id, string_agg(name, ',') from table5 group by id;

array_agg
与 string_agg 类似，但是它返回的是一个数组

select id, array_agg(name) from table5 group by id;

array_to_string
这个函数可以将数组类型数据转换成字符串

select id, array_to_string(array_agg(name), ',') from table5 group by id;

array_to_json
与 array_to_string 类似，这个函数只是将数组转换成了 Json

select array_to_json(arr_int) from table6;

转化函数

translate
将字符串中的内容进行转化

create table public.table3(
msg text
);
insert into public.table3(msg) values ('ALADDIN 2019');
-- translate 函数
select 
     translate(msg, '0123456789ABCD', '##########%%%%') 
from table3; -- %L%%%IN ####

replace
将字符串中的字段进行转化

select 
     replace(msg, 'ALADDIN', 'HELLO') 
from table3; -- HELLO 2019

coalesce
将 null 值转化为特定值

-- 将 null -> 0
select coalesce(t2.sal, 0) from table2 t2;

窗口函数

窗口函数不会将结果集进行分组计算输出一行，而是将计算后的结果集输出到结果集上，可以简化 SQL 代码。
一般情况下，窗口函数能做的事情，都是可以通过复杂的 SQL 语句来实现。

avg()
用于计算分组后的平均值 ( avg() 为聚合函数 + over() 用来进行分组操作 = 分组求均值 )

create table student_score(
stu_id int4,
subject text,
score double precision
);
insert into student_info(id, name) values (1001, 'aladdin'), (1002, 'bilib'), (1003, 'chrome');
insert into
student_score(stu_id, subject, score)
values
       (1001, 'chinese', 90.0), (1001, 'math', 100.0), (1001, 'english', 80.0),
       (1002, 'chinese', 100.0), (1002, 'math', 80.0), (1002, 'english', 60.0),
       (1003, 'chinese', 60), (1003, 'math', 90), (1003, 'english', 100.0);
-- 统计学个各科成绩和各科的平均成绩
select
     ss.subject, si.name, ss.score, avg(ss.score) over(partition by ss.subject)
from
   student_info si left join student_score ss on si.id = ss.stu_id;

row_number()
为分组后数据标注行号

select 
     si.*, ss.subject, ss.score, row_number() over (partition by ss.subject order by ss.score) 
from 
   student_info si left join student_score ss on si.id = ss.stu_id;

rank() 和 dense_rank()
rank() 函数与 row_number() 类似，只是 rank() 会考虑并列的问题，rank() 和 dense_rank() 的区别是，rank() 会产生排序间隙，dense_rank() 不会产生排序间隙：

rank() 的排序结果可能是：1, 1, 3, 4, 4, 6
dense_rank() 的排序结果是：1, 1, 2, 3, 3, 4

lag()
将某字段的数据进行偏移拼接：

select
     si.*, ss.subject, ss.score, lag(ss.score, 1, '100.0') over(partition by ss.subject order by score)
     -- lag(field, offset, default value)，如果向上偏 offset = -X
from
   student_info si left join student_score ss on si.id = ss.stu_id;

first_value() & last_value()
取结果集分组后的第一条 / 最后一条数据

select
     ss.subject ,first_value(ss.score) over (partition by subject order by score)
from
   student_score ss;

nth_value()
用来取结果集某一指定行的数据

select
     ss.subject ,nth_value(ss.score, 2) over (partition by subject order by score)
from
   student_score ss;