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;