宜宾网站建设公司

GreenPlum常用SQL

 下面整理记录一些时常会使用到的SQL,也方便以后查阅：

专注于为中小企业提供成都网站设计、做网站服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业正阳免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了数千家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

描述

SQL

添加分区（在没有创建DEFAULT PARTITION的情况下）

alter tabletablename add partition  partition_namestart (BIGINT '1471276800000')  inclusive end (BIGINT '1471363200000')  exclusive; 

添加分区（在创建DEFAULT PARTITION的情况下）

ALTER TABLE tablename SPLIT  DEFAULT PARTITION

START (1471363200000) INCLUSIVE END (1471449600000) EXCLUSIVE

INTO (PARTITIONpartition_name, default partition)

修改ID序列取值的方式，提高效率

ALTER SEQUENCEtablename_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_nameSTART (BIGINT ‘1467302400000’) INCLUSIVE END(BIGINT ‘1483200000000’)  EXCLUSIVE EVERY(BIGINT ‘86400000’)

)

删除分区

ALTER TABLEtablename DROP PARTITION partition_name;

删除表分区数据

ALTER TABLEtablename TRUNCATE PARTITION  partition_name;

删除列

ALERT TABLEtablenameDROP COLUMNcolumn_name;

删除序列

DROP SEQUENCE serial_name

添加列

ALTER TABLEtablename ADD COLUMN column_name column_type

修改列类型

ALTER TABLEtablename ALTER COLUMNcolumn_name

TYPEnew_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(
      ID bigserial,
     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,'yyyymmddhh34'),'yyyymmddhh34') ;
 partitionNum := partitionN;
        WHILE partitionNum > 0 LOOP
       
     --得到分区名称
     pname := to_char(beginTime,'yyyymmddhh34');
     --开始毫秒数
     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 RECORDAS $$
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(
    ID integer,
    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);


名称栏目：GreenPlum常用SQL
分享地址：http://ybzwz.com/article/gjhjho.html

其他资讯