create table iss_ind.ind_pm_AcceptanceBillingPrjMonth(
gp_id serial NOT NULL,
gp_prjid int8, -- 项目维度id int8
gp_prjid_m int8,
gp_prjccid varchar, -- 项目实施成本中心维度ID
gp_indexno varchar , -- 指标编号
gp_value numeric(16,2), -- 指标值
gp_accperiod timestamp, -- 数据统计周期
gp_getitat timestamp -- ETL时间
)
DISTRIBUTED BY (id);

 

 

 

 

insert into iss_edw.ewd_pm_Report_Acceptance_Billing_PrjMonth
(
gp_prjid,
gp_prjid_m,
gp_prjccid,
gp_value,
gp_accperiod,
gp_getitat
)
select
(
pm_prjid,
pm_prjid_m,
pm_prjccid,
pm_accountid,
case when gp_indexno = 'PM01301' then total_Actual_fill,
when gp_indexno = 'PM01302' then Cumulative_confirm_workhours,
when gp_indexno = 'PM01303' then cumul_confirm_rate_workHours,
when gp_indexno = 'PM01304' then Cumulative_Estimation_workhours,
when gp_indexno = 'PM01305' then cumulative_tibao_workhours,
when gp_indexno = 'PM01306' then cumul_estimation_income_rmb,
when gp_indexno = 'PM01307' then cumul_tibao_income_rmb,
when gp_indexno = 'PM01308' then cumul_confirm_income_rmb,
when gp_indexno = 'PM01309' then cumul_confirm_je,
when gp_indexno = 'PM01310' then ontime_tibao_workhours,
when gp_indexno = 'PM01311' then Tmontime_tibao_rate
end as gp_value,
AccPeriod,
GetItAt
)
from iss_edw.ewd_pm_Report_Acceptance_Billing_PrjMonth;