chengben/Scripts/bigscreen/chengben_quzhun_1_a_grid.sql

---1-a.转供电+定额包干累计成本占比（%）
---chengben_quzhun_1_a_grid_202203.csv
select 
t1.*,
t2.city_code*100 as city_code,
t3.area_code
from
(select smonth,city_name,area_name,
sum(accrued_cost) as total_cost,
sum(case when y.meter_type_name in ('12-01 | 转供手抄表','21-01 | 定额协议表','21-02 | 铁塔包干表') then accrued_cost else 0 end) as zgd_cost,
sum(case when y.meter_type_name in ('12-01 | 转供手抄表','21-01 | 定额协议表','21-02 | 铁塔包干表') then accrued_cost else 0 end)/sum(accrued_cost) as zgd_cost_rate
from sqmdb_cost.ytmx_new y 
group by smonth,city_name,area_name) t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2
on t1.city_name=t2.city_name
join 
(select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) t3
on t1.city_name=t3.city_name and t1.area_name=t3.area_name
where t1.smonth='202204'
order by t2.city_code,t3.area_code