chengben/Scripts/大屏_比别人.sql

--02、网运成本占收比（省内口径）
--chengben_other_1_city_b10_202203.csv
select t1.*,
case 
when sname='北京' then 110000
when sname='天津' then 120000
when sname='河北' then 130000
when sname='山西' then 140000
when sname='内蒙古' then 150000
when sname='辽宁' then 210000
when sname='吉林' then 220000
when sname='黑龙江' then 230000
when sname='山东' then 370000
when sname='河南' then 410000
else t2.city_code*100 end as city_code 
from
(select smonth,stype,sname,cost_total,income,
cost_total/income as cost_income_rate
from sqmdb_cost.chengben_shengnei
--where stype='北10'
--where stype='地市'
where smonth='202203') t1
left join
(select distinct city_code,city_pro from sqmdb_cost.dict_grid) t2
on t1.sname=t2.city_pro



--14、单载频载扇能耗（月）
---地市
---chengben_other_2_city_202203.csv
select fm.smonth,fz.city_sname,fz.cost_yw,fz.cost_hexin,
(fz.cost_yw+fz.cost_hexin) as cost_total,
fm.zps_num_with5g,
fm.zps_num_no5g,
case when fm.zps_num_with5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_with5g end as cost_zps_with5g,
case when fm.zps_num_no5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_no5g end as cost_zps_no5g,
fz.city_code*100 as city_code
from
(select a.smonth,a.sname,dg.city_sname,dg.city_code,a.cost_yw,a.cost_hexin from
(select smonth,sname,
(column4+column5) as cost_yw,
column19 as cost_hexin
from sqmdb_cost.report50
where period_type='M' 
and sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and smonth='202203') a
join (select distinct city_code,city_sname,city_pro from sqmdb_cost.dict_grid) dg 
on a.sname=dg.city_pro) fz
join
(select z1.smonth,z1.city_name,
sum(round((coalesce(z1.zps_num_234g,0)+coalesce(z2.zps_num_234g,0)+coalesce(z1.zps_num_5g, 0)+coalesce(z2.zps_num_5g,0))/2)) as zps_num_with5g,
sum(round((coalesce(z1.zps_num_234g,0)+coalesce(z2.zps_num_234g,0))/2)) as zps_num_no5g
from
(select * from sqmdb_cost.zpzs where smonth='202203') z1
left join
(select * from sqmdb_cost.zpzs where smonth='202112') z2
on z1.city_name=z2.city_name and z1.grid_name=z2.grid_name group by z1.smonth,z1.city_name) fm
on fz.city_sname=fm.city_name
order by fz.city_code


---网格新（49直接关联载频载扇里的网格名称）
---chengben_other_2_grid_202203.csv
select fm.smonth,fz.city_name,fz.grid_name,fz.cost_yw,fz.cost_hexin,
(fz.cost_yw+fz.cost_hexin) as cost_total,
fm.zps_num_with5g,
fm.zps_num_no5g,
case when fm.zps_num_with5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_with5g end as cost_zps_with5g,
case when fm.zps_num_no5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_no5g end as cost_zps_no5g,
dg.city_code*100 as city_code,
dg2.area_code
from
(select smonth,city_name,grid_name,
(column4+column5) as cost_yw,
column19 as cost_hexin
from sqmdb_cost.report49
where period_type='M' 
and smonth='202203') fz
join
(select z1.smonth,'河北_'||z1.city_name as city_name,z1.grid_name,
round((coalesce(z1.zps_num_234g,0)+coalesce(z2.zps_num_234g,0)+coalesce(z1.zps_num_5g, 0)+coalesce(z2.zps_num_5g,0))/2) as zps_num_with5g,
round((coalesce(z1.zps_num_234g,0)+coalesce(z2.zps_num_234g,0))/2) as zps_num_no5g
from
(select * from sqmdb_cost.zpzs where smonth='202203') z1
left join
(select * from sqmdb_cost.zpzs where smonth='202112') z2
on z1.city_name=z2.city_name and z1.grid_name=z2.grid_name) fm
on fz.city_name=fm.city_name and trim(fz.grid_name)=trim(fm.grid_name)
left join
(select distinct city_code,city_sname,grid_city_name from sqmdb_cost.dict_grid) dg
on fz.city_name=dg.grid_city_name
left join
(select distinct area_code,grid_name from sqmdb_cost.dict_grid) dg2
on trim(fz.grid_name)=trim(dg2.grid_name)
where fz.grid_name!='合计'
order by dg.city_code,dg2.area_code



--13、单端口能耗成本（月）
---地市
---chengben_other_3_city_202203.csv
select ta1.*,ta2.city_code*100 as city_code from
(select t1.smonth,t1.city_name,t1.cost_nenghao,t2.ftth_port_count,
t1.cost_nenghao/t2.ftth_port_count as cost_port_nenghao
from 
(select distinct a.smonth,dg.city_name,a.cost_nenghao from
(select smonth,sname,stype,
(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4)-column5-column19-column108 as cost_nenghao
from sqmdb_cost.report50 r
where stype ='北方省汇总展现'
and sname in ('河北省本部','石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and smonth='202203') a
join sqmdb_cost.dict_grid dg
on a.sname=dg.city_pro) t1
join
(select distinct dg.city_name,a.ftth_port_count from 
(select sdate,local_net_name,sum(ftth_port_count) as ftth_port_count
from sqmdb_cost.kuandai_ftth 
where areal_type_name='小计' and sdate='2022-04-02 00:00:00'
group by sdate,local_net_name) a
join sqmdb_cost.dict_grid dg 
on a.local_net_name=dg.grid_city_kd) t2
on t1.city_name=t2.city_name) ta1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) ta2
on ta1.city_name=ta2.city_name
order by ta2.city_code


---网格
---chengben_other_3_grid_202203.csv
select ta1.*,ta2.city_code*100 as city_code,ta3.area_code from
(select t1.smonth,t1.city_name,t1.area_name,t1.cost_nenghao,t2.ftth_port_count,
t1.cost_nenghao/t2.ftth_port_count as cost_port_nenghao
from 
(select fz.smonth,dg.city_name,dg.area_name,fz.cost_nenghao from 
(select smonth,period_type,segment1_name,city_code,city_name,grid_code,grid_name,
(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4)-column5-column19-column108 as cost_nenghao
from sqmdb_cost.report49
where period_type='M' and grid_name!='合计' and smonth='202203') fz
join sqmdb_cost.dict_grid dg
on fz.city_code=dg.grid_city_code::varchar and fz.grid_code=dg.grid_code::varchar) t1
join
(select dg.city_name,dg.area_name,a.ftth_port_count from 
(select sdate,local_net_name,area_name,sum(ftth_port_count) as ftth_port_count
from sqmdb_cost.kuandai_ftth 
where areal_type_name!='小计' and sdate='2022-04-02 00:00:00'
group by sdate,local_net_name,area_name) a
join sqmdb_cost.dict_grid dg 
on a.local_net_name=dg.grid_city_kd and a.area_name=dg.grid_name_kd) t2
on t1.city_name=t2.city_name and t1.area_name=t2.area_name) ta1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) ta2
on ta1.city_name=ta2.city_name
left join 
(select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) ta3
on ta1.city_name=ta3.city_name and ta1.area_name=ta3.area_name
order by ta2.city_code,ta3.area_code



--16、单站铁塔租赁费（月）
---北10数据（select * from sqmdb_cost.report50016）
---chengben_other_4_b10_202203.csv
select smonth,sname,column1 as tower_cost_avg_b10,
case 
when sname='北京' then 110000
when sname='天津' then 120000
when sname='河北' then 130000
when sname='山西' then 140000
when sname='内蒙古' then 150000
when sname='辽宁' then 210000
when sname='吉林' then 220000
when sname='黑龙江' then 230000
when sname='山东' then 370000
when sname='河南' then 410000
end as prov_code 
from sqmdb_cost.report50016
where sname in ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
and smonth='202203'
order by case
when sname='北京' then 1
when sname='天津' then 2
when sname='河北' then 3
when sname='山西' then 4
when sname='内蒙古' then 5
when sname='辽宁' then 6
when sname='吉林' then 7
when sname='黑龙江' then 8
when sname='山东' then 9
when sname='河南' then 10
end


---网格
---chengben_other_4_grid_202203.csv
select t1.*,
t2.city_code*100 as city_code,
t3.area_code
from
(select sdate,city_operator,area_operator,
sum(total_cost) as tower_cost_total,
count(*) as tower_num,
sum(total_cost)/count(*) as tower_cost_avg_area
from (
select sdate,city_operator,area_operator,site_addr_id,sum(total_cost) as total_cost from 
sqmdb_cost.tower2 t group by sdate,city_operator,area_operator,site_addr_id) tmp group by sdate,city_operator,area_operator) t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2
on t1.city_operator=t2.city_name
left join 
(select distinct city_code,city_name,area_code,area_tower from sqmdb_cost.dict_grid) t3
on t1.city_operator=t3.city_name and t1.area_operator=t3.area_tower
where t1.sdate='202203'
order by t2.city_code,t3.area_code