chengben/Scripts/bigscreen/chengben_quzhun_1_c_grid.sql

---1-c.大于2倍铁塔单站租赁费三方站点占比
---chengben_quzhun_1_c_grid_202203.csv
----网格
select 
t1.*,
t2.city_code*100 as city_code,
t3.area_code 
from
(select sdate,city_name,area_name,
sum(case when property_type='租用第三方' then 1 else 0 end) as cost_over2b_3rd_num,
count(*) as station_num,
sum(case when property_type='租用第三方' then 1 else 0 end)/count(*)::numeric as over_rate_3rd
from(
select *,
cost_site.cost_tower/cost_cbzx.cbzx_cost_avg as cost_rate
from
(select station_sid,city_name,area_name,property_type,tower_code from sqmdb_cost.station3 s where s.property_type in ('租用铁塔','租用第三方')) st
join
(select sdate,site_addr_id,sum(total_cost) as cost_tower from sqmdb_cost.tower3 t group by sdate,site_addr_id) cost_site
on st.tower_code=cost_site.site_addr_id
join sqmdb_cost.dict_tower_cbzx dtc on st.tower_code=dtc.tower_code
join 
(select sdate as cbzx_sdate,cbzx,cbzx_cost_avg from sqmdb_cost.v_tower_cost_cbzx) cost_cbzx
on cost_site.sdate=cost_cbzx.cbzx_sdate::varchar and dtc.cbzx=cost_cbzx.cbzx
where cost_site.cost_tower/cost_cbzx.cbzx_cost_avg>2
and cost_site.sdate='202204') a
group by sdate,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
left 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
order by t2.city_code,t3.area_code