chengben/Scripts/bigscreen/chengben_exception_1_grid.sql

--租赁费（chengben_exception_1_grid_202203.csv）
----横向匹配 局站表a <- 合同索引b <- 合同系统c <- 铁塔全量表d
select tb1.*,tb2.city_code*100 as city_code,tb3.area_code from
(select 
distinct
t1.sdate,
t1.city_name,
t1.area_name,
t1.station_sid,
t1.station_sname,
t1.property_type,
t1.sid as hetong_sid,
t1.hetong_yuezujin as cost_hetong,
t1.tower_code,
t1.tower_cost as cost_tower,
t1.cost_total,
t2.cbzx,
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.site_addr_id,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 (select sdate,site_addr_id,sum(total_cost) as total_cost from sqmdb_cost.tower3 where sdate='202204' group by sdate,site_addr_id) 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 (select * from sqmdb_cost.v_tower_cost_cbzx where sdate='202204') t2 
on t1.sdate=t2.sdate::varchar and t1.cbzx=t2.cbzx
where t1.cost_total/t2.cbzx_cost_avg>1.5) 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