postgresql常用函数

常用函数

32位UUID

select ROW_NUMBER() over();
select replace(cast(uuid_generate_v4() as varchar),'-','');

创建dual视图

create or replace view dual as select null::unknown as  unknown;

创建序列

INCREMENT BY ： 每次序列增加（或减少）的步长
MINVALUE ： 序列最小值，NO MINVALUE表示没有最小值
MAXVALUE ： 序列最大值，NO MAXVALUE表示没有最大值
START WITH ：以什么序列值开始
CYCLE ： 序列是否循环使用
OWNED BY ： 可以直接指定一个表的字段，也可以不指定。

--创建
DROP SEQUENCE seq_test;
CREATE SEQUENCE seq_test INCREMENT BY 2 MINVALUE 1 MAXVALUE 5 START WITH 2 NO CYCLE;
  
--查询
select nextval('seq_test')

开窗函数

--row_number(行号)
select * ,row_number() over (partition by c_id order by s_score) from score;
--rank(排名)
--dense_rank(密集排名)
--ntile(分组排名)

pg递归查询

--postgre sql递归查询:
with  recursive  r  as (
select * from [表] where id = xxx
union all
select [表].* from [表], r where [表].id = r.parent_id
)
select * from r order by id;
with recursive r as (
	select * from place where id = 2
	union all
 	select place.* from place, r where place.parent_id = r.id
 ) 
 select * from r order by id;

position匹配字段

position(substr in str)

coalesce处理null

--coalesce(expr1,expr2,...)
select coalesce(null,0)

cast类型转换

--cast(expression AS data_type)
select cast('304' as numeric);

array_agg多行合并

--示例1：
select array_agg(org_name) from jg_om_organization;

--示例2：
select 
	t1.role_id,  
	array_to_string (ARRAY_AGG ( t2.emp_name  ),',') role_user
from 
	jg_ac_role_emp t1 ,
	jg_om_employee t2  
where role_id = 'SXYCL_FZR' 
	and t1.emp_id = t2.emp_id 
    group by t1.role_id;
    
--结合开窗函数使用,根据角色和部门分组
select 	
	t1.role_id,
	t1.emp_id,
	t2.emp_name,
	t2.res_code,
	'01' current_node,
	array_to_string(ARRAY_AGG(t2.emp_name) 
                    over(partition by t1.role_id,t2.res_code),',') role_user
from 
	jg_ac_role_emp t1 ,
	jg_om_employee t2 	
where t1.emp_id = t2.emp_id
	and role_id = 'SXYCL_FZR';

concat函数

在PostgreSQL中, CONCAT 函数用来将两个或多个字符串连接成一个。

用法： CONCAT(string_1, string_2, ...)

select concat(1,2,3,222,'111') --123222111

substr截取函数

--第一个是位置，第二是个数
select substr('1234',1,3) as re ; --123

select substr('1234',0,3) as re; --特殊
--如果是pg：得到的结果是12

--如果是oracle：得到的结果是123

--如果是mysql：得到的结果是空

--因为pg和mysql的substr()函数的第一个位置都是为1的，pg为0的时候返回数据是有值的，但mysql为0时候返回数据为空了

replace函数

select replace('2022-01-29','-','') --结果：20220129

位数不够补0

--位数不够补零
select lpad(cast(89 as varchar), 6, '0');

split_part 分割取值

--将aaaa,bbbb 分割，
--参数 1：取第一部分
select split_part('aaaa,bbbb',',',1);  --aaaa

round 四舍五入

select round(20.341, 2)

floor 向下取整

select floor(20.349);

--小数向下取整
select floor(20.349*100)/100;

ceil 向上取整

select ceil(20.341)

--小数向上取整
select ceil(20.341*100)/100;

排序

row_number() over( partition by 分组列 order by 排序列 desc )

ROW_NUMBER () OVER (ORDER BY  t.apply_id,t.org_code,tmp1.staff_wage,tmp1.flag  )