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;