chengben/Scripts/01_08_10_19.sql

--01、网运成本占收比（集团口径）
---地市
select 
a.smonth as 月份,
a.sname as 公司,
a.stype as 数据类型,
a.cost_total as 费用合计,
a.cost_total2 as 费用合计含账面人工,
a.cost_total2-a.cost_people as 费用合计去账面人工,
a.cost_people as 账面人工成本,
a.cost_nenghao as 网络能耗,
a.cost_fix as 修理维护费,
a.cost_wireless as 无线网成本,
a.cost_rental_3rd as 第三方租赁费,
a.cost_rental_tower as 铁塔租赁费,
b.income_year as 本年收入累计,
b.stype as 本年收入累计类型,
case when b.income_year=0 then null else a.cost_total/b.income_year end as 网运成本占收比（集团口径）,
case when b.income_year=0 then null else a.cost_nenghao/b.income_year end as 网络能耗占收比,
case when b.income_year=0 then null else a.cost_fix/b.income_year end as 修理维护费占收比,
case when b.income_year=0 then null else a.cost_wireless/b.income_year end as 无线成本占收比
from
(select smonth,sname,stype,
(column10+column23+column31+column45+column53+column61+column67+column74+column81+column90+column103+column109+column116+column6+column20+column28+column42+column50+column58+column71+column79+column87+column101+column7+column21+column29+column43+column51+column59+column65+column72+column80+column88+column96+column102+column11+column24+column32+column46+column54+column62+column68+column75+column82+column91+column104+column9+column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4+column2+column3+column12+column15+column16+column25+column35+column39+column38+column47+column55+column63+column76+column83+column92+column105+column111+column118+column13+column14+column36+column37+column95+column112+column113+column33+column34+column17+column8+column22+column30+column44+column52+column60+column66+column73+column85+column89+column97+column106+column110+column117+column99+column120)/10000 as cost_total,
(column115+column116+column117+column118+column119+column108+column109+column110+column111+column112+column113+column114+column100+column101+column102+column103+column104+column105+column106+column107+column99+column94+column95+column96+column97+column98+column86+column87+column88+column89+column90+column91+column92+column93+column78+column79+column80+column81+column82+column83+column84+column85+column70+column71+column72+column73+column74+column75+column76+column77+column65+column66+column67+column68+column69+column57+column58+column59+column60+column61+column62+column63+column64+column49+column50+column51+column52+column53+column54+column55+column56+column41+column42+column43+column44+column45+column46+column47+column48+column27+column28+column29+column30+column31+column32+column33+column34+column35+column36+column37+column38+column39+column40+column19+column20+column21+column22+column23+column24+column25+column26+column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18+column120)/10000 as cost_total2,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18-column18)/10000 as cost_wireless,
(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4)/10000 as cost_nenghao,
(column10+column23+column31+column45+column53+column61+column67+column74+column81+column90+column103+column109+column116+column6+column20+column28+column42+column50+column58+column71+column79+column87+column101+column7+column21+column29+column43+column51+column59+column65+column72+column80+column88+column96+column102+column11+column24+column32+column46+column54+column62+column68+column75+column82+column91+column104+column9)/10000 as cost_fix,
(column12+column15+column16)/10000 as cost_rental_3rd,
(column2+column3)/10000 as cost_rental_tower,
(column119+column114+column107+column98+column93+column84+column77+column69+column64+column56+column48+column40+column26+column18)/10000 as cost_people
from sqmdb_cost.report50 where stype ='北方省汇总展现') a
left join
(select report_id,smonth,sname,stype,column4 as income_year from sqmdb_cost.report80040) b
on a.smonth=b.smonth and a.sname=b.sname
where 
--a.sname like '%河北%'
a.sname in ('北方10省合计','河北省本部','石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北','廊坊数据分公司_河北')
--a.sname in ('河北省本部')
and a.smonth='202202'


--1、网运成本占收比（集团口径）
--网格（网格数据缺少分母）
select 
a.smonth as 月份,
a.segment1_name as 公司,
a.city_name as 地市,
a.grid_name as 网格,
a.cost_total as 费用合计,
a.cost_total2 as 费用合计含账面人工,
a.cost_total2-a.cost_people as 费用合计去账面人工,
a.cost_people as 账面人工成本,
a.cost_bai as 网络能耗,
a.cost_fix as 修理维护费,
a.cost_wireless as 无线网成本,
a.cost_rental_3rd as 第三方租赁费,
a.cost_rental_tower as 铁塔租赁费,
b.income_year as 本年收入累计,
case when b.income_year=0 then null else a.cost_total/b.income_year end as 网运成本占收比（集团口径）,
case when b.income_year=0 then null else a.cost_bai/b.income_year*100 end as 网络能耗占收比,
case when b.income_year=0 then null else a.cost_fix/b.income_year end as 修理维护费占收比,
case when b.income_year=0 then null else a.cost_wireless/b.income_year end as 无线成本占收比
from
(select smonth,segment1_name,city_name,grid_name,
(column10+column23+column31+column45+column53+column61+column67+column74+column81+column90+column103+column109+column116+column6+column20+column28+column42+column50+column58+column71+column79+column87+column101+column7+column21+column29+column43+column51+column59+column65+column72+column80+column88+column96+column102+column11+column24+column32+column46+column54+column62+column68+column75+column82+column91+column104+column9+column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4+column2+column3+column12+column15+column16+column25+column35+column39+column38+column47+column55+column63+column76+column83+column92+column105+column111+column118+column13+column14+column36+column37+column95+column112+column113+column33+column34+column17+column8+column22+column30+column44+column52+column60+column66+column73+column85+column89+column97+column106+column110+column117+column99+column120)/10000 as cost_total,
(column115+column116+column117+column118+column119+column108+column109+column110+column111+column112+column113+column114+column100+column101+column102+column103+column104+column105+column106+column107+column99+column94+column95+column96+column97+column98+column86+column87+column88+column89+column90+column91+column92+column93+column78+column79+column80+column81+column82+column83+column84+column85+column70+column71+column72+column73+column74+column75+column76+column77+column65+column66+column67+column68+column69+column57+column58+column59+column60+column61+column62+column63+column64+column49+column50+column51+column52+column53+column54+column55+column56+column41+column42+column43+column44+column45+column46+column47+column48+column27+column28+column29+column30+column31+column32+column33+column34+column35+column36+column37+column38+column39+column40+column19+column20+column21+column22+column23+column24+column25+column26+column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18+column120)/10000 as cost_total2,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18-column18)/10000 as cost_wireless,
(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4)/10000 as cost_bai,
(column10+column23+column31+column45+column53+column61+column67+column74+column81+column90+column103+column109+column116+column6+column20+column28+column42+column50+column58+column71+column79+column87+column101+column7+column21+column29+column43+column51+column59+column65+column72+column80+column88+column96+column102+column11+column24+column32+column46+column54+column62+column68+column75+column82+column91+column104+column9)/10000 as cost_fix,
(column12+column15+column16)/10000 as cost_rental_3rd,
(column2+column3)/10000 as cost_rental_tower,
(column119+column114+column107+column98+column93+column84+column77+column69+column64+column56+column48+column40+column26+column18)/10000 as cost_people
from sqmdb_cost.report49) a
left join
--网格数据缺少分母
(select report_id,smonth,sname,stype,column4 as income_year from sqmdb_cost.report80040) b
on a.smonth=b.smonth and a.sname=b.sname