chengben/Scripts/24.sql

--24、租赁局站占比
------------------------使用station3------------------------
---地市
select 
city_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
order by city_name

---网格
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
order by city_name,area_name

------------------------使用station2------------------------
--select * from sqmdb_cost.dict where dicttext like '%租用%'（99928：租用铁塔，99931：租用第三方）
---地市
select 
c.dicttext as city_name,
a.zuyong::numeric/b.total::numeric as zu_rate
from 
(select city_code,count(*) as zuyong from sqmdb_cost.station2 where property_type in ('99928','99931') group by city_code) a
left join
(select city_code,count(*) as total from sqmdb_cost.station2 group by city_code) b
on a.city_code=b.city_code
left join sqmdb_cost.dict c on a.city_code::varchar=c.dictid
order by c.dicttext

---网格
select 
c.dicttext as city_name,
d.dicttext as area_name,
a.zuyong::numeric/b.total::numeric as zu_rate
from 
(select city_code,area_code,count(*) as zuyong from sqmdb_cost.station2 where property_type in ('99928','99931') group by city_code,area_code) a
left join
(select city_code,area_code,count(*) as total from sqmdb_cost.station2 group by city_code,area_code) b
on a.city_code=b.city_code and a.area_code=b.area_code
left join sqmdb_cost.dict c on a.city_code::varchar=c.dictid
left join sqmdb_cost.dict d on a.area_code::varchar=d.dictid
order by c.dicttext,d.dicttext