11_grid.sql 1.7 KB

1234567891011121314151617181920212223242526272829303132333435363738
  1. --11、单业务量移网成本
  2. ---网格
  3. select
  4. cost_t.smonth as MONTH_ID,
  5. '网格' as ZB_TYPE,
  6. '河北' as PROV_NAME,
  7. dcg.city_code*100 as CITY_ID,
  8. dcg.city_name as CITY_NAME,
  9. dcg.grid_code as GRID_ID,
  10. dcg.grid_name as GRID_NAME,
  11. '11' as ZB_CODE,
  12. '单业务量移网成本' as ZB_NAME,
  13. '否' as IS_RATIO,
  14. cost_t.cost_yw+cost_t.cost_hexin as QZ_FZ,
  15. ywl_t.yewul as QZ_FM,
  16. (cost_t.cost_yw+cost_t.cost_hexin)/ywl_t.yewul/7 as BY_QZ
  17. from
  18. (select a.smonth,a.city_name,a.grid_name,dgc.grid_code,a.cost_yw,a.cost_hexin from
  19. (select smonth,city_name_short(city_name) as city_name,grid_name,
  20. (column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_yw,
  21. (column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin
  22. from sqmdb_cost.report49
  23. where period_type='Y'
  24. and smonth='202207') a
  25. join sqmdb_cost.dict_grid_code dgc
  26. on a.city_name=dgc.city_name and trim(a.grid_name)=trim(dgc.grid_name)) cost_t
  27. join
  28. (select dg.city_name,dg.grid_code,dg.grid_name,avg_num(y1.yewul,y2.yewul) as yewul from
  29. (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
  30. join
  31. (select city_name,grid_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202207' group by city_name,grid_name) y2
  32. on y1.city_name=y2.city_name and trim(y1.grid_name)=trim(y2.grid_name)
  33. join sqmdb_cost.dict_grid_code dg
  34. on y1.city_name=dg.city_name and trim(y1.grid_name)=trim(dg.grid_name)
  35. where dg.grid_code is not null and avg_num(y1.yewul,y2.yewul)>0) ywl_t
  36. on cost_t.grid_code=ywl_t.grid_code
  37. join sqmdb_cost.dict_code_grid dcg on cost_t.grid_code=dcg.grid_code
  38. order by dcg.city_code