--一 日期计算和转换
--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'
扫一扫
05-05
602
602
12-27
4848
4848
08-24
600
600
09-29
1万+
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
