chengben/Scripts/存储过程表.sql

--网格端口数天粒度汇聚月粒度，每月初执行（不要执行2022年4月之前的月份，前期数据没有，执行会导致现有表前期月份数据删除，如需恢复，用202204的数据改日期后重新导入）
--call sqmdb_cost.kuandai_ftth_month('202207');

--省内成本入库后计算当月值
call sqmdb_cost.chengben_shengnei_m('202207');
--端口数、用户数、皮长公里中间表
call sqmdb_cost.jt_number_b10('202207');--每次执行去年同期和本月
call sqmdb_cost.jt_number_city('202207');
--更新铁塔成本中心字典（从铁塔预提表生成字典数据）
call sqmdb_cost.update_dict_tower_cbzx();
--单载频载扇能耗
call sqmdb_cost.rpt_dzpzs_cost_b10('202207');--每次执行去年同期和本月
call sqmdb_cost.rpt_dzpzs_cost_city('202207');
call sqmdb_cost.rpt_dzpzs_cost_grid('202207');
call sqmdb_cost.rpt_dzpzs_cost_prov('202207');
--单端口能耗
call sqmdb_cost.rpt_dport_cost_b10('202207');--每次执行去年同期和本月
call sqmdb_cost.rpt_dport_cost_city('202207');
call sqmdb_cost.rpt_dport_cost_grid('202207');
--铁塔单站租赁费
call sqmdb_cost.rpt_tower_cost_city('202207');
call sqmdb_cost.rpt_tower_cost_grid('202207');

--6个报表
--call sqmdb_cost.rpt_baobiao_1_dzpz('202207');
--call sqmdb_cost.rpt_baobiao_2_ddk('202207');
--call sqmdb_cost.rpt_baobiao_3_dzzl('202207');
--call sqmdb_cost.rpt_baobiao_4_cgzf('202207');
--call sqmdb_cost.rpt_baobiao_5_cgdf('202207');
--call sqmdb_cost.rpt_baobiao_6_cgnh('202207');
call sqmdb_cost.rpt_baobiao_base('202207');

--校验-只保留当期数据！！！
--有合同无局站
--call sqmdb_cost.err_has_ht_no_station('202207');
--有能耗无局站
--call sqmdb_cost.err_has_nh_no_station('202207');
--有铁塔订单无局站
--call sqmdb_cost.err_has_tw_no_station('202207');
--又有铁塔又有第三方（偶尔慢，执行200s左右）
--call sqmdb_cost.err_station_has_tw_3rd('202207');
call sqmdb_cost.err_base('202207');



--rpt_dzpzs_cost_prov
--单载频载扇能耗-全省
--create table sqmdb_cost.rpt_dzpzs_cost_prov as
select fz.smonth,fz.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/4 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/4 end as cost_zps_no5g
from
(select '202204' as smonth,sname,
sum(column4+column5)/10000 as cost_yw,
sum(column19)/10000 as cost_hexin
from sqmdb_cost.report50
where period_type='M' and sname ='河北' and smonth between '202201' and '202204' group by sname) fz
join
(select a.city_name,
(a.zps_total+b.zps_total)/10000/2 as zps_num_with5g,
(a.zps_total_no5g+b.zps_total_no5g)/10000/2 as zps_num_no5g
from
(select '河北' as city_name,(zps_total-coalesce(zps_5g,0)) as zps_total_no5g,zps_total from sqmdb_cost.zpzs_city where smonth='202112' and city_name='全省')a
join
(select '河北' as city_name,(zps_total-coalesce(zps_5g,0)) as zps_total_no5g,zps_total from sqmdb_cost.zpzs_city where smonth='202204' and city_name='全省')b
on a.city_name=b.city_name) fm
on fz.sname=fm.city_name

--rpt_dzpzs_cost_city
--单载频载扇能耗-地市
--create table sqmdb_cost.rpt_dzpzs_cost_city as
select fz.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/4 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/4 end as cost_zps_no5g,
fz.city_code*100 as city_code
from
(select a.smonth,dg.city_sname,dg.city_code,a.cost_yw,a.cost_hexin from
(select '202204' as smonth,sname,
sum(column4+column5)/10000 as cost_yw,
sum(column19)/10000 as cost_hexin
from sqmdb_cost.report50
where period_type='M' 
and sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and smonth between '202201' and '202204' group by sname) 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 
city_name,
zps_total_with5g as zps_num_with5g,
zps_total_no5g as zps_num_no5g 
from sqmdb_cost.jt_number_city where month_id='202204') fm
on fz.city_sname=fm.city_name
order by fz.city_code


--单载频载扇能耗--网格
--drop table sqmdb_cost.rpt_dzpzs_cost_grid_1
--create table sqmdb_cost.rpt_dzpzs_cost_grid_1 as
select fm.smonth,dcg.city_name,dcg.grid_name,fz.cost_yw,fz.cost_hexin,
(fz.cost_yw+fz.cost_hexin) as cost_total,
fm.zps_num_with5g,
case when fm.zps_num_with5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_with5g/4 end as cost_zps_with5g,
dcg.city_code,dcg.grid_code
from
(select jt.smonth,jt.city_name,jt.grid_name,jt.cost_yw,jt.cost_hexin,dgc.grid_code from 
(select smonth,city_name_short(city_name) as city_name,grid_name,(column4+column5) as cost_yw,column19 as cost_hexin
from sqmdb_cost.report49 where period_type='Y' 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) fz
join
(select z1.smonth,z1.city_name as city_name,z1.grid_name,
z2.zps_num_total as zps_num_total_ly,
z1.zps_num_total as zps_num_total_cm,
avg_num(z1.zps_num_total,z2.zps_num_total) as zps_num_with5g,
dgc1.grid_code 
from
(select * from sqmdb_cost.zpzs where smonth='202204') z1
 join
(select * from sqmdb_cost.zpzs where smonth='202112') z2
on trim(z1.city_name)=trim(z2.city_name) and trim(z1.grid_name)=trim(z2.grid_name)
left join sqmdb_cost.dict_grid_code dgc1 on trim(z1.city_name)=trim(dgc1.city_name) and trim(z1.grid_name)=trim(dgc1.grid_name)
where dgc1.grid_code is not null) fm
on fz.grid_code=fm.grid_code
left join sqmdb_cost.dict_code_grid dcg on fz.grid_code=dcg.grid_code 



--网格端口数：天粒度城区、县城、农村按区域汇总，当月天平均值
--create table sqmdb_cost.kuandai_ftth_month as 
select
'202204' as smonth,
city_name_short(local_net_name) as city_name,area_name,
avg(ftth_port_count) as ftth_port_count,
avg(ftth_port_sts_count) as ftth_port_sts_count
from 
(select 
sdate,local_net_name,area_name,
sum(ftth_port_count) as ftth_port_count,
sum(ftth_port_sts_count) as ftth_port_sts_count
from sqmdb_cost.kuandai_ftth kf 
where areal_type_name!='小计'
and sdate>=date_trunc('month',to_date('202204','yyyymm'))
and sdate<date_trunc('month',to_date('202204','yyyymm') + interval '1' month)
group by sdate,local_net_name,area_name) day_area_sum
group by local_net_name,area_name 


--单端口能耗、网运成本--网格
--核查网格单端口能耗和地市单端口能耗差异问题（t1表去掉where条件作为分子，t2表作为分母，分别汇总算地市值，单独计算网格值）
--drop table sqmdb_cost.rpt_dport_cost_grid_1
--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.grid_code
from 
(select jt.smonth,jt.city_name,jt.grid_name,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.city_name,a.area_name,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


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.grid_code
from 
(select jt.smonth,jt.city_name,jt.grid_name,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.city_name,a.area_name,a.ftth_port_sts_count,dgc1.grid_code from 
(select p2.smonth,p2.city_name,p2.area_name,
avg_num(p1.ftth_port_sts_count,p2.ftth_port_sts_count)/10000 as ftth_port_sts_count from
(select * from sqmdb_cost.kuandai_ftth_month where smonth='202112')p1
join
(select * from sqmdb_cost.kuandai_ftth_month where smonth='202204')p2
on p1.city_name=p2.city_name and p1.area_name=p2.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


--以下是6个报表

--1 单载频载扇能耗
---网格报表
--drop table sqmdb_cost.rpt_baobiao_1_dzpz
--create table sqmdb_cost.rpt_baobiao_1_dzpz as 
select
grid.city_name,
grid.grid_name,
grid.cost_zps_with5g as cost_grid,
city.cost_zps_with5g as cost_city,
grid.cost_zps_with5g/city.cost_zps_with5g as cost_over_city,
row_number() over(partition by grid.smonth,grid.city_name order by grid.cost_zps_with5g) as rn_city,
prov.cost_zps_with5g as cost_prov,
grid.cost_zps_with5g/prov.cost_zps_with5g as cost_over_prov,
row_number() over(partition by grid.smonth order by grid.cost_zps_with5g) as rn_prov,
grid.city_code*100 as city_code,
grid.grid_code,
grid.smonth,
dcg.sort_id as sort_id_city
from 
(select * from sqmdb_cost.rpt_dzpzs_cost_grid) grid 
left join
(select smonth,city_sname,cost_zps_with5g from sqmdb_cost.rpt_dzpzs_cost_city) city
on grid.smonth=city.smonth and grid.city_name=city.city_sname
left join
(select smonth,cost_zps_with5g from sqmdb_cost.rpt_dzpzs_cost_prov) prov
on grid.smonth=prov.smonth
join sqmdb_cost.dict_code_grid dcg on grid.grid_code=dcg.grid_code
where grid.cost_zps_with5g>0
and grid.smonth='202205'
order by dcg.sort_id,rn_city


--2 单端口能耗
--drop table sqmdb_cost.rpt_baobiao_2_ddk
--create table sqmdb_cost.rpt_baobiao_2_ddk as 
select 
grid.city_name,
grid.grid_name,
grid.cost_port_nenghao,
city.cost_port_nenghao_city,
grid.cost_port_nenghao/city.cost_port_nenghao_city as over_cost_city,
row_number() over(partition by grid.smonth,grid.city_name order by grid.cost_port_nenghao) as rn_city,
prov.cost_port_nenghao_prov,
grid.cost_port_nenghao/prov.cost_port_nenghao_prov as over_cost_prov,
row_number() over(partition by grid.smonth order by grid.cost_port_nenghao) as rn_prov,
grid.city_code*100 as city_code,
grid.grid_code,
grid.smonth,
dcg.sort_id as sort_id_city
from 
(select * from sqmdb_cost.rpt_dport_cost_grid) grid
left join 
(select smonth,city_sname,cost_port_nenghao as cost_port_nenghao_city from sqmdb_cost.rpt_dport_cost_city) city
on grid.smonth=city.smonth and grid.city_name=city.city_sname
left join
(select smonth,cost_port_nenghao as cost_port_nenghao_prov from sqmdb_cost.rpt_dport_cost_b10 where sname='河北') prov
on grid.smonth=prov.smonth
join sqmdb_cost.dict_code_grid dcg on grid.grid_code=dcg.grid_code
where grid.smonth='202205'
order by dcg.sort_id,rn_city


--3 单站租赁费
--drop table sqmdb_cost.rpt_baobiao_3_dzzl
--create table sqmdb_cost.rpt_baobiao_3_dzzl as 
select 
tw.city_name,
--tw.area_operator,
tw.grid_name,
round(tw.tower_cost_avg_area) as tower_cost_avg_area,
round(tw.tower_cost_avg_city) as tower_cost_avg_city,
round(tw.over_cost_city,2) as tower_over_cost_city,
tw.rn_city as tower_rn_city,
round(tw.tower_cost_avg_prov) as tower_cost_avg_prov,
round(tw.over_cost_prov,2) as tower_over_cost_prov,
tw.rn_prov as tower_rn_prov,
round(ht.avg_cost_grid) as ht_avg_cost_grid,
round(ht.avg_cost_city) as ht_avg_cost_city,
round(ht.over_cost_city,2) as ht_over_cost_city,
ht.rn_city as ht_rn_city,
round(ht.avg_cost_prov) as ht_avg_cost_prov,
round(ht.over_cost_prov,2) as ht_over_cost_prov,
ht.rn_prov as ht_rn_prov,
tw.city_code,
tw.grid_code,
tw.smonth,
dcg.sort_id as sort_id_city
from
(select
tg.smonth,
tg.city_name,
tg.grid_name,
tg.tower_cost_avg as tower_cost_avg_area,
tc.tower_cost_avg as tower_cost_avg_city,
tg.tower_cost_avg/tc.tower_cost_avg as over_cost_city,
row_number() over(partition by tg.smonth,tg.city_name order by tg.tower_cost_avg) as rn_city,
tp.tower_cost_avg_prov,
tg.tower_cost_avg/tp.tower_cost_avg_prov as over_cost_prov,
row_number() over(partition by tg.smonth order by tg.tower_cost_avg) as rn_prov,
tg.city_code*100 as city_code,
tg.grid_code
from sqmdb_cost.rpt_tower_cost_grid tg
left join sqmdb_cost.rpt_tower_cost_city tc on tg.smonth=tc.smonth and tg.city_name=tc.city_name
left join
(select smonth,sname,column1 as tower_cost_avg_prov from sqmdb_cost.report50016 where smonth='202206' and sname='河北') tp
on tg.smonth=tp.smonth
where tg.smonth='202206'
order by tg.city_code,rn_city) tw
left join
(with station_cost as ( --合同
select city_name_short(s.city_name) as city_name,s.area_name,s.station_sid,s.station_sname,h.sid,h.sname,h.yuezujin
from sqmdb_cost.station3 s
 join sqmdb_cost.station_hetong sh on s.station_sid=sh.station_sid
 join sqmdb_cost.hetong_new h on h.sid=sh.hetong_sid
where h.is_tc='否' and h.yuezujin is not null)
select 
grid.smonth,
grid.city_name,
grid.area_name,
grid.avg_cost_grid,
city.avg_cost_city,
grid.avg_cost_grid/city.avg_cost_city as over_cost_city,
row_number() over(partition by grid.smonth,grid.city_name order by grid.avg_cost_grid) as rn_city,
prov.avg_cost_prov,
grid.avg_cost_grid/prov.avg_cost_prov as over_cost_prov,
row_number() over(partition by grid.smonth order by grid.avg_cost_grid) as rn_prov,
dgc.grid_code 
from
(select '202206' as smonth,city_name,area_name,avg(yuezujin) as avg_cost_grid from station_cost group by city_name,area_name) grid
left join
(select '202206' as smonth,city_name,avg(yuezujin) as avg_cost_city from station_cost group by city_name) city
on grid.city_name=city.city_name
left join 
(select '202206' as smonth,avg(yuezujin) as avg_cost_prov from station_cost) prov
on grid.smonth=prov.smonth
left join sqmdb_cost.dict_grid_code dgc on grid.city_name=dgc.city_name and grid.area_name=dgc.grid_name) ht
on tw.grid_code=ht.grid_code
join sqmdb_cost.dict_code_grid dcg on tw.grid_code=dcg.grid_code 
order by dcg.sort_id,tw.rn_city


--4 超高租费
--drop table sqmdb_cost.rpt_baobiao_4_cgzf
--create table sqmdb_cost.rpt_baobiao_4_cgzf as 
select distinct
dg.city_sname,	--地市分公司
--t1.city_name,	
t1.station_sname,--局站名称
t1.area_name,	--区县名称
t1.station_sid,	--局站ID
t1.station_type,--局站类型
t1.station_level,--所属场景
t1.property_type,--权属类别
t1.tower_cost,	--铁塔租金
t1.changd_cost,	--其中：场租
t1.zhejiu_cost,	--其中：折旧
t1.service_property,--业务属性
t1.production_type,	--产品类型
t1.site_addr_id,	--铁塔站址编码
t1.tower_cbzx,		--成本中心
tchang.changd_cost_grid, --场租（网格）
t1.changd_cost/tchang.changd_cost_grid as over_changd_cost_grid,--场租倍数（网格）
tchang.tower_cost_grid,	--租赁费 本网格
case when tchang.tower_cost_grid=0 then null else t1.tower_cost/tchang.tower_cost_grid end as over_tower_cost_grid,--租赁费倍数（网格）
tchang.changd_cost_city, --场租（地市） 
t1.changd_cost/tchang.changd_cost_city as over_changd_cost_city,--场租倍数（地市）
tchang.tower_cost_city,	--租赁费 本地市
t1.tower_cost/tchang.tower_cost_city as over_tower_cost_city,--租赁费倍数（地市）
min(t1.changd_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type) as changd_cost_grid_scene_good,	--场租：本场景本网格最优
avg(t1.changd_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type) as changd_cost_grid_scene_avg,	--场租：本场景本网格均值
case when avg(t1.changd_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type)=0 then null else
t1.changd_cost/avg(t1.changd_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type) end as changd_cost_grid_scene_bs,	--场租：场租倍数
min(t1.changd_cost)over(partition by t1.city_name,t1.station_level,t1.service_property,t1.production_type) as changd_cost_city_scene_good,				--场租：本场景本地市网格内最优
avg(t1.changd_cost)over(partition by t1.city_name,t1.station_level,t1.service_property,t1.production_type) as changd_cost_city_scene_avg,				--场租：本场景本地市网格内均值
min(t1.tower_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type) as tower_cost_grid_scene_good,	--铁塔租赁费：本场景本网格最优
avg(t1.tower_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type) as tower_cost_grid_scene_avg,	--铁塔租赁费：本场景本网格均值
case when avg(t1.tower_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type)=0 then null else
t1.tower_cost/avg(t1.tower_cost)over(partition by t1.city_name,t1.area_name,t1.station_level,t1.service_property,t1.production_type) end as tower_cost_grid_scene_bs,	--铁塔租赁费：租赁费倍数
min(t1.tower_cost)over(partition by t1.city_name,t1.station_level,t1.service_property,t1.production_type) as tower_cost_city_scene_good,				--铁塔租赁费：本场景本地市网格内最优
avg(t1.tower_cost)over(partition by t1.city_name,t1.station_level,t1.service_property,t1.production_type) as tower_cost_city_scene_avg,					--铁塔租赁费：本场景本地市网格内均值
t1.hetong_yuezujin as cost_hetong,	--合同租金
t1.sid as hetong_sid,				--合同编号
t1.hetong_cbzx,						--合同成本中心
min(t1.hetong_yuezujin)over(partition by t1.city_name,t1.area_name,t1.station_level) as ht_cost_grid_scene_good,--本场景本网格最优
min(t1.hetong_yuezujin)over(partition by t1.city_name,t1.station_level) as ht_cost_city_scene_good,				--本场景本地市网格内最优
avg(t1.hetong_yuezujin)over(partition by t1.city_name,t1.area_name,t1.station_level) as ht_cost_grid_scene_avg,	--本场景本地市网格内均值
case when avg(t1.hetong_yuezujin)over(partition by t1.city_name,t1.area_name,t1.station_level)=0 then null else
t1.hetong_yuezujin/avg(t1.hetong_yuezujin)over(partition by t1.city_name,t1.area_name,t1.station_level) end as ht_cost_grid_scene_bs, --租金倍数
min(t1.hetong_yuezujin)over(partition by t1.city_name,t1.station_level,t1.hetong_cbzx) as ht_cost_cbzx_good,	--本场景本单位最优
dg.city_code*100 as city_code,
'202206' as smonth,
dgc.grid_code,
dg.sort_id as sort_id_city,
(case t1.station_level
when '城市' then 1
when '县城' then 2
when '乡镇' then 3
when '农村' then 4
else 5 end) as station_level_id
from (
select
a.station_sid,
a.station_sname,
a.city_name,
a.area_name,
a.station_type,
a.property_type,
a.station_level,
a.tower_code,
c.yuezujin as hetong_yuezujin,--合同系统月租金（由合同总月份数和总金额计算得出）
c.cname,
c.cfname,
c.cbzx as hetong_cbzx,
c.sid,
c.sname,
d.sdate,
e.cbzx as tower_cbzx,
d.site_addr_id,
d.total_cost as tower_cost,
d.changd_cost,
d.zhejiu_cost,
d.service_property,
d.production_type,
(coalesce(c.yuezujin,0)+coalesce(d.total_cost,0)) as cost_total,--总费用（合同月租金使用合同系统值）
case when c.cbzx is null then e.cbzx else c.cbzx end as cbzx
from sqmdb_cost.station3 a
left join sqmdb_cost.station_hetong b on a.station_sid=b.station_sid
left join (select * from sqmdb_cost.hetong_new where is_tc='否') c on c.sid=b.hetong_sid
left join 
(select sdate,site_addr_id,service_property,production_type,
sum(total_cost) as total_cost,
sum(changd_cost) as changd_cost,
sum(zhejiu_cost) as zhejiu_cost 
from sqmdb_cost.tower3 
where sdate='202206' 
group by sdate,site_addr_id,service_property,production_type) d 
on a.tower_code=d.site_addr_id
left join sqmdb_cost.dict_tower_cbzx e on a.tower_code=e.tower_code) t1
left join 
(select * from sqmdb_cost.v_tower_cost_changd where sdate='202206') tchang 
on t1.city_name=tchang.city_operator and t1.area_name=tchang.area_name
left join sqmdb_cost.dict_grid_code dgc on city_name_short(t1.city_name)=dgc.city_name and trim(t1.area_name)=trim(dgc.grid_name)
join 
(select distinct city_code,city_name,city_sname,sort_id from sqmdb_cost.dict_grid) dg
on t1.city_name=dg.city_name
where t1.site_addr_id is not null or t1.sid is not null
order by sort_id_city,
(case t1.station_level
when '城市' then 1
when '县城' then 2
when '乡镇' then 3
when '农村' then 4
else 5 end),
t1.service_property,t1.production_type,t1.changd_cost desc


--5 转供电超高电费单价站点
--drop table sqmdb_cost.rpt_baobiao_5_cgdf
--create table sqmdb_cost.rpt_baobiao_5_cgdf as 
select distinct
dg.city_sname,
s.station_sid,
s.station_sname,
yn.build_name,
s.station_type,
s.station_level,
s.property_type,
case when yn.accrued_power=0 then 0 else yn.accrued_cost/yn.accrued_power end as eprice,
yn.cost_unit_name,
dg.city_code*100 as city_code,
yn.smonth,
dgc.grid_code,
yn.area_name,
dg.sort_id as sort_id_city
from sqmdb_cost.ytmx_new yn
left join sqmdb_cost.station_nenghao sn on yn.build_code_short=sn.oss_station
left join sqmdb_cost.station3 s on sn.station_sid=s.station_sid
left join sqmdb_cost.dict_grid_code dgc on city_name_short(yn.city_name)=dgc.city_name and trim(yn.area_name)=trim(dgc.grid_name)
left join (select distinct city_name,city_code,city_sname,sort_id from sqmdb_cost.dict_grid) dg on yn.city_name=dg.city_name
where yn.meter_type_name in ('12-01 | 转供手抄表')
and yn.build_type_name not in ('办公楼','办公用房','营业用房')
and yn.smonth='202207'
order by dg.sort_id,s.station_level


--6 超高能耗站点
--drop table sqmdb_cost.rpt_baobiao_6_cgnh
--create table sqmdb_cost.rpt_baobiao_6_cgnh as 
select distinct
dg.city_sname,
s.station_sname,
s.area_name,
s.station_type,
s.station_level,
s.property_type,
yn.meter_type_name,
yn.accrued_power,
case when yn.accrued_power=0 then 0 else yn.accrued_cost/yn.accrued_power end as eprice,
yn.accrued_cost,
yn.cost_unit_name,
wg_nh.nh_total as lilun_power,
case when wg_nh.nh_total=0 then 0 else yn.accrued_power/wg_nh.nh_total end as over_lilun_power,
cec.city_eprice as lilun_eprice,
cec.city_eprice*wg_nh.nh_total as lilun_cost,
case when wg_nh.nh_total=0 then 0 else yn.accrued_cost/(cec.city_eprice*wg_nh.nh_total) end as over_lilun_cost,
dg.city_code*100 as city_code,
yn.smonth,
dgc.grid_code,
yn.build_code_short as nh_sid_short,
s.station_sid,
dg.sort_id as sort_id_city
from sqmdb_cost.ytmx_new yn
left join sqmdb_cost.station_nenghao sn on yn.build_code_short=sn.oss_station
left join sqmdb_cost.station3 s on sn.station_sid=s.station_sid
left join sqmdb_cost.dict_grid_code dgc on city_name_short(yn.city_name)=dgc.city_name and trim(yn.area_name)=trim(dgc.grid_name)
left join (select distinct city_name,city_code,city_sname,sort_id from sqmdb_cost.dict_grid) dg on yn.city_name=dg.city_name
left join sqmdb_cost.cfg_eprice_city cec on dg.city_sname=cec.city_name
join sqmdb_cost.v_station_omc_nh_month wg_nh --专业网管能耗
on yn.smonth=wg_nh.smonth and sn.station_sid=wg_nh.station_sid
where yn.smonth='202207'
order by dg.sort_id


--有合同无局站
select 
distinct
'202207' as smonth,
h.city_name,
h.cname,
h.dname,
h.sid,
h.sname,
h.cbzx,
sh.station_sid as idx_station_sid,
sh.hetong_sid as idx_hetong_sid,
(case when sh.hetong_sid is null then '有合同无局站' else '有合同有局站' end) as stype,
dgc.grid_code,
dc.sort_id as sort_id_city
from 
(select city_name,sid,sname,cname,dname,cbzx from sqmdb_cost.hetong_new where is_tc='否') h
left join sqmdb_cost.station_hetong sh
on h.sid=sh.hetong_sid
left join sqmdb_cost.dict_grid_code dgc on h.city_name=dgc.city_name and trim(h.dname)=trim(dgc.grid_name)
left join sqmdb_cost.dict_city dc on h.city_name=dc.city_name 
order by dc.sort_id


--有能耗无局站
select 
distinct
yn.smonth,
yn.build_code_short,
yn.city_name,
yn.area_name,
yn.build_name,
yn.cost_unit_name,
yn.accrued_power,
yn.accrued_cost,
s.city_name as idx_city_name,
sn.oss_station as idx_nh_short_id,
sn.station_sid as idx_station_sid,
s.station_sname as idx_station_name,
wg.station_sid as omc_station_sid,
wg.nh_total as omc_nh_total,
(case 
when s.oss_station is not null and yn.accrued_cost!=0 and yn.accrued_cost is not null and wg.station_sid is not null then '校验正常'
when s.oss_station is null then '有能耗无索引'
when yn.accrued_cost is null or yn.accrued_cost=0 then '有能耗无成本'
when yn.accrued_cost!=0 and wg.station_sid is null then '有能耗有成本，校验有问题'
end) as stype,
dgc.grid_code,
dc.sort_id as sort_id_city
from 
(select * from sqmdb_cost.ytmx_new where build_code_short is not null and smonth='202207') yn 
left join sqmdb_cost.station_nenghao sn on yn.build_code_short=sn.oss_station
left join sqmdb_cost.station3 s on sn.station_sid=s.station_sid
left join
(select * from sqmdb_cost.v_station_omc_nh_month where smonth='202207') wg
on sn.station_sid=wg.station_sid
left join sqmdb_cost.dict_grid_code dgc on city_name_short(yn.city_name)=dgc.city_name and trim(yn.area_name)=trim(dgc.grid_name)
left join sqmdb_cost.dict_city dc on yn.city_name=dc.city_name 
order by dc.sort_id


--有铁塔订单无局站
select 
distinct 
t.sdate as smonth,
t.city_operator,
t.area_operator,
t.site_addr_id,
dtc.cbzx,
s.station_sid,
s.station_sname,
s.property_type,
s.station_level,
case when s.tower_code is null then '有铁塔订单无局站' else '有铁塔订单有局站' end as stype,
dgc.grid_code,
dc.sort_id as sort_id_city
from 
(select distinct sdate,city_operator,area_operator,site_addr_id,site_name 
from sqmdb_cost.tower3 where sdate='202207') t
left join 
(select station_sid,station_sname,property_type,station_level,tower_code from sqmdb_cost.station3) s 
on t.site_addr_id=s.tower_code
left join sqmdb_cost.dict_grid_code dgc on city_name_short(t.city_operator)=dgc.city_name and trim(t.area_operator)=trim(dgc.grid_name)
left join sqmdb_cost.dict_tower_cbzx dtc on t.site_addr_id=dtc.tower_code
left join sqmdb_cost.dict_city dc on t.city_operator=dc.city_name 
order by dc.sort_id