123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135 |
- --地市
- --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 ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
|