chengben/Scripts/kpi31/24_grid.sql

--24、租赁局站占比
------------------------使用station3------------------------
---网格
select 
'202206' 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,
a.area_name as GRID_NAME,
'24' as ZB_CODE,
'租赁局站占比' as ZB_NAME,
'是' as IS_RATIO,
a.zu_num as QZ_FZ,
a.total_num as QZ_FM,
a.zu_rate as BY_QZ
from
(select city_name,area_name,
count(*) as total_num,
sum(case when property_type like '%租用%' then 1 else 0 end) as zu_num,
sum(case when property_type like '%租用%' then 1 else 0 end)/count(*)::numeric as zu_rate
from sqmdb_cost.station3
group by city_name,area_name) a
join sqmdb_cost.dict_grid_code dgc on city_name_short(a.city_name)=dgc.city_name and trim(a.area_name)=trim(dgc.grid_name)
join sqmdb_cost.dict_code_grid dcg on dgc.grid_code=dcg.grid_code
order by dcg.city_code