chengben/Scripts/集团成本入库.sql

---下面的表入库的时候都没有加smonth过滤条件，有多少入多少，以后增量入库的时候要加上
create table sqmdb_cost.report50017(
report_id varchar,
smonth varchar,
period varchar,
sname varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric
)

--导入数据到report50017
--truncate table sqmdb_cost.report50017
insert into sqmdb_cost.report50017
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as sname,
column1,column2,column3,column4,column5 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''50017''  order by report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''50017''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'

create table sqmdb_cost.report50016(
report_id varchar,
smonth varchar,
period varchar,
sname varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric
)

--导入数据到report50016
--truncate table sqmdb_cost.report50016
insert into sqmdb_cost.report50016
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as sname,
column1,column2,column3,column4,column5 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''50016''  order by report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''50016''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'

create table sqmdb_cost.report50010(
report_id varchar,
smonth varchar,
period varchar,
sname varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric
)

--truncate table sqmdb_cost.report50010
--导入数据到report50010
insert into sqmdb_cost.report50010
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as sname,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''50010''  order by report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''50010''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'


--truncate table sqmdb_cost.report50003
--导入数据到report50003
insert into sqmdb_cost.report50003
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as sname,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,column15,column16,column17,column18,column19,column20,column21,
column22 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''50003''  order by report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''50003''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'


create table sqmdb_cost.report50002(
report_id varchar,
smonth varchar,
period varchar,
sname varchar,
COLUMN1 numeric,
AMOUNT1 numeric,
AMOUNT2 numeric,
COLUMN2 numeric,
COLUMN3 numeric
)

--导入数据到report50002
insert into sqmdb_cost.report50002
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as sname,
column1,amount1,amount2,column2,column3 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''50002''  order by report_id||''~''||smonth||''~''||period||''~''||entity_name_disp,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''50002''  order by 1')
as (
t varchar,
COLUMN1 numeric,
AMOUNT1 numeric,
AMOUNT2 numeric,
COLUMN2 numeric,
COLUMN3 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'


create table sqmdb_cost.report80054(
report_id varchar,
smonth varchar,
period varchar,
period_type varchar,
sname varchar,
stype varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric
)

--导入数据到report80054
insert into sqmdb_cost.report80054
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as period_type,tt[5] as sname,tt[6] as stype,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,
column15 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''80054'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''省本部展现'',''分省展现'',''本地网'') 
and lgl_type_name=''01合并''  order by report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''80054'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''省本部展现'',''分省展现'',''本地网'') 
and lgl_type_name=''01合并''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'


create table sqmdb_cost.report80053(
report_id varchar,
smonth varchar,
period varchar,
period_type varchar,
sname varchar,
stype varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric
)

--导入数据到report80053
insert into sqmdb_cost.report80053
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as period_type,tt[5] as sname,tt[6] as stype,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,
column15,column16,column17,column18,column19,column20,column21,column22,column23,column24,column25,column26,column27,
column28,column29,column30 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''80053'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''省本部展现'',''分省展现'',''本地网'') 
and lgl_type_name=''01合并''  order by report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''80053'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''省本部展现'',''分省展现'',''本地网'') 
and lgl_type_name=''01合并''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'


create table sqmdb_cost.report80040(
report_id varchar,
smonth varchar,
period varchar,
period_type varchar,
sname varchar,
stype varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric,
COLUMN31 numeric,
COLUMN32 numeric,
COLUMN33 numeric,
COLUMN34 numeric,
COLUMN35 numeric,
COLUMN36 numeric,
COLUMN37 numeric,
COLUMN38 numeric,
COLUMN39 numeric,
COLUMN40 numeric,
COLUMN41 numeric,
COLUMN42 numeric
)

--导入数据到report80040
insert into sqmdb_cost.report80040
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as period_type,tt[5] as sname,tt[6] as stype,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,
column15,column16,column17,column18,column19,column20,column21,column22,column23,column24,column25,column26,column27,
column28,column29,column30,column31,column32,column33,column34,column35,column36,column37,column38,column39,column40,
column41,column42 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''80040'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''省本部展现'',''分省展现'',''本地网'') 
and lgl_type_name=''01合并''  order by report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''80040'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''省本部展现'',''分省展现'',''本地网'') 
and lgl_type_name=''01合并''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric,
COLUMN31 numeric,
COLUMN32 numeric,
COLUMN33 numeric,
COLUMN34 numeric,
COLUMN35 numeric,
COLUMN36 numeric,
COLUMN37 numeric,
COLUMN38 numeric,
COLUMN39 numeric,
COLUMN40 numeric,
COLUMN41 numeric,
COLUMN42 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'

create table sqmdb_cost.cfg_grid_city(
segment1 varchar,
segment1_name varchar,
city_code varchar,
city_name varchar,
grid_code varchar,
grid_name varchar
)

---cfg_grid_city导入数据
insert into sqmdb_cost.cfg_grid_city 
select distinct segment1,segment1_name,
city_code,city_name,grid_code,grid_name FROM sqmdb_cost.jituanchengben where report_id='49' and ds_cn='归集+横向分摊+调差'  and prov_name='河北'  
and period_type='M'

create table sqmdb_cost.report49(
report_id varchar,
smonth varchar,
period varchar,
period_type varchar,
segment1 varchar,
segment1_name varchar,
prov_code varchar,
prov_name varchar,
city_code varchar,
city_name varchar,
grid_code varchar,
grid_name varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric,
COLUMN31 numeric,
COLUMN32 numeric,
COLUMN33 numeric,
COLUMN34 numeric,
COLUMN35 numeric,
COLUMN36 numeric,
COLUMN37 numeric,
COLUMN38 numeric,
COLUMN39 numeric,
COLUMN40 numeric,
COLUMN41 numeric,
COLUMN42 numeric,
COLUMN43 numeric,
COLUMN44 numeric,
COLUMN45 numeric,
COLUMN46 numeric,
COLUMN47 numeric,
COLUMN48 numeric,
COLUMN49 numeric,
COLUMN50 numeric,
COLUMN51 numeric,
COLUMN52 numeric,
COLUMN53 numeric,
COLUMN54 numeric,
COLUMN55 numeric,
COLUMN56 numeric,
COLUMN57 numeric,
COLUMN58 numeric,
COLUMN59 numeric,
COLUMN60 numeric,
COLUMN61 numeric,
COLUMN62 numeric,
COLUMN63 numeric,
COLUMN64 numeric,
COLUMN65 numeric,
COLUMN66 numeric,
COLUMN67 numeric,
COLUMN68 numeric,
COLUMN69 numeric,
COLUMN70 numeric,
COLUMN71 numeric,
COLUMN72 numeric,
COLUMN73 numeric,
COLUMN74 numeric,
COLUMN75 numeric,
COLUMN76 numeric,
COLUMN77 numeric,
COLUMN78 numeric,
COLUMN79 numeric,
COLUMN80 numeric,
COLUMN81 numeric,
COLUMN82 numeric,
COLUMN83 numeric,
COLUMN84 numeric,
COLUMN85 numeric,
COLUMN86 numeric,
COLUMN87 numeric,
COLUMN88 numeric,
COLUMN89 numeric,
COLUMN90 numeric,
COLUMN91 numeric,
COLUMN92 numeric,
COLUMN93 numeric,
COLUMN94 numeric,
COLUMN95 numeric,
COLUMN96 numeric,
COLUMN97 numeric,
COLUMN98 numeric,
COLUMN99 numeric,
COLUMN100 numeric,
COLUMN101 numeric,
COLUMN102 numeric,
COLUMN103 numeric,
COLUMN104 numeric,
COLUMN105 numeric,
COLUMN106 numeric,
COLUMN107 numeric,
COLUMN108 numeric,
COLUMN109 numeric,
COLUMN110 numeric,
COLUMN111 numeric,
COLUMN112 numeric,
COLUMN113 numeric,
COLUMN114 numeric,
COLUMN115 numeric,
COLUMN116 numeric,
COLUMN117 numeric,
COLUMN118 numeric,
COLUMN119 numeric,
COLUMN120 numeric
)

--导入数据到report49
insert into sqmdb_cost.report49
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as period_type,tt[5] as segment1,tt[6] as segment1_name,
tt[7] as prov_code,tt[8] as prov_name,tt[9] as city_code,tt[10] as city_name,tt[11] as grid_code,tt[12] as grid_name,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,
column15,column16,column17,column18,column19,column20,column21,column22,column23,column24,column25,column26,column27,
column28,column29,column30,column31,column32,column33,column34,column35,column36,column37,column38,column39,column40,
column41,column42,column43,column44,column45,column46,column47,column48,column49,column50,column51,column52,column53,
column54,column55,column56,column57,column58,column59,column60,column61,column62,column63,column64,column65,column66,
column67,column68,column69,column70,column71,column72,column73,column74,column75,column76,column77,column78,column79,
column80,column81,column82,column83,column84,column85,column86,column87,column88,column89,column90,column91,column92,
column93,column94,column95,column96,column97,column98,column99,column100,column101,column102,column103,column104,
column105,column106,column107,column108,column109,column110,column111,column112,column113,column114,column115,
column116,column117,column118,column119,column120 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||segment1||''~''||segment1_name
||''~''||prov_code||''~''||prov_name||''~''||city_code||''~''||city_name||''~''||grid_code||''~''||grid_name,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''49'' and ds_cn=''归集+横向分摊+调差''  and prov_name=''河北''  
 order by report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||segment1||''~''||segment1_name
||''~''||prov_code||''~''||prov_name||''~''||city_code||''~''||city_name||''~''||grid_code||''~''||grid_name,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''49'' and ds_cn=''归集+横向分摊+调差''  and prov_name=''河北''  
order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric,
COLUMN31 numeric,
COLUMN32 numeric,
COLUMN33 numeric,
COLUMN34 numeric,
COLUMN35 numeric,
COLUMN36 numeric,
COLUMN37 numeric,
COLUMN38 numeric,
COLUMN39 numeric,
COLUMN40 numeric,
COLUMN41 numeric,
COLUMN42 numeric,
COLUMN43 numeric,
COLUMN44 numeric,
COLUMN45 numeric,
COLUMN46 numeric,
COLUMN47 numeric,
COLUMN48 numeric,
COLUMN49 numeric,
COLUMN50 numeric,
COLUMN51 numeric,
COLUMN52 numeric,
COLUMN53 numeric,
COLUMN54 numeric,
COLUMN55 numeric,
COLUMN56 numeric,
COLUMN57 numeric,
COLUMN58 numeric,
COLUMN59 numeric,
COLUMN60 numeric,
COLUMN61 numeric,
COLUMN62 numeric,
COLUMN63 numeric,
COLUMN64 numeric,
COLUMN65 numeric,
COLUMN66 numeric,
COLUMN67 numeric,
COLUMN68 numeric,
COLUMN69 numeric,
COLUMN70 numeric,
COLUMN71 numeric,
COLUMN72 numeric,
COLUMN73 numeric,
COLUMN74 numeric,
COLUMN75 numeric,
COLUMN76 numeric,
COLUMN77 numeric,
COLUMN78 numeric,
COLUMN79 numeric,
COLUMN80 numeric,
COLUMN81 numeric,
COLUMN82 numeric,
COLUMN83 numeric,
COLUMN84 numeric,
COLUMN85 numeric,
COLUMN86 numeric,
COLUMN87 numeric,
COLUMN88 numeric,
COLUMN89 numeric,
COLUMN90 numeric,
COLUMN91 numeric,
COLUMN92 numeric,
COLUMN93 numeric,
COLUMN94 numeric,
COLUMN95 numeric,
COLUMN96 numeric,
COLUMN97 numeric,
COLUMN98 numeric,
COLUMN99 numeric,
COLUMN100 numeric,
COLUMN101 numeric,
COLUMN102 numeric,
COLUMN103 numeric,
COLUMN104 numeric,
COLUMN105 numeric,
COLUMN106 numeric,
COLUMN107 numeric,
COLUMN108 numeric,
COLUMN109 numeric,
COLUMN110 numeric,
COLUMN111 numeric,
COLUMN112 numeric,
COLUMN113 numeric,
COLUMN114 numeric,
COLUMN115 numeric,
COLUMN116 numeric,
COLUMN117 numeric,
COLUMN118 numeric,
COLUMN119 numeric,
COLUMN120 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'

create table sqmdb_cost.report50(
report_id varchar,
smonth varchar,
period varchar,
period_type varchar,
sname varchar,
stype varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric,
COLUMN31 numeric,
COLUMN32 numeric,
COLUMN33 numeric,
COLUMN34 numeric,
COLUMN35 numeric,
COLUMN36 numeric,
COLUMN37 numeric,
COLUMN38 numeric,
COLUMN39 numeric,
COLUMN40 numeric,
COLUMN41 numeric,
COLUMN42 numeric,
COLUMN43 numeric,
COLUMN44 numeric,
COLUMN45 numeric,
COLUMN46 numeric,
COLUMN47 numeric,
COLUMN48 numeric,
COLUMN49 numeric,
COLUMN50 numeric,
COLUMN51 numeric,
COLUMN52 numeric,
COLUMN53 numeric,
COLUMN54 numeric,
COLUMN55 numeric,
COLUMN56 numeric,
COLUMN57 numeric,
COLUMN58 numeric,
COLUMN59 numeric,
COLUMN60 numeric,
COLUMN61 numeric,
COLUMN62 numeric,
COLUMN63 numeric,
COLUMN64 numeric,
COLUMN65 numeric,
COLUMN66 numeric,
COLUMN67 numeric,
COLUMN68 numeric,
COLUMN69 numeric,
COLUMN70 numeric,
COLUMN71 numeric,
COLUMN72 numeric,
COLUMN73 numeric,
COLUMN74 numeric,
COLUMN75 numeric,
COLUMN76 numeric,
COLUMN77 numeric,
COLUMN78 numeric,
COLUMN79 numeric,
COLUMN80 numeric,
COLUMN81 numeric,
COLUMN82 numeric,
COLUMN83 numeric,
COLUMN84 numeric,
COLUMN85 numeric,
COLUMN86 numeric,
COLUMN87 numeric,
COLUMN88 numeric,
COLUMN89 numeric,
COLUMN90 numeric,
COLUMN91 numeric,
COLUMN92 numeric,
COLUMN93 numeric,
COLUMN94 numeric,
COLUMN95 numeric,
COLUMN96 numeric,
COLUMN97 numeric,
COLUMN98 numeric,
COLUMN99 numeric,
COLUMN100 numeric,
COLUMN101 numeric,
COLUMN102 numeric,
COLUMN103 numeric,
COLUMN104 numeric,
COLUMN105 numeric,
COLUMN106 numeric,
COLUMN107 numeric,
COLUMN108 numeric,
COLUMN109 numeric,
COLUMN110 numeric,
COLUMN111 numeric,
COLUMN112 numeric,
COLUMN113 numeric,
COLUMN114 numeric,
COLUMN115 numeric,
COLUMN116 numeric,
COLUMN117 numeric,
COLUMN118 numeric,
COLUMN119 numeric,
COLUMN120 numeric
)

--导入数据到report50,这次没有加上smonth过滤，以后增量增加的时候要加上
insert into sqmdb_cost.report50
select tt[1] as report_id,tt[2] as smonth,tt[3] as period,tt[4] as period_type,tt[5] as sname,tt[6] as stype,
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,
column15,column16,column17,column18,column19,column20,column21,column22,column23,column24,column25,column26,column27,
column28,column29,column30,column31,column32,column33,column34,column35,column36,column37,column38,column39,column40,
column41,column42,column43,column44,column45,column46,column47,column48,column49,column50,column51,column52,column53,
column54,column55,column56,column57,column58,column59,column60,column61,column62,column63,column64,column65,column66,
column67,column68,column69,column70,column71,column72,column73,column74,column75,column76,column77,column78,column79,
column80,column81,column82,column83,column84,column85,column86,column87,column88,column89,column90,column91,column92,
column93,column94,column95,column96,column97,column98,column99,column100,column101,column102,column103,column104,
column105,column106,column107,column108,column109,column110,column111,column112,column113,column114,column115,
column116,column117,column118,column119,column120 from 
(select regexp_split_to_array(t,'~') as tt,* from crosstab(
'select distinct report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,
sort_id,amount as amount FROM sqmdb_cost.jituanchengben_e where report_id=''50'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''北方省汇总展现'',''分省展现'') 
and lgl_type_name=''01合并''  order by report_id||''~''||smonth||''~''||period||''~''||period_type||''~''||entity_name_disp||''~''||sch_name,sort_id',
'select distinct sort_id FROM sqmdb_cost.jituanchengben_e where report_id=''50'' and ds_cn=''归集+横向分摊+调差'' and sch_name in (''北方省汇总展现'',''分省展现'') 
and lgl_type_name=''01合并''  order by 1')
as (
t varchar,
COLUMN1 numeric,
COLUMN2 numeric,
COLUMN3 numeric,
COLUMN4 numeric,
COLUMN5 numeric,
COLUMN6 numeric,
COLUMN7 numeric,
COLUMN8 numeric,
COLUMN9 numeric,
COLUMN10 numeric,
COLUMN11 numeric,
COLUMN12 numeric,
COLUMN13 numeric,
COLUMN14 numeric,
COLUMN15 numeric,
COLUMN16 numeric,
COLUMN17 numeric,
COLUMN18 numeric,
COLUMN19 numeric,
COLUMN20 numeric,
COLUMN21 numeric,
COLUMN22 numeric,
COLUMN23 numeric,
COLUMN24 numeric,
COLUMN25 numeric,
COLUMN26 numeric,
COLUMN27 numeric,
COLUMN28 numeric,
COLUMN29 numeric,
COLUMN30 numeric,
COLUMN31 numeric,
COLUMN32 numeric,
COLUMN33 numeric,
COLUMN34 numeric,
COLUMN35 numeric,
COLUMN36 numeric,
COLUMN37 numeric,
COLUMN38 numeric,
COLUMN39 numeric,
COLUMN40 numeric,
COLUMN41 numeric,
COLUMN42 numeric,
COLUMN43 numeric,
COLUMN44 numeric,
COLUMN45 numeric,
COLUMN46 numeric,
COLUMN47 numeric,
COLUMN48 numeric,
COLUMN49 numeric,
COLUMN50 numeric,
COLUMN51 numeric,
COLUMN52 numeric,
COLUMN53 numeric,
COLUMN54 numeric,
COLUMN55 numeric,
COLUMN56 numeric,
COLUMN57 numeric,
COLUMN58 numeric,
COLUMN59 numeric,
COLUMN60 numeric,
COLUMN61 numeric,
COLUMN62 numeric,
COLUMN63 numeric,
COLUMN64 numeric,
COLUMN65 numeric,
COLUMN66 numeric,
COLUMN67 numeric,
COLUMN68 numeric,
COLUMN69 numeric,
COLUMN70 numeric,
COLUMN71 numeric,
COLUMN72 numeric,
COLUMN73 numeric,
COLUMN74 numeric,
COLUMN75 numeric,
COLUMN76 numeric,
COLUMN77 numeric,
COLUMN78 numeric,
COLUMN79 numeric,
COLUMN80 numeric,
COLUMN81 numeric,
COLUMN82 numeric,
COLUMN83 numeric,
COLUMN84 numeric,
COLUMN85 numeric,
COLUMN86 numeric,
COLUMN87 numeric,
COLUMN88 numeric,
COLUMN89 numeric,
COLUMN90 numeric,
COLUMN91 numeric,
COLUMN92 numeric,
COLUMN93 numeric,
COLUMN94 numeric,
COLUMN95 numeric,
COLUMN96 numeric,
COLUMN97 numeric,
COLUMN98 numeric,
COLUMN99 numeric,
COLUMN100 numeric,
COLUMN101 numeric,
COLUMN102 numeric,
COLUMN103 numeric,
COLUMN104 numeric,
COLUMN105 numeric,
COLUMN106 numeric,
COLUMN107 numeric,
COLUMN108 numeric,
COLUMN109 numeric,
COLUMN110 numeric,
COLUMN111 numeric,
COLUMN112 numeric,
COLUMN113 numeric,
COLUMN114 numeric,
COLUMN115 numeric,
COLUMN116 numeric,
COLUMN117 numeric,
COLUMN118 numeric,
COLUMN119 numeric,
COLUMN120 numeric
)
) a where tt[2]='202207' and tt[3] like '%2022-07%'

create table sqmdb_cost.station2(
station_sid numeric,
station_sname varchar,
city_code numeric,
area_code numeric,
station_type varchar,
baidu_longitude numeric,
baidu_latitude numeric,
tower_code varchar,
property_type varchar,
property_unit varchar);

create table sqmdb_cost.dict(
dictid varchar,
dicttext varchar);

select siteinfo.city_name,
siteinfo.district_name,
station.station_sname,
station.station_sid,
station.station_type,
dict.dicttext

--基于物理站
select siteinfo.*,rru.room,roomid1.room_sid as rru_room,room1.station_sid as rru_station_id,room1.room_sname,room1.room_type,room1.property_type,dict1.dicttext,
bbu.*,roomid2.room_sid as bbu_room,room2.station_sid as bbu_station_id,
room2.room_sname,room2.room_type,room2.property_type,dict2.dicttext,
station.baidu_longitude,station.baidu_latitude,
station.station_sname,station.station_type,station.property_type
from sqmdb_cost.siteinfo siteinfo 
left join (select *,regexp_split_to_table(cell_oid,'\|') as cell_id from sqmdb_cost.rru) rru
on '127.'||siteinfo.enbid||'.'||siteinfo.cellid = rru.cell_id
left join sqmdb_cost.roomid roomid1 on roomid1.room_id=rru.room
left join sqmdb_cost.room room1 on room1.room_sid=roomid1.room_sid
left join sqmdb_cost.bbu bbu on bbu.site_id='127.'||siteinfo.enbid
left join sqmdb_cost.roomid roomid2 on roomid2.room_id=bbu.room
left join sqmdb_cost.room room2 on room2.room_sid=roomid2.room_sid
left join sqmdb_cost.station2 station on station.station_sid=room2.station_sid
left join sqmdb_cost.dict dict1 on dict1.dictid=room1.property_type
left join sqmdb_cost.dict dict2 on dict2.dictid=room2.property_type