PG经验资料总结

--一 日期计算和转换	
	--1.1 当前日期时间
		select now()				--2020-12-16 10:25:51 DATE
		      ,current_timestamp	--2020-12-16 10:25:51 DATE
		      ,current_date  		--2020-12-16 DATE
		      ,current_time         --10:25:51 DATE
		      ;
	--1.2 文本数据类型转化成日期类型(3种)
		select 
			   current_date                       --2020-12-31 DATE  
			  ,to_date('2020-12-31','YYYY-MM-DD') --2020-12-31 DATE
			  ,date('2020-12-31')                 --2020-12-31 DATE
			  ,date '2020-12-31'                  --2020-12-31 DATE
			  ,cast(current_date as VARCHAR)      --2022-02-09
			  ,to_char(current_date,'YYYY-MM-DD')
			  ,to_char(current_date,'YYYYMMDD')
			  ,to_char(current_date,'YYYY/MM/DD')
			  ;
	--1.3 获取日期月初、月末、年初、年末 	
		select 
		     current_date                                                   --2022-02-09 date
			,date_trunc('month',current_date) 						        --2022-02-01 00:00:00 date 本月第一天
			,date_trunc('month',current_date) + interval '1 month - 1 day'  --2022-02-28 00:00:00 date 本月最后一天
			,date_trunc('month',current_date) - interval '1 day' 		    --2022-01-31 00:00:00 date 上月最后一天
			,date_trunc('year',current_date) 						        --2022-01-01 00:00:00 date 
			,date_trunc('year',current_date) + interval '12 month - 1 day' 	--2022-12-31 00:00:00 date			
			;
	--1.4 截取年月日的函数
		SELECT
			 current_timestamp							--2022-02-09 14:24:18 DATE
		    ,current_date 								--2022-02-09 DATE
			,EXTRACT(YEAR FROM current_date)            --2022 int
			,EXTRACT(month FROM current_date)           --2  int
			,EXTRACT(day FROM current_date)             --9  int
			,EXTRACT(HOUR   FROM current_timestamp)     --14 INT
			,EXTRACT(MINUTE FROM current_timestamp)     --24 INT
			,EXTRACT(SECOND FROM current_timestamp)     --18.527019 INT
			;
	--1.5 日期按照周期计算(存储中直接用变量替换'2020-12-16') 周\月\年 week、month、year
		select 
			 now()				                                                --2022-02-09 14:47:59 date
		    ,current_timestamp	                                                --2022-02-09 14:47:59 date
		    ,current_date 		                                                --2022-02-09          date
		    ,now() + interval '1 year'                                          --2023-02-09 14:47:59 date
		    ,now() + interval '1 month'                                         --2023-03-09 14:47:59 date
			,now() + interval '1 week'                                          --2022-02-16 14:47:59 date
			,now() + interval '1 day'                                           --2022-02-10 14:47:59 date
			,now() + interval '1 hour'                                          --2022-02-09 15:47:59 date
			,now() + interval '1 minute'                                        --2022-02-09 14:48:59 date
			,now() + interval '1 second'                                        --2022-02-09 14:48:00 date
		    ,to_char('2020-12-16'::timestamp+interval '+1 day','YYYY-MM-DD')    --2020-12-17
		    ,to_char('2020-12-16'::timestamp+interval '-1 day','YYYY-MM-DD')    --2020-12-15
		    ; 
			存储中使用:v_tommoring_auto_date := to_char(v_max_auto_date::timestamp+interval '1 day','YYYY-MM-DD');	
	--1.6 根据身份证号获取客户年龄，四种方式
		select
			 current_date                                                                                      --2022-02-09 date
			,age(timestamp '1989-02-26')                                                                       --32 years 11 mons 11 days 精确
			,extract(year from age(current_date,date(substring('320723198902260017',7,8))) )                   --32  比较准确
			,to_number(to_char(current_date,'YYYY'),'9999')-to_number(substr('320723198902260017',7,4),'9999') --33
			,(current_date  - date(substring('320723198902260017',7,8)))/365                                   --32
			,(date(now())  - date(substring('320723198902260017',7,8)))/365                                    --32
			;
	--1.7varchar转化成number
	cast(B.trade_amt as decimal)
--二 存储测试
--三 存储中常用语法语句
	--1 查看表是否存在，存在就删除(动态sql拼接)
		select count(*) into isExist  from pg_class where relname=v_tab_name||'_'||v_p;
		if(isExist=true) then 
			pSql:= 'drop table '||v_name||'_'||v_p||'';
			execute pSql;
			raise notice 'pSql:%',pSql;
		else
			raise notice '所选数据不存在，不操作';
		end if ;	
	--2 正则匹配的两种方法：匹配字符串和数字
		1. where relname ~ '[0-9]{8}'	
		2. select to_char(date(max(substr(relname,length('cdp_t_creditcard')+2,8))),'YYYY-MM-DD')
		   from pg_class 
		   where relname similar to 'cdp_t_creditcard'||'_[0-9]{8}'  --模糊匹配8位的数字
		   and relname<>'cdp_t_creditcard_x'; 
	--3 对变量的替换和大写小写
		v_lsb_jgb_name := replace(v_lsb_name,'_X','');
		v_lsb_name:= upper(v_lsb_name);
		v_lsb_jgb_name := lower(v_lsb_jgb_name);
	--4 查询表，拼接”'“需要"''"进行转义(输出结果，如果需要输出变量则 '%')
		pSql:='select * from public.tab_partition where lower(tab_name) = '''||v_lsb_jgb_name||'''';
		execute pSql into v_tab_name,v_org_col_name,v_is_need_org_partition,v_is_monend_save;
		raise notice '结果表:%-%-%-%',v_tab_name,v_org_col_name,v_is_need_org_partition,v_is_monend_save;
	--5 pg的merge方法
	update cw_dr t1 set 等级 = '1'  from cw_1 t2 where trim(t2.yx_phone) = trim(t1.手机号码); -- 3
    --6.行转列类似于炸裂函数
    regexp_split_to_table('1|2|3|4|5',E'\\|')  persondepamt,
--四 存储中书写注意事项
	--存储中调用有返回结果的函数时需要插入到变量中，不然会报错
	select cdp_pro_create_partition_his(v_tab_name,v_org_col_name,v_tommoring_auto_date,v_tommoring_auto_date) into v_log;
--五 优化,排错
错误一：解决postgresql - ERROR: query has no destination for result date
	在存储中调用A存储过程时,若A存储有返回值则要用变量去接受返回值
	-- 声明变量处声明变量
	v_log text;
	-- 此处是需要建立多级分区的语句
	select cdp_pro_create_partition_his(v_tab_name,v_org_col_name,
	v_tommoring_auto_date,v_tommoring_auto_date) into v_log;


--六 进程相关
	--2.1 查看任务进程，并kill的sql语句
	 --找到‘base_card_mpur_x’表的进程id
	 select pid from pg_catalog.pg_locks where relation in (select  oid from pg_catalog.pg_class where relname='base_card_mpur_x');
	 --查看活跃进程
	 select pid,state,usename,query,query_start 
	 from pg_stat_activity 
	 where pid in (select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r');
	 --调用函数杀掉该进程
	 select pg_cancel_backend(259756);
	 select pg_terminate_backend(30008);
--七 DDL语句
	--1.1 创建dblink语句
	create public database link MPM_DEV
	connect to DEV identified by  password 
	using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 1521)))(CONNECT_DATA =(SID = cpddpdb1)))';
	--1.2 分区建表(自定义和默认)
	create  table  xyk_fzyx.base_cust_view_32000020 partition of xyk_fzyx.base_cust_view for values in ('32000020');
	...
	create  table  xyk_fzyx.base_cust_view_32025182 partition of xyk_fzyx.base_cust_view for values in ('32025182');
	create  table  xyk_fzyx.base_cust_view_defsult partition of xyk_fzyx.base_cust_view default;
	
	create  table p20200507 partition of cdp_t_card_acc_test for values in ('2020-05-07') partition by hash(open_org_id) ;
	create  table p20200507_org0001 partition of p20200507 for values with (modulus 400,remainder 0);
	--1.3 dblink pg库，同库
	select * from dblink('hostaddr=21.144.1.118 port=6432 dbname=dhfxyj user=dhfxyj password=Js#dhfxyj_3604','select menu_id,menu_name from bc_pmctl_menu')
	as t(a text,b text);


	--1.4 创建分区表
    drop table T_CDM_DWD_CUST_INFO_TQ;
    	CREATE TABLE IF NOT EXISTS  T_CDM_DWD_CUST_INFO_TQ (
    		cs_field1          varchar(1)     DEFAULT NULL 
    		,cs_field2         varchar(8)     DEFAULT NULL 
    		,cs_field3         varchar(20)    DEFAULT NULL 
    		,cs_field4         varchar(60)    DEFAULT NULL 
    		,cs_field5         text           DEFAULT NULL 
    		,cs_field6         int            DEFAULT NULL 
    		,cs_field7         decimal(10,2)  DEFAULT NULL 
    		,cs_field8         float          DEFAULT NULL 
    		,cs_field9         char(1)        DEFAULT NULL 
    		,bds_etl_job_dt    DATE not NULL
    	)
    	PARTITION BY  LIST (bds_etl_job_dt);
    	COMMENT ON TABLE public.T_CDM_DWD_CUST_INFO_TQ IS '数据接口测试多字段大数据量测试表';
    	COMMENT  ON COLUMN public.T_CDM_DWD_CUST_INFO_TQ.cs_field1      IS 'varchar(1)测试字段';
    	
    select * from T_CDM_DWD_CUST_INFO_TQ;
    
    create  table  T_CDM_DWD_CUST_INFO_TQ_20220101 partition of T_CDM_DWD_CUST_INFO_TQ for values in ('2022-01-01');
    create  table  T_CDM_DWD_CUST_INFO_TQ_20220102 partition of T_CDM_DWD_CUST_INFO_TQ for values in ('2022-01-02');
    
    create  table  T_CDM_DWD_CUST_INFO_TQ_default partition of T_CDM_DWD_CUST_INFO_TQ default;
    
    insert into T_CDM_DWD_CUST_INFO_TQ (cs_field1,bds_etl_job_dt)  
    select '1','2022-01-03' ;
--1.5 分区加索引
   PARTITION BY LIST (manager_pporg_id);
   CREATE INDEX base_cust_view_iden_no_idx ON ONLY xyk_fzyx.base_cust_view USING btree (iden_no);
   CREATE INDEX base_cust_view_manager_id_idx ON ONLY xyk_fzyx.base_cust_view USING btree (manager_id, first_pporg_id);
   CREATE INDEX base_cust_view_manager_org_id_idx ON ONLY xyk_fzyx.base_cust_view USING btree (manager_org_id, manager_porg_id, manager_pporg_id);
   CREATE INDEX base_cust_view_manager_pporg_id_idx ON ONLY xyk_fzyx.base_cust_view USING btree (manager_pporg_id);
--1.6查看表的字段类型
    SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar
        , a.attnotnull AS notnull, b.description AS comment
    FROM pg_class c, pg_attribute a
        LEFT JOIN pg_description b
        ON a.attrelid = b.objoid
            AND a.attnum = b.objsubid, pg_type t
    WHERE c.relname = 'CDP_t_pim_info_x'
        AND a.attnum > 0
        AND a.attrelid = c.oid
        AND a.atttypid = t.oid
    ORDER BY a.attnum;
    -- 查看pg存储过程的语句
    select prosrc from pg_proc where proname = 'cdp_pro_create_partition_his'