greenplum
创建分区表
CREATE TABLE fi_middle.order_detail
(
date_id integer,
order_id character varying(22),
product_id character varying(50),
order_quantity numeric,
allot_quantity numeric,
original_price numeric,
sale_price numeric,
vip_price numeric,
bargin_price numeric,
medium numeric,
promotion_id numeric,
is_vip_discount numeric,
product_type numeric,
reduce_price numeric,
etl_change_date timestamp without time zone,
order_items_id numeric,
gift_card_charge numeric(12,2),
gift_unit_price numeric,
item_id numeric,
parent_item_id numeric,
allot_activity_fee numeric(12,2),
allot_point_deduction_amount numeric,
send_date timestamp without time zone,
privilege_code_discount_amount numeric,
relation_type numeric,
parent_id character varying(16),
shop_id numeric,
shop_type numeric
)
WITH (
OIDS=FALSE
)
DISTRIBUTED BY (order_id)
PARTITION BY RANGE(send_date)
(
PARTITION p_order_detail_20170701 START ('2017-06-01 00:00:00'::timestamp without time zone) END ('2017-07-01 00:00:00'::timestamp without time zone),
PARTITION p_order_detail_20170801 START ('2017-07-01 00:00:00'::timestamp without time zone) END ('2017-08-01 00:00:00'::timestamp without time zone)
)
添加分区
alter table fi.order_detail_adt_cp add partition p_order_detail_adt_20170601 START ('2017-05-01 00:00:00'::timestamp without time zone) END ('2017-06-01 00:00:00'::timestamp without time zone) EVERY ('1 mon'::interval)
修改表名称
alter table fi_middle.order_detail rename to order_detail_adt;
（表名默认在旧表模式下，不必指定模式）
内容修改
update fi.fi_promotion_info set supp_no='00'||supp_no where date_id=20170915 and length(supp_no)=5 and supp_no<>'80000';
update fi.fi_promotion_info set supp_no='0'||supp_no where date_id=20170915 and length(supp_no)=4;
update fi.fi_promotion_info set end_date=end_date+interval '1 day' where date_id=20170915 and to_char(end_date,'yyyymmdd hh24:mi:ss') like '%00:00:00';
update fi.fi_promotion_info set cat2_name='全品' where date_id=20170915 and cat2_name='全部品种';