1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253 |
- --11、单业务量移网成本
- ---地市
- select cost_t.smonth,cost_t.city_name,cost_t.cost_yw,cost_t.cost_hexin,ywl_t.yewul,
- (cost_t.cost_yw+cost_t.cost_hexin)/ywl_t.yewul as cost_ywl
- from
- (select a.smonth,dg.city_name,a.cost_yw,a.cost_hexin from
- (select '202203' as smonth,sname,
- sum(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_yw,
- sum(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin
- from sqmdb_cost.report50
- where period_type='M'
- and sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
- and smonth between '202201' and '202203' group by sname) a
- join (select distinct city_pro,city_name from sqmdb_cost.dict_grid) dg
- on a.sname=dg.city_pro) cost_t
- join
- (select dg.city_name,(y1.yewul+y2.yewul)/2 as yewul from
- (select city_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202112' group by city_name) y1
- join
- (select city_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202203' group by city_name) y2
- on y1.city_name=y2.city_name
- join
- (select distinct city_sname,city_name from sqmdb_cost.dict_grid) dg
- on y1.city_name=dg.city_sname) ywl_t
- on cost_t.city_name=ywl_t.city_name
- ---网格
- select cost_t.smonth,cost_t.city_name,cost_t.area_name,cost_t.cost_yw,cost_t.cost_hexin,ywl_t.yewul,
- (cost_t.cost_yw+cost_t.cost_hexin)/ywl_t.yewul as cost_ywl
- from
- (select a.smonth,dg.city_name,dg.area_name,a.cost_yw,a.cost_hexin from
- (select smonth,city_code,city_name,grid_code,grid_name,
- (column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_yw,
- (column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin
- from sqmdb_cost.report49
- where period_type='Y'
- and smonth='202203') a
- join sqmdb_cost.dict_grid dg
- on a.city_code=dg.grid_city_code::varchar and a.grid_code=dg.grid_code::varchar) cost_t
- join
- (select dg.city_name,dg.area_name,(y1.yewul+y2.yewul)/2 as yewul from
- (select city_name,grid_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202112' group by city_name,grid_name) y1
- join
- (select city_name,grid_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202203' group by city_name,grid_name) y2
- on y1.city_name=y2.city_name and y1.grid_name=y2.grid_name
- join
- (select distinct city_sname,city_name,area_name,grid_name from sqmdb_cost.dict_grid) dg
- on y1.city_name=dg.city_sname and y1.grid_name=dg.grid_name) ywl_t
- on cost_t.city_name=ywl_t.city_name and cost_t.area_name=ywl_t.area_name
|