chengben/Scripts/26.sql

--26、H端口实占率
---地市新版（使用baobiao_ipduankou整理）
select month_id,city_name,port_total,port_inuse,port_rate 
from sqmdb_cost.jt_number_city jnc where month_id='202203'

---地市旧版，使用kuandai_ftth（率值：ftth_port_sts_count/ftth_port_count）
select local_net_name,
sum(ftth_port_count)/10000 as ftth_port_count,
sum(ftth_port_sts_count)/10000 as ftth_port_sts_count,
sum(ftth_port_sts_count)/sum(ftth_port_count) as ftth_port_rate
from sqmdb_cost.kuandai_ftth 
where areal_type_name='小计' and sdate='2022-04-02'
group by local_net_name
order by local_net_name

---网格，使用kuandai_ftth（率值：ftth_port_sts_count/ftth_port_count）
select local_net_name,area_name,
sum(ftth_port_count)/10000 as ftth_port_count,
sum(ftth_port_sts_count)/10000 as ftth_port_sts_count,
sum(ftth_port_sts_count)/sum(ftth_port_count) as ftth_port_rate
from sqmdb_cost.kuandai_ftth
where areal_type_name!='小计' and sdate='2022-04-02'
group by local_net_name,area_name
order by local_net_name,area_name

select date_trunc('month',current_date - interval '1' month)
union
select date_trunc('month',current_date)