SQL开发--PostgreSQL未完待续

跨表更新

update test t1 set field1=t2.field1 from test2 t2 where t1.id=t2.id

copy导入csv

copy sexit from 'e:/sexit.csv' delimiter as '|' csv quote as '''';
\copy pgbench_accounts from '/home/digoal/123.csv' with (format csv);
copy pgbench_accounts from '/home/digoal/123.csv' with (format csv);

跨表删除

delete from t using t1 where t.id = t1.id;
delete from t where id in (select id from t1);

distinct 和 distinct on

select distinct on (c3) c2,c3 from tbl; 
select DISTINCT id, c1 from test; 
count_distinct()

psql只输出结果

$ psql -A -t -c "select now() :: date";
2019-06-17

PostgreSQL的Lateral Joins

SELECT <columns>
FROM <table reference>,
     LATERAL <inner subquery>;
SELECT <columns>
FROM <table reference>
     JOIN LATERAL <outer subquery>
ON TRUE;

1、 例如查20200806这天射雕 游戏的新用户；最后充值时间，充值金额；如何写？

SELECT
	account_sub.accountid,
	account_sub.reg_time,
	pay_sub.pay_time,
	pay_sub.amount
FROM
	( SELECT accountid, reg_time FROM tbl_android_account_log WHERE pg_to_char ( reg_time ) = '20200806' AND appid = 2846 ) account_sub
	left join LATERAL ( SELECT l.pay_time, l.amount FROM tbl_ad_android_pay_log l WHERE pg_to_char ( l.pay_time) >= '20200806' AND l.appid = 2846 and  l.accountid = account_sub.accountid order by 1 desc) pay_sub on true;

PostgreSQL跟shell交互

split -l 20000 tbl_dup.csv load_test_  
date +%F%T.%N  
for i in `ls load_test_??`   
do  
psql <<EOF &  
truncate tmp;  
copy tmp from '/home/digoal/$i';  
EOF  
done  
for ((i=1;i>0;i=1))  
do  
sleep 0.0001  
cnt=`ps -ewf|grep -v grep|grep -c psql`  
if [ $cnt -eq 0 ]; then  
break  
fi  
done  
psql <<EOF  
copy (select id,sid,crt_time,mdf_time,c1,c2,c3,c4,c5,c6,c7,c8 from   
(select row_number() over(partition by sid,crt_time order by mdf_time desc) as rn, * from tmp) t  
where t.rn=1) to '/dev/shm/tbl_uniq.csv';  
EOF  
date +%F%

修改表

alter table TBL_IOS_CALLBACK_LOG set (fillfactor = 75);
alter table TBL_ANDROID_CALLBACK_LOG set (autovacuum_vacuum_scale_factor = 0.05);
-- 对表加快vaccum
alter table tbl_ad_ios_pay_log set (autovacuum_vacuum_scale_factor = 0.05);
1、增加一列
ALTER TABLE table_name ADD column_name datatype;
 2、删除一列
 ALTER TABLE table_name DROP  column_name;
 3、更改列的数据类型
ALTER TABLE table_name ALTER  column_name TYPE datatype;
alter TABLE table_namealter COLUMN column_name type integer USING column_name::integer; 
4、表的重命名
 ALTER TABLE table_name RENAME TO new_name;
 5、更改列的名字
 ALTER TABLE table_name RENAME column_name to new_column_name;
 6、字段的not null设置
 ALTER TABLE table_name ALTER column_name {SET|DROP} NOT NULL;
 7、给列添加default
 ALTER TABLE table_name ALTER column_name SET DEFAULT expression;

格式化函数

格式化函数
常用于构建动态SQL
format(formatstr text [, formatarg "any" [, ...] ])  
嵌入格式
%[position][flags][width]type  
  
type:  
  
s formats the argument value as a simple string. A null value is treated as an empty string.  
  
I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).  
  
L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable).  
例子
SELECT format('Hello %s', 'World';  
Result: Hello World  
  
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');  
Result: Testing one, two, three, %  
  
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');  
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')  
  
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');  
Result: INSERT INTO locations VALUES(E'C:\\Program Files')  

高效删除数据

 delete from tbl_ad_click_log
  where ctid = any(array(select citd
           from (select "row_number"() over(partition by es_app_id, idfa_sum order by id) as rn,ctid
                   from tbl_ad_click_log) as ad
          where ad.rn > 1));