數(shù)字函數(shù)

• 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
  取余數(shù)

select mod(9, 4); -- 1

• round
  四舍五入

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

聚集函數(shù)

數(shù)據(jù)準備：

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
  統(tǒng)計行數(shù)

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;

字符函數(shù)

數(shù)據(jù)準備：

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

• char_length
  字符數(shù)

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
  將字符串轉(zhuǎn)大寫

select upper(line) from table8;

• lower
  轉(zhuǎn)小寫

select lower(line) from table8; -- xaddcwwf345ww

• ltrim
  去除字符串左邊空格

select ltrim('  xxdd  ');

• rtrim
  去除字符串右邊空格

select rtrim('  xx  ');

• position
  字符串首次出現(xiàn)的位置

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;

日期函數(shù)

• 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

聚合函數(shù)

• string_agg
  將結(jié)果集的某個字段所有行連接成字符串

-- 第一個參數(shù)為合并字段，第二個參數(shù)為分隔符
-- 可以用于分組合并
select id, string_agg(name, ',') from table5 group by id; 

• array_agg
  與 string_agg 類似，但是它返回的是一個數(shù)組

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

• array_to_string
  這個函數(shù)可以將數(shù)組類型數(shù)據(jù)轉(zhuǎn)換成字符串

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

• array_to_json
  與 array_to_string 類似，這個函數(shù)只是將數(shù)組轉(zhuǎn)換成了 Json

select array_to_json(arr_int) from table6;

轉(zhuǎn)化函數(shù)

• translate
  將字符串中的內(nèi)容進行轉(zhuǎn)化

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

• replace
  將字符串中的字段進行轉(zhuǎn)化

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

• coalesce
  將 null 值轉(zhuǎn)化為特定值

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

窗口函數(shù)

窗口函數(shù)不會將結(jié)果集進行分組計算輸出一行，而是將計算后的結(jié)果集輸出到結(jié)果集上，可以簡化 SQL 代碼。
一般情況下，窗口函數(shù)能做的事情，都是可以通過復雜的 SQL 語句來實現(xiàn)。

• avg()
  用于計算分組后的平均值 ( avg() 為聚合函數(shù) + 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);
-- 統(tǒng)計學個各科成績和各科的平均成績
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()
  為分組后數(shù)據(jù)標注行號

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() 函數(shù)與 row_number() 類似，只是 rank() 會考慮并列的問題，rank() 和 dense_rank() 的區(qū)別是，rank() 會產(chǎn)生排序間隙，dense_rank() 不會產(chǎn)生排序間隙：

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

• lag()
  將某字段的數(shù)據(jù)進行偏移拼接：

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()
  取結(jié)果集分組后的第一條 / 最后一條數(shù)據(jù)

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

• nth_value()
  用來取結(jié)果集某一指定行的數(shù)據(jù)

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