chengben/Scripts/bigscreen/chengben_quzhun_1_e_grid.sql

---1-e.基站单站租金合理性（局站维度）与【看异常】租赁费匹配顺序相同
---chengben_quzhun_1_e_grid_202203.csv
----网格
----判断标准：大于2倍铁塔成本中心平均租费
select tb1.*,tb2.city_code*100 as city_code,tb3.area_code from
(select sdate,city_name,area_name,
count(*) as station_num,
sum(case when cost_rate>2 then 1 else 0 end) as over2_num,
sum(case when cost_rate>2 then 1 else 0 end)/count(*)::numeric as over2_rate
from
(select distinct t1.sdate,t1.city_name,t1.area_name,t1.station_sid,t1.cost_total as cost_station,
t2.cbzx_cost_avg,t1.cost_total/t2.cbzx_cost_avg as cost_rate from 
(select 
a.station_sid,a.station_sname,a.city_name,a.area_name,a.property_type,a.station_level,a.tower_code,
c.yuezujin as hetong_yuezujin,--合同系统月租金（由合同总月份数和总金额计算得出）
c.cname,c.cfname,c.cbzx as hetong_cbzx,c.sid,c.sname,
d.sdate,e.cbzx as tower_cbzx,d.service_tracking_id,d.site_addr_id,d.stype,d.is_duxiang,d.total_cost as tower_cost,
(coalesce(c.yuezujin,0)+coalesce(d.total_cost,0)) as cost_total,--总费用（合同月租金使用合同系统值）
case when c.cbzx is null then e.cbzx else c.cbzx end as cbzx
from sqmdb_cost.station3 a
left join sqmdb_cost.station_hetong b on a.station_sid=b.station_sid
left join sqmdb_cost.hetong_new c on c.sid=b.hetong_sid
left join sqmdb_cost.tower3 d on a.tower_code=d.site_addr_id
left join sqmdb_cost.dict_tower_cbzx e on a.tower_code=e.tower_code) t1
left join sqmdb_cost.v_tower_cost_cbzx t2 on t1.sdate=t2.sdate::varchar and t1.cbzx=t2.cbzx
where t1.sdate='202204') tmp
group by sdate,city_name,area_name) tb1
left join 
(select distinct city_code,city_name from sqmdb_cost.dict_grid) tb2
on tb1.city_name=tb2.city_name
left join 
(select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) tb3
on tb1.city_name=tb3.city_name and tb1.area_name=tb3.area_name
order by tb2.city_code,tb3.area_code