代元培

Greenplum 的执行计划和 PostgreSQL 基本一致，但是在表关联的时候还是会有区别，因为 greenplum 中数据不同于 pg，greenplum 是将数据分布在多个节点上。所以当两表关联时关联列又不是表的分布键时就会出现表的广播和重分布。本文将详细介绍广播和重分布的区别及场景。

PostgreSQL列存与行存

vacuum：该选项主要是清理数据库表中的垃圾空间，该动作会消耗系统一定的资源，引起系统的IO上升，对有一定系统瓶颈来说容易造成堵塞，严重会把GP宕掉，造成数据库瞬断。一般不建议vacuum库中全表，通常做法是vacuum指定的表。

-- 在PG里如果想做除法并想保留小数，用上面的方法却行不通，因为"/" 运算结果为取整，并且会截掉小数部分。select 1/4; -- 0select round(1::numeric/4::numeric,2); -- 0.25select round(cast(1 as numeric )/cast(4 as numeric),2); -- 0.25-- cast函数用法SELECT substr(CAST(1234 AS text),3,1); -- 3...

alter table xxx owner to new_owner;

在PostgreSQL中，所有内容都是围绕role概念构建的。在macOS上首次安装PostgreSQL时，该脚本使用您的macOS用户名创建了一个角色，并授予了权限列表。PostgreSQL中没有用户，只有role。通过在终端中运行psql postgres，您将使用您的macOS用户名自动登录到PostgreSQL，从而访问创建的角色。就我而言，创建了flaviocopes角色，可以使用\du命令看到它：看到？ 默认情况下，我具有以下角色属性： Superuser...

对于数据库系统的并发控制，PostgreSQL采用MVCC（多版本并发控制）进行处理。这种机制有一个缺点，就是随着时间的推移，数据文件中积累的dead tuples会越来越多。怎么去清理这些dead tuples，这个时候就需要vacuum处理。 PostgreSQL系统的vacuum是一个例行性的维护过程，系统也会在启动服务时启动autovacuum守护进程对此进行维护，当然也有vacuum命令可以让用户进行手动执行vacuum操作。除了清理dead tuples，vacuum还有冻结事...

-- gp考试，执行角色选择：RCC组，集群选择：etron_exam，数据库选择：postgres-- 如果是测试sql，为了速度，可以使用limit限制返回的数据量。-- --用户表:-- create table public.user_info(-- user_id bigint,--用户编号-- user_name text,--用户名-- user_info jsonb,--用户详细信息-- --参考： {user_id:"用户编号",user_name:"用户名",provi.

前言 行列转换在做报表分析时还是经常会遇到的，今天就说一下如何实现行列转换吧。 行列转换就是如下图所示两种展示形式的互相转换。 行转列 PIVOT 后跟一个聚合函数来拿到结果，FOR 后面跟的科目是我们要转换的列，这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。 当然我们也可以用 CASE WHEN 得到同样的结果，就是写起来麻烦一点。使用 CASE WHEN 可以得到和 PIVOT 同样的结果，没有 PIVOT 简...

不必要的union（分析sql逻辑），相似的子查询重复union，仅仅为了过滤不同的条件。 影响：表重复冗余扫描多次，执行效率低 优化方法：使用case when改写union 举例：select id,score,'type1' from table_b where type=1union select id,score,'type2' from table_b where type=2union select id,score,'type3' fr...

-- row_number 最简单用法select f_uid, f_create_time, f_typefrom( select f_uid, f_create_time, f_type, row_number() over (PARTITION BY f_uid order by f_create_time desc) as rn from open.t_test) tmpwhere rn.

-- 删除f_1,f_2,f_3完全一致重复记录 保留重复记录中f_mt最大的一个create table open.t_ttt( f_1 character varying(128), f_2 character varying(128), f_3 smallint, f_ct timestamp without time zone, f_mt timestamp without time zone) WITH (appendonly=true) DISTRI.

PostgreSQL支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率，json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。注意：键值对的键必须使用双引号从PostgreSQL 9.3开始，json就成了pos

-- uc为schema pay_user_rd为账号-- 增加读写权限grant select on uc.t_did_2_uid to pay_user_rd;grant insert on uc.t_did_2_uid to pay_user_rd;grant update on uc.t_did_2_uid to pay_user_rd;grant delete on uc.t_did_2_uid to pay_user_rd;grant truncate on uc.t_did_2_.

# 查账号所拥有的权限select * from INFORMATION_SCHEMA.role_table_grants where grantee='pay_spv_itf_rd';

　　Greenplum由多个postgres组合而成，因此Greenplum属于分布式数据库，所以在创建数据表的时候需要指定分布键，当然在不指定的时候Greenplum数据库会默认使用使用表的第一个字段作为数据库的分布键。　　Greenplum分布策略　　（1）hash分布：选择一个列后者多个列作为数据表的分布键，通过hash计算，然后将插入的数据路由到特定的segment上；CREATE TABLE open.t_ttt( f_id INT, f_num INT)dis

　　PostgreSQL数据库提供regexp_split_to_table和regexp_split_to_array两个函数用于分隔字符串成表和数组，在某些场景下使用起来还挺方便的。　　举个例子：有这样一张表，维护用户的兴趣，多个兴趣用逗号分隔。-- f_interest 兴趣，多个兴趣逗号分割CREATE TABLE open.t_ttt( f_user_name character varying(20) NOT NULL, f_interest character v

COALESCE函数是返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错。select COALESCE(null,null); -- 结果得到''select COALESCE(null,null,'a',''); // 结果得到aselect COALESCE(null,null,'','a'); // 结果得到''-- 可以和其他函数配合来实现一些复杂点的功能：查询学生姓名，如果学生名字为null或''，则显示“姓名为空”se...

regexp_split_to_table、regexp_split_to_array这两个函数都是用来将字符串转换成格式化数据，一个是转换成结果集，一个是转换成数组。select regexp_split_to_table('a,b,c',',');select regexp_split_to_table('/home/work/pg.sh','\/');select regexp_split_to_table(coalesce('/home/work/pg.sh',''),E'\/');

-- 近6月的数据存放该表create table open.t_ttt_0401( uid bigint) WITH (appendonly=true) DISTRIBUTED BY (uid);-- 这是这一批每个月都有信贷还款的用户insert into open.t_ttt_0401select uidfrom ( select to_char(f_business_time, 'YYYY-MM') as m, f_trans_.

-- 方式一create external table ext.r_ext_t_pay (like open.t_pay)location ('gpfdist://*.*.*.*:****/open/t_pay')format 'TEXT' (delimiter as E'\t' null as '\N' escape 'OFF');-- 方式二create external table ext.r_ext_t_pay ( f_id bigint DEFAULT 1 NOT NULL,.

-- 创建父表CREATE TABLE open.t_log_info( f_date integer, f_action integer, f_uid bigint, f_note character varying(255)) WITH (appendonly=true) DISTRIBUTED BY (f_date, f_action);-- 创建子表 子表自动继承父表的所有字段 通过 INHERITS 指定继承的父表CREATE TABLE open.t.

-- 以表 t_test 为例-- 重置序列起始值为1alter sequence t_test_id_seq restart with 1;-- 查看当前序列SELECT nextval('t_test_id_seq ');-- 清空并重置自增主键TRUNCATE t_test RESTART IDENTITY;-- 清除所有的记录TRUNCATE t_test questions CASCADE;-- 清除所有的记录，并且索引号从0开始TRUNCATE t_test questio.

psql中character varying和character区别 类型 说明 character varying(n), varchar(n) 变长，有长度限制 character(n), char(n) 定长，不足补空白 text 变长，无长度限制 SQL定义了两种基本的字符类型：character varying(n)和character(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符的字符串。试图存储更长的字符串到这些类型的字段里会产生..

日期/时间操作符 操作符 例子 结果 + date '2001-09-28' + integer '7' date '2001-10-05' + date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00:00' + date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00:00' + interval

-- 使用SERIAL自增主键create table open.t_test( id SERIAL primary key, name varchar(20));-- 创建索引CREATE UNIQUE INDEX t_test_pkey ON open.t_test USING btree (id);-- 插入insert into open.t_test (name) values ('daiyuanpei');

# 理财场景 ACS 和 百盈 union all 加合计行 解决排序问题select *from ((select dt as "日期", ACS_DaE_cnt as "ACS_笔数", round(ACSDaE_amount/1000000,2) as "ACS_金额(万元)", concat(round(ACSDaE_zb*100,2),'%') as "ACS_金额占比", round(((daE_b2c+daE_duiSi+daE_kua.

select f_service from open.t_pay where position('B2CUnionpay' in f_service)!=0;select f_service from open.t_pay where position('B2CUnionpay' in f_service)>0;select f_service from open.t_pay where strpos(f_service,'B2CUnionpay')!=0;select f_service f.

--以 t_credit_trans_tag 表为例--查询索引select * from pg_indexes where tablename='t_credit_trans_tag';--创建索引CREATE INDEX time_index ON pay_data.t_credit_trans_tag USING btree (create_time);CREATE UNIQUE INDEX t_credit_trans_tag_pkey ON pay_data.t_credit_tran.

# 查某个表的所有字段名SELECT c.relname as table_name, a.attname as field_nameFROM pg_class as c,pg_attribute as awhere a.attrelid = c.oid and a.attnum>0and c.relname='t_rate_configure'group by c.relname,a.attnameorder by 1;# 查某个表的所有字段名&字段类型&a.

# 查包含某些字段名的表 字段名会区分大小写SELECT c.relname as table_name, a.attname as field_nameFROM pg_class as c,pg_attribute as awhere a.attrelid = c.oid and a.attnum>0and a.attname in ('f_is_need_front','F_email','passid','phone')group by c.relname,a.att.

# 创建分区主表CREATE TABLE core.t_trans_paymode_info( f_id bigint, f_trans_id character varying(32), f_enabled smallint, f_pay_mode smallint, f_amount bigint, f_fee_amount bigint, f_fee_rate bigint, f_fee_count_type smallint, .

# 查看表 分区信息 分区键SELECT pg_get_partition_def('core.t_payments'::regclass,true);SELECT pg_get_partition_def('wallet.dwd_wallet_trans'::regclass,true);

# 删除字段alter table open.t_pay drop column f_reserve_int_1;# 删除外表字段ALTER external TABLE pay_data_ext.r_ext_t_pay DROP COLUMN f_reserve_int_1;

alter table [tb_name] rename [old_field_name] to [new_field_name];

alter table [oldname] rename to [newname];

select t3.scenario_type as "场景名称", t3.amount as "交易金额", t3.cnt as "订单量", t3.kdj as "客单价", concat(round(t3.cost_r*100,4),'%') as "成本率", concat(round(t4.cost_r*100,4),'%') as "D-2成本率", concat(round((t3.cost_r-t4.cost_r)*100,4),'%.

# 上月同期select now() + interval '-1 month';select TO_CHAR(NOW() - INTERVAL '1 MONTH','YYYY-MM-DD') as "上月同期";select TO_CHAR(NOW() - INTERVAL '1 MONTH','YYYY-MM-DD HH24:MI:SS') as "上月同期";# 指定日期的上月同期 没有取上月最后一天select date_trunc('day', date '2020-03-31') +.

# 理财场景 ACS 和 百盈select dt, ACSDaE_cnt, round(ACSDaE_amount/1000000,2) as ACSDaE_amount, concat(round(round(ACSDaE_zb,4)*100,2),'%') as ACSDaE_zb, round(((daE_b2c+daE_duiSi+daE_kuanJie+daE_daiKou+daE_b2b+daE_qiTa)-ACS_DaE_cost)/100,2)...

# 获取表名及注释select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;# 过滤掉分表：加条件 and relchecks=0 即可select.