binguo的博客

--生成月份字典表drop table if exists monthmapping;create table monthmapping as select cast(to_char(onday,'yyyy-mm')||'-01' as char(10)) monthid,onday from (select cast('2001-01-01 00:00:00' as timestamp)

--日期转时间戳SELECT EXTRACT(epoch FROM NOW()); SELECT EXTRACT(epoch FROM CAST('2017-12-06 00:17:10' AS TIMESTAMP)); --时间戳转日期SELECT TO_TIMESTAMP(1512490630)

--1.指定月份总共多少天SELECT EXTRACT(DAY FROM CAST('2017-08-01' AS DATE) + INTERVAL '1 month' - INTERVAL '1 day')SELECT EXTRACT(DAY FROM CAST(TO_CHAR(NOW(),'YYYY-MM')||'-01' AS DATE) + INTERVAL '1 month' -

1.查看数据库中大于100MB的表的倾斜情况SELECT table_name,max_div_avg,pg_size_pretty(total_size) table_size FROM (SELECT table_name,MAX(size)/(AVG(size)+0.001) AS max_div_avg,CAST(SUM(size) AS BIGINT) total_size

1.总的调度流程，通过linux下的crontab定时执行一个包含kjb执行信息的shell脚本2.XXXX_0_Execute_Judge转换下有两个作业，通过获取每天的同步状态值来判断是否执行同步工作，如果同步状态不满足，会发邮件告知3.XXXX_A0_Connect_Next作业下含有四个并行执行的作业，Message_Prepare_Yes作业负责的获取到同步状态O

--1.查询指定库下面的活动会话，procpid字段表示会话Procselect * from pg_stat_activity where datname = 'dbname';--2.中断查询，${procpid}表示上面查询对应的procpid，下同select pg_cancel_backend(${procpid}); --3.中断会话连接select pg_termina

--1.判断字符串是否是数字create or REPLACE function isnumeric(txtStr VARCHAR) RETURNS BOOLEANAS$$BEGINRETURN txtStr ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$';END;$$language 'plpgsql';create or REPLA

--1.利用INTERSECT关键字数组之间交集函数CREATE OR REPLACE FUNCTION array_intersect(anyarray, anyarray) RETURNS anyarray AS $$ SELECT ARRAY( SELECT UNNEST($1) INTERSECT SELECT UNNE

1.参考GreenPlum之生成月份字典表   GreenPlum之生成月份字典表2.根据Case When语句及GreenPlum中string_agg聚合函数拼凑对应月份或季度周期字段，以下是核心实现sql，读者可根据需求自行修改：  --字段按照月份行转列if position('yyyy-mm' in in_datecolum)>0 then out

把一个数据库备份成SQL脚本文件，该脚本文件可以用gpdbrestore命令来恢复数据库。摘要gpcrondump -x database_name    [-s schema | -t schema.table | -T schema.table]    [--table-file=filename | --exclude-table-file=filename]