greenplum
租房请下载:房东直租APP
租房请下载:房东直租APP
展开
-
mysql迁移greenplum
1.locate(a,b)->position(a in b)2.convert()函数不可以使用3.ifnull(null,0.00)->COALESCE(null,0.00)4.timestampadd(hour,24,jysj)->jysj::timestamp+'24 hour'5.substr()->substring()6.from_unixtime(unix_ti原创 2017-11-28 09:09:16 · 775 阅读 · 0 评论 -
postgresql创建序列
创建表之前现需要创建序列drop table if exists 模式.seq_tablename;create sequence 模式.seq_tablenameincrement by 1minvalue 1no maxvaluestart with 1cache 1; 或者在create table 模式名.表名( shard_id seria...原创 2017-11-30 16:17:51 · 1504 阅读 · 2 评论 -
sql去重
单个字段去重 distinct多字段查重(去重使用delete)多字段:cxkh,jysjselect shard_id ,row_number() over (partition by cxkh,jysj) counts from table )t where t.counts >2原创 2018-01-30 16:22:07 · 436 阅读 · 0 评论 -
gp授权以及进程的查看和杀死
gp是指postgresql或者greenplum//gp授权grant select,insert,update,delete on "analysis"."t_model_a" to analysis;//进程select pg_cancel_backend();select * from pg_stat_activity;原创 2018-04-10 19:30:13 · 2424 阅读 · 0 评论 -
sql去重
查看去重select * from ( select name,row_number() OVER (PARTITION BY $filter$) rownum from table ) a where a.rownum >1原创 2018-05-02 10:57:32 · 495 阅读 · 0 评论 -
Postgresql行列转换
列转行1 查询测试表数据[html] view plain copycqdb=> select * from test ; name ------ AA BB CC (3 rows) 2 列转行(string_agg)[html] view plain copycqdb=> select string_agg(name,',') from test; stri...转载 2018-05-02 18:03:12 · 1550 阅读 · 1 评论 -
五组数据每组取前3条数据
select * from ( select name,row_number() over (partition by id order by id) flag from table where sid=1)a where flag <=3原创 2018-04-25 20:42:49 · 743 阅读 · 0 评论 -
Postgresql修改字段的长度
alter table table_name alter column column_name type varchar(200)原创 2018-06-19 11:48:17 · 4023 阅读 · 0 评论