GreenPlum常用SQL

zhaoke0727

　　下面整理记录一些时常会使用到的SQL,也方便以后查阅：
　　描述
　　SQL
　　添加分区（在没有创建DEFAULT PARTITION的情况下）
　　alter table tablename add partition  partition_name start (BIGINT '1471276800000')  inclusive end (BIGINT '1471363200000')  exclusive;
　　添加分区（在创建DEFAULT PARTITION的情况下）
　　ALTER TABLE tablename SPLIT  DEFAULT PARTITION
　　START (1471363200000) INCLUSIVE  END (1471449600000) EXCLUSIVE
　　INTO (PARTITION partition_name, default partition)
　　修改ID序列取值的方式，提高效率
　　ALTER SEQUENCE tablename_seq_name CACHE  100
　　创建分区
　　PARTITION BY RANGE (date)
　　(PARTITION Jan16 START (BIGINT '1451577600') INCLUSIVE ,
　　PARTITION Feb16 START (BIGINT '1454256000') INCLUSIVE ,
　　PARTITION Mar16 START (BIGINT '1456761600') INCLUSIVE ,
　　PARTITION Apr16 START (BIGINT '1459440000') INCLUSIVE ,
　　PARTITION May16 START (BIGINT '1462032000') INCLUSIVE ,
　　PARTITION Jun16 START (BIGINT '1464710400') INCLUSIVE ,
　　PARTITION Jul16 START (BIGINT '1467302400') INCLUSIVE ,
　　PARTITION Aug16 START (BIGINT '1469980800') INCLUSIVE ,
　　PARTITION Sep16 START (BIGINT '1472659200') INCLUSIVE ,
　　PARTITION Oct16 START (BIGINT '1475251200') INCLUSIVE ,
　　PARTITION Nov16 START (BIGINT '1477929600') INCLUSIVE ,
　　PARTITION Dec16 START (BIGINT '1480521600') INCLUSIVE
　　END (BIGINT '1483200000') EXCLUSIVE );
　　创建分区(会创建天粒度的N多个分区20160701到20170101，每间隔一天一个分区)
　　PARTITION BY RANGE (TIME)(
　　PARTITION  partition_name START (BIGINT ‘1467302400000’) INCLUSIVE END(BIGINT ‘1483200000000’)  EXCLUSIVE EVERY(BIGINT ‘86400000’)
　　)
　　删除分区
　　ALTER TABLE tablename DROP PARTITION  partition_name;
　　删除表分区数据
　　ALTER TABLE tablename TRUNCATE PARTITION  partition_name;
　　删除列
　　ALERT TABLE tablename DROP COLUMN column_name ;
　　删除序列
　　DROP SEQUENCE serial_name
　　添加列
　　ALTER TABLE tablename ADD COLUMN  column_name  column_type
　　修改列类型
　　ALTER TABLE tablename>column_name
　　TYPE new_column_type
　　函数操作举例
　　例子一
　　删除函数：  DROP FUNCTION function_name(varchar);
　　创建函数：
　　CREATE OR REPLACE FUNCTION convert_grade(grade varchar)
　　RETURNS integer AS
　　$BODY$
　　DECLARE
　　result int;
　　BEGIN
　　if grade = '优' then result = 1;
　　ELSIF grade ='良' then  result = 2;
　　ELSIF grade = '中' then  result = 3 ;
　　ELSIF grade = '差' then  result = 4 ;
　　end if ;
　　RETURN result;
　　END;
　　$BODY$
　　LANGUAGE plpgsql VOLATILE;
　　例子二
　　---------创建生成RADIUS表的函数,参数：省份的拼音------------------
　　CREATE OR REPLACE FUNCTION  CREATE_RADIUS_TABLE(province varchar)
　　RETURNS BOOLEAN AS $$
　　DECLARE
　　passed BOOLEAN;
　　BEGIN
　　EXECUTE
　　'CREATE TABLE JK_'||province||'_RADIUS(

　　>　　start_time BIGINT,
　　end_time   BIGINT,
　　user_account VARCHAR,
　　src_ip     VARCHAR,
　　bras       VARCHAR
　　INSERT_DATE  DATE DEFAULT NOW()
　　)DISTRIBUTED BY (ID)';
　　EXECUTE 'ALTER SEQUENCE JK_'||province||'_RADIUS_ID_seq CACHE 100' ;
　　RETURN passed;
　　END;
　　$$  LANGUAGE plpgsql ;
　　----------用于创建所有省的所需的表-------------
　　CREATE OR REPLACE FUNCTION  CREATE_REPLACE_ALL_TABLE()
　　RETURNS BOOLEAN AS $$
　　DECLARE
　　passed BOOLEAN:=TRUE;
　　province varchar;
　　BEGIN
　　FOR province IN
　　SELECT OBJECT_EN_NAME FROM CM_PROVINCE
　　LOOP
　　EXECUTE 'DROP TABLE IF EXISTS JK_'||province||'_RADIUS'; --先删除
　　PERFORM CREATE_RADIUS_TABLE(province);
　　END LOOP;
　　RETURN passed;
　　END;
　　$$  LANGUAGE plpgsql ;
　　例子三
　　CREATE OR REPLACE FUNCTION  CREATE_PARTITION(beginT timestamp with time zone,partitionN INTEGER)
　　RETURNS BOOLEAN AS $$
　　DECLARE
　　passed BOOLEAN:=TRUE;
　　pname VARCHAR ;
　　smsc bigint ;
　　emsc bigint ;
　　beginTime timestamp with time zone;
　　partitionNum INTEGER ;
　　BEGIN
　　beginTime := to_TIMESTAMP(to_char(beginT,'yyyymmddhh24'),'yyyymmddhh24') ;
　　partitionNum := partitionN;
　　WHILE partitionNum > 0 LOOP
　　--得到分区名称
　　pname := to_char(beginTime,'yyyymmddhh24');
　　--开始毫秒数
　　smsc = extract(epoch from beginTime)::bigint * 1000;
　　--结束毫秒
　　beginTime := (beginTime + interval '1 hour');
　　emsc = extract(epoch from beginTime)::bigint * 1000;
　　--添加分区
　　EXECUTE 'ALTER TABLE'||'JK_COMPLEX_XDR'||' SPLIT DEFAULT PARTITION START ('||smsc||') INCLUSIVE
　　END ('||emsc||') EXCLUSIVE INTO (PARTITION p_day_'||pname||', default partition)';
　　partitionNum := partitionNum-1;
　　RAISE INFO 'current num: %,pname: %,stime:%,etime:%',partitionNum,pname,smsc,emsc;
　　END LOOP;
　　RETURN passed;
　　END;
　　$$  LANGUAGE plpgsql ;
　　例四：返回多条结果集
　　CREATE OR REPLACE FUNCTION  get_record()
　　RETURNS SETOF RECORD AS $$
　　DECLARE
　　rec RECORD;
　　BEGIN
　　FOR rec IN
　　SELECT OBJECT_NAME FROM CM_PROVINCE
　　LOOP
　　RETURN NEXT rec;
　　END LOOP;
　　RETURN ;
　　END;
　　$$  LANGUAGE plpgsql ;
　　--执行函数
　　select * from get_record() t(name varchar) ;
　　添加两层分区举例
　　create table p_test(

　　>　　name varchar ,
　　time bigint
　　)DISTRIBUTED BY (ID)
　　PARTITION BY LIST(name)
　　SUBPARTITION BY RANGE(time)
　　SUBPARTITION TEMPLATE
　　(
　　SUBPARTITION sp_time_111 START (BIGINT '111') INCLUSIVE END (BIGINT '222') EXCLUSIVE ,
　　default SUBPARTITION sp_time_other
　　)
　　(
　　PARTITION p_name_d VALUES('d'),
　　PARTITION p_name_s VALUES('s'),
　　PARTITION p_name_f VALUES('f')
　　);
　　--添加主分区
　　alter table p_test add partition p_name_x  VALUES('x');
　　--修改子分区模版,子分区将按照新的模版产生。已经存在的分区不会被修改
　　ALTER TABLE p_test SET SUBPARTITION TEMPLATE
　　(
　　SUBPARTITION sp_time_222 START (BIGINT '222') INCLUSIVE END (BIGINT '333') EXCLUSIVE ,
　　DEFAULT SUBPARTITION other
　　)
　　--查看分区情况
　　SELECT partitionboundary, partitiontablename,
　　partitionname, partitionlevel, partitionrank
　　FROM pg_partitions WHERE tablename='p_test';
　　--查询指定的子分区
　　select * from p_test subpartition(sp_time_111);