GreenPlum日常总结

1、计算占比

--保留小数点两位，拼接%
round((a2.uv-a1.uv)/a1.uv::numeric*100,2)||'%' as ratio

2、计算存储

--查看外部表的存储大小
select pg_size_pretty(pg_relation_size('ods.ods_dev_device_info_d_ext_20191225'))

3、decode条件判读

--单条件的字符串对比CASE WHEN
decode(carrier_id,-1,public.p_pub_get_randon_carrier_id() :: integer,4,public.p_pub_get_randon_carrier_id():: integer,carrier_id) carrier_id;
decode(expression, value, return [,value, return]...
       [, default])

4、包含指定字段的表的查询

-- 获取非分区表
select table_name,pg_size_pretty(pg_relation_size('bds.' || table_name)) from information_schema.columns
where table_schema ='bds' and column_name = 'device_id'
and table_name not like '%_1_prt_%'
and table_name not like '%_ext_%'
and table_name not in (select distinct split_part(table_name,'_1_prt_',1) from information_schema.columns
where table_schema ='bds' and column_name = 'device_id'
and table_name like '%_1_prt_%')
and pg_relation_size('bds.' || table_name) > 0
order by pg_relation_size('bds.' || table_name) desc
-- 获取分区表
select
split_part(table_name,'_1_prt_',1),
min(table_name),max(table_name),
count(1) cnt
from information_schema.columns
where table_schema ='bds' and column_name = 'device_id'
and table_name like '%_1_prt_%'
and pg_relation_size('bds.' || table_name) > 0
group by 1
order by cnt desc

3、delete table

--使用 delete from 操作一张表即时没有限制条件，依旧很慢，而且占用大量IO，建议使用TRUNCATE,因为它不做表扫描
delete from table
--TRUNCATE在在操作的每个表上请求一个ACCESS EXCLUSIVE锁， 这种锁会阻塞表上的所有其他并发操作。如果需要对一个表并发访问， 那么应该使用DELETE命令
TRUNCATE table

4、文件导出

psql -h192.168.1.1 -p5432  -Uyg_ids -d eamp -c "copy (select * from ids.ids_app_rank_product_common_d_bak20201215 where day_id=20201204) to STDOUT DELIMITER ',';" > /data1_4T/data/test/ids_app_rank_product_common_d_bak20201215_20201204.txt
pg_dump -h192.168.1.1 -p5432  -Uyg_ids -t 'ids.ids_dev_buffer_app_liveness_dis_new_m' eamp > ./back_app.sql

5、去重表中重复数据

-- 剔除重复行的数据转存到新表
SELECT DISTINCT *
INTO ids.ids_app_rank_product_common_d_temp 
FROM ids.ids_app_rank_product_common_d; 

-- 将原表表重命名
ALTER TABLE ids.ids_app_rank_product_common_d RENAME TO ids_app_rank_product_common_d_bak20201215;
-- 重新创建原表
复制粘贴建表语句，重新创建原表
-- 将旧表表重命名
insert INTO ids.ids_app_rank_product_common_d 
SELECT * FROM ids.ids_app_rank_product_common_d_temp;