chengben/Scripts/端口用户数_已校准.sql

--地市
--create table sqmdb_cost.jt_number_city as
--insert into sqmdb_cost.jt_number_city
select 
t.*,
km.total_gl,
zps.zps_total_with5g,--载频载扇数（含5G）
zps.zps_total_no5g--载频载扇数（不含5G） 
from
(select 
t2.month_id,
case when t1.city_name='雄安新区' then '雄安' else t1.city_name end,
(t1.kpi_value+t2.kpi_value)/2/10000 as total_gl from
(select month_id,city_name,sum(kpi_value) as kpi_value from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('YB0140') and month_id='202112' group by month_id,city_name) t1
left join
(select month_id,city_name,sum(kpi_value) as kpi_value from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('YB0140') and month_id='202204' group by month_id,city_name) t2
on t1.city_name=t2.city_name) km
join 
(select 
kd.month_id,
case when kd.city_name='雄安新区' then '雄安' else kd.city_name end,
(kd.total_user_kd+yw.total_user_yw) as total_user,--用户数
kd.total_user_kd,--宽带用户数
yw.total_user_yw,--移网用户数
ip.port_total,--端口数
ip.port_rate,--端口实占率
ip.port_inuse--在用端口数
from
(select month_id,city_name,kpi_value/10000 as total_user_kd from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('UAFA3000_AS')) kd
join
(select month_id,city_name,kpi_value/10000 as total_user_yw from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('UAGG2000')) yw
on kd.month_id=yw.month_id and kd.city_name=yw.city_name
join 
(select distinct month_id,city_desc,kpi_value as port_total,by_szl/100 as port_rate,kpi_value*by_szl/100 as port_inuse 
from sqmdb_cost.baobiao_ipduankou bi) ip
on kd.month_id=ip.month_id and kd.city_name=ip.city_desc
where kd.month_id='202204') t
on km.month_id=t.month_id and km.city_name=t.city_name
join
(select t2.smonth,t1.city_name,
(t1.zps_total_with5g+t2.zps_total_with5g)/2/10000 as zps_total_with5g,
(t1.zps_total_no5g+t2.zps_total_no5g)/2/10000 as zps_total_no5g
from
(select smonth,city_name,zps_total as zps_total_with5g,(zps_total-zps_5g) as zps_total_no5g from sqmdb_cost.zpzs_city where smonth='202112') t1
left join
(select smonth,city_name,zps_total as zps_total_with5g,(zps_total-zps_5g) as zps_total_no5g from sqmdb_cost.zpzs_city where smonth='202204') t2
on t1.city_name=t2.city_name) zps
on km.month_id=zps.smonth and km.city_name=zps.city_name



--皮长公里（接口数据baobiao_jf_yonghush，统计结果与专业分解表一致）
--YB0140-光缆总长度
select t2.month_id,t1.city_name,(t1.kpi_value+t2.kpi_value)/2/10000 as total_gl from
(select month_id,city_name,sum(kpi_value) as kpi_value from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('YB0140') and month_id='202112' group by month_id,city_name) t1
left join
(select month_id,city_name,sum(kpi_value) as kpi_value from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('YB0140') and month_id='202203' group by month_id,city_name) t2
on t1.city_name=t2.city_name


--载频载扇数（手工提供，统计结果与专业分解表不一致）
select t2.smonth,t1.city_name,(t1.zps_total+t2.zps_total)/2/10000 as zps_total_with5g from
(select smonth,city_name,sum(zps_num_234g+COALESCE(zps_num_5g,0)) as zps_total from sqmdb_cost.zpzs where smonth='202112' group by smonth,city_name) t1
left join
(select smonth,city_name,sum(zps_num_234g+COALESCE(zps_num_5g,0)) as zps_total from sqmdb_cost.zpzs where smonth='202203' group by smonth,city_name) t2
on t1.city_name=t2.city_name

----载频载扇数-地市版，统计结果与专业分解表一致（由于地市载频载扇数和网格载频载扇数不是同一时间提取，所以不能用网格汇聚地市，会有差异）
select t2.smonth,t1.city_name,(t1.zps_total+t2.zps_total)/2/10000 as zps_total_with5g from
(select smonth,city_name,zps_total from sqmdb_cost.zpzs_city where smonth='202112') t1
left join
(select smonth,city_name,zps_total from sqmdb_cost.zpzs_city where smonth='202203') t2
on t1.city_name=t2.city_name


--宽带用户（接口数据baobiao_jf_yonghush，统计结果与专业分解表一致）
--UAFA3000_AS-宽带接入网上用户#
select month_id,city_name,kpi_value/10000 as total_user_kd from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('UAFA3000_AS') and month_id='202203'


--移网用户（接口数据baobiao_jf_yonghush，统计结果与专业分解表一致）
--UAGG2000-移动电话用户
select month_id,city_name,kpi_value/10000 as total_user_yw from sqmdb_cost.baobiao_jf_yonghush
where kpi_code in('UAGG2000') and month_id='202203'


--端口数（接口数据baobiao_ipduankou，统计结果与专业分解表一致）
select distinct month_id,city_desc,kpi_value as port_total,by_szl/100 as port_rate,kpi_value*by_szl/100 as port_inuse 
from sqmdb_cost.baobiao_ipduankou bi where month_id='202203'




--北10
--create table sqmdb_cost.jt_number_b10 as
--insert into sqmdb_cost.jt_number_b10
select 
t.*,
zps.total_gl,
zps.zps_total_no5g--载频载扇数（不含5G） 
from
(select 
yhs.smonth,
yhs.sname,
(yhs.total_user_kd+yhs.total_user_yw) as total_user,--用户数
yhs.total_user_kd,--宽带用户数
yhs.total_user_yw,--移网用户数
ip.port_total,--端口数
ip.port_rate,--端口实占率
ip.port_inuse--在用端口数
from
(select smonth,sname,user_kd as total_user_kd,user_yw as total_user_yw from sqmdb_cost.baobiao_jf_yonghush_b10) yhs
join 
(select distinct smonth,sname,kpi_value as port_total,by_szl/100 as port_rate,kpi_value*by_szl/100 as port_inuse 
from sqmdb_cost.baobiao_ipduankou_b10) ip
on yhs.smonth=ip.smonth and yhs.sname=ip.sname
where yhs.smonth='202204') t
join
(select t2.smonth,t2.sname,
(t1.total_gl+t2.total_gl)/2 as total_gl,
(t1.zps_total_no5g+t2.zps_total_no5g)/2 as zps_total_no5g
from
(select smonth,sname,total_gl,zps_total_no5g from sqmdb_cost.zpzs_gl_b10 where smonth='202112') t1
left join
(select smonth,sname,total_gl,zps_total_no5g from sqmdb_cost.zpzs_gl_b10 where smonth='202204') t2
on t1.sname=t2.sname) zps
on t.smonth=zps.smonth and t.sname=zps.sname
where t.sname in ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')