chengben/Scripts/kpi31/17_grid.sql

--17、户均装机料费（集团网格report49料费成本大部分是0，都集中在市本部）
---网格
select 
fz.smonth as MONTH_ID,
'网格' as ZB_TYPE,
'河北' as PROV_NAME,
dcg.city_code*100 as CITY_ID,
dcg.city_name as CITY_NAME,
dcg.grid_code as GRID_ID,
fz.grid_name as GRID_NAME,
'17' as ZB_CODE,
'户均装机料费' as ZB_NAME,
fz.cost_liao as QZ_FZ,
fm.order_num as QZ_FM,
fz.cost_liao/fm.order_num/4 as BY_QZ
from
(select a.smonth,a.city_name,a.grid_name,a.cost_liao,dgc.grid_code from
(select smonth,city_name_short(city_name) as city_name,grid_name,column33 as cost_liao
from sqmdb_cost.report49 where period_type='Y' and smonth='202204') a
join sqmdb_cost.dict_grid_code dgc on a.city_name=dgc.city_name and trim(a.grid_name)=trim(dgc.grid_name)
where dgc.grid_code is not null) fz
join
(select b.smonth,b.city_name,b.area_name,b.order_num,dgc1.grid_code from
(select smonth,city_name_short(city_name) as city_name,area_name,sum(order_num) as order_num 
from sqmdb_cost.zhuangji where smonth='202204' group by smonth,city_name_short(city_name),area_name) b
join sqmdb_cost.dict_grid_code dgc1 on b.city_name=dgc1.city_name and trim(b.area_name)=trim(dgc1.grid_name)) fm
on fz.grid_code=fm.grid_code
join sqmdb_cost.dict_code_grid dcg on fz.grid_code=dcg.grid_code