--网格端口数天粒度汇聚月粒度，每月初执行（不要执行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 sdate0 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