chengben/Scripts/12_13.sql

--端口数
select local_net_name,area_name,sum(ftth_port_count) as ftth_port_count
from sqmdb_cost.kuandai_ftth
where sdate='2022-04-02 00:00:00'
group by local_net_name,area_name 

select sdate,local_net_name,area_name,areal_type_name,ftth_port_count
from sqmdb_cost.kuandai_ftth where local_net_name ='石家庄' and area_name='平山'

--12、单端口网运成本（月）
--13、单端口能耗成本（月）
--全省
--create table sqmdb_cost.rpt_dport_cost_prov as
select 
t1.smonth,t1.sname,t1.cost_total,t1.cost_nenghao,t2.port_inuse,
t1.cost_total/t2.port_inuse/3 as cost_port,
t1.cost_nenghao/t2.port_inuse/3 as cost_port_nenghao
from 
(select '202203' as smonth,sname,stype,
sum(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-column108-column109-column110-column111-column112-column113-column114)/10000 as cost_total,
sum(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115-column5-column19-column108)/10000 as cost_nenghao
from sqmdb_cost.report50 r
where period_type='M' and sname='河北'
and smonth between '202201' and '202203' group by sname,stype) t1
join
(select sname,port_inuse from sqmdb_cost.jt_number_b10 where sname='河北' and smonth='202203') t2
on t1.sname=t2.sname



---地市
--create table sqmdb_cost.rpt_dport_cost_city as
select t1.smonth,t1.city_sname,t1.cost_total,t1.cost_nenghao,t2.port_inuse,
t1.cost_total/t2.port_inuse/4 as cost_port,
t1.cost_nenghao/t2.port_inuse/4 as cost_port_nenghao
from 
(select a.smonth,dg.city_sname,a.cost_total,a.cost_nenghao from
(select '202204' as smonth,sname,stype,
sum(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-column108-column109-column110-column111-column112-column113-column114)/10000 as cost_total,
sum(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115-column5-column19-column108)/10000 as cost_nenghao
from sqmdb_cost.report50 r
where stype ='北方省汇总展现'
and period_type='M'
and sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and smonth between '202201' and '202204' group by sname,stype) a
join 
(select distinct city_sname,city_pro from sqmdb_cost.dict_grid) dg
on a.sname=dg.city_pro) t1
join
(select city_name,port_inuse from sqmdb_cost.jt_number_city where month_id='202204') t2
on t1.city_sname=t2.city_name



--单端口能耗、网运成本--网格
--create table sqmdb_cost.rpt_dport_cost_grid_1 as
select t1.smonth,dcg.city_name,dcg.grid_name,t1.cost_total,t1.cost_nenghao,t2.ftth_port_sts_count,
t1.cost_total/t2.ftth_port_sts_count/4 as cost_port,
t1.cost_nenghao/t2.ftth_port_sts_count/4 as cost_port_nenghao,
dcg.city_code,dcg.area_code
from 
(select jt.smonth,jt.cost_total,jt.cost_nenghao,dgc.grid_code from
(select smonth,period_type,segment1_name,city_code,city_name_short(city_name) as city_name,grid_code,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-column108-column109-column110-column111-column112-column113-column114)/10000 as cost_total,
(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115-column5-column19-column108)/10000 as cost_nenghao
from sqmdb_cost.report49
where period_type='Y' and grid_name!='合计' and smonth='202204') jt
left join sqmdb_cost.dict_grid_code dgc on jt.city_name=dgc.city_name and trim(jt.grid_name)=trim(dgc.grid_name)
where dgc.grid_code is not null) t1
join
(select a.ftth_port_sts_count,dgc1.grid_code from 
(select sdate,city_name_short(local_net_name) as city_name,area_name,sum(ftth_port_sts_count)/10000 as ftth_port_sts_count
from sqmdb_cost.kuandai_ftth 
where areal_type_name!='小计' and sdate='2022-04-30 00:00:00'
group by sdate,city_name_short(local_net_name),area_name) a
left join sqmdb_cost.dict_grid_code dgc1 on a.city_name=dgc1.city_name and trim(a.area_name)=trim(dgc1.grid_name)) t2
on t1.grid_code=t2.grid_code
left join sqmdb_cost.dict_code_grid dcg on t1.grid_code=dcg.grid_code