123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151 |
- --16、单站铁塔租赁费(月)
- ---北10数据(select * from sqmdb_cost.report50016)
- select
- smonth as MONTH_ID,
- '北10' as ZB_TYPE,
- sname as PROV_NAME,
- null as CITY_ID,
- null as CITY_NAME,
- null as GRID_ID,
- null as GRID_NAME,
- '16' as ZB_CODE,
- '单站铁塔租赁费(月)' as ZB_NAME,
- column2*10000 as QZ_FZ,
- column5 as QZ_FM,
- column2*10000/column5/4 as BY_QZ
- from sqmdb_cost.report50016
- where sname in ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
- and smonth='202204'
- --北10
- select
- smonth,
- sname,
- column2*10000 as tower_cost,
- column4 as tower_num_ly,
- column3 as tower_num_cm,
- (column3+column4)/2 as tower_num_avg,
- column2*10000/((column3+column4)/2)/4 as tower_cost_avg
- from sqmdb_cost.report50016
- where sname in ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
- and smonth='202204'
- --河北省
- select smonth,sname,column1 as tower_cost_avg_prov from sqmdb_cost.report50016 where smonth='202204' and sname='河北'
- ---地市
- --create table sqmdb_cost.rpt_tower_cost_city as
- select
- '202204' as smonth,
- (regexp_split_to_array(tc.sname,'_'))[1] as city_name,
- tc.tower_cost,
- tn.tower_num_ly,
- tn.tower_num_cm,
- tn.tower_num_avg,
- (tc.tower_cost/tn.tower_num_avg)/4 as tower_cost_avg
- from
- (select sname,sum(column2+column3) as tower_cost from sqmdb_cost.report50 r
- where period_type='M' and smonth between '202201' and '202204' group by sname)tc
- join
- (with tower_code as (select distinct sdate,city_operator,site_addr_id from sqmdb_cost.tower3 where stype!='室分'
- union all
- select sdate,city_operator,site_addr_id from sqmdb_cost.tower3 where stype='室分')
- select
- dg.city_code,
- dg.city_pro,
- t1.tower_num_ly,
- t2.tower_num_cm,
- (t1.tower_num_ly+t2.tower_num_cm)/2 as tower_num_avg
- from
- (select city_operator,count(*) as tower_num_ly from tower_code where sdate='202112' group by city_operator)t1
- join
- (select city_operator,count(*) as tower_num_cm from tower_code where sdate='202204' group by city_operator)t2
- on t1.city_operator=t2.city_operator
- join
- (select distinct city_code,city_name,city_pro from sqmdb_cost.dict_grid)dg
- on t1.city_operator=dg.city_name) tn
- on tc.sname=tn.city_pro
- ---网格---网格名称+区县名称
- --drop table sqmdb_cost.rpt_tower_cost_grid
- --create table sqmdb_cost.rpt_tower_cost_grid as
- select
- tc.smonth,
- (regexp_split_to_array(tc.city_name,'_'))[2] as city_name,
- tn.area_name,
- tn.area_tower,
- tc.grid_name,
- tc.tower_cost,
- tn.tower_num_ly,
- tn.tower_num_cm,
- tn.tower_num_avg,
- (tc.tower_cost/tn.tower_num_avg)/4 as tower_cost_avg
- from
- (select smonth,city_name,grid_name,(column2+column3) as tower_cost from sqmdb_cost.report49 r
- where period_type='Y' and smonth='202204')tc
- join
- (with tower_code as (
- select distinct sdate,city_operator,area_operator,site_addr_id from sqmdb_cost.tower3 where stype!='室分'
- union all
- select sdate,city_operator,area_operator,site_addr_id from sqmdb_cost.tower3 where stype='室分')
- select
- dg.city_code,
- dg.area_name,
- dg.area_tower,
- dg.grid_city_name,
- dg.grid_name,
- t1.area_operator,
- t1.tower_num_ly,
- t2.tower_num_cm,
- (t1.tower_num_ly+t2.tower_num_cm)/2 as tower_num_avg
- from
- (select city_operator,area_operator,count(*) as tower_num_ly from tower_code where sdate='202112' group by city_operator,area_operator)t1
- join
- (select city_operator,area_operator,count(*) as tower_num_cm from tower_code where sdate='202204' group by city_operator,area_operator)t2
- on t1.city_operator=t2.city_operator and t1.area_operator=t2.area_operator
- join
- (select distinct city_code,city_name,area_name,grid_city_name,grid_name,area_tower from sqmdb_cost.dict_grid)dg
- on t1.city_operator=dg.city_name and t1.area_operator=dg.area_tower) tn
- on tc.city_name=tn.grid_city_name and trim(tc.grid_name)=trim(tn.grid_name)
- ---网格---使用铁塔预提表计算网格站点数
- --drop table sqmdb_cost.rpt_tower_cost_grid_1
- --create table sqmdb_cost.rpt_tower_cost_grid_1 as
- select
- tc.smonth,
- tc.city_name,
- dcg.city_code*100 as city_code,
- dcg.grid_name,
- dcg.grid_code,
- --tn.area_name,
- --tn.area_tower,
- --tc.grid_name,
- tc.tower_cost,
- tn.tower_num_ly,
- tn.tower_num_cm,
- tn.tower_num_avg,
- (tc.tower_cost/tn.tower_num_avg)/4 as tower_cost_avg
- from
- (select t1.smonth,t1.city_name,t1.tower_cost,t2.grid_code
- from
- (select smonth,city_name_short(city_name) as city_name,grid_name,(column2+column3) as tower_cost from sqmdb_cost.report49 r where period_type='Y' and smonth='202204')t1
- left join sqmdb_cost.dict_grid_code t2 on t1.city_name=t2.city_name and trim(t1.grid_name)=trim(t2.grid_name)
- where t2.grid_code is not null)tc
- join
- (select yt.city_name,dgc.grid_code,yt.tower_num_ly,yt.tower_num_cm,yt.tower_num_avg from
- (select a.city_name,a.cbzx,b.tower_num_ly,a.tower_num_cm,
- avg_num(a.tower_num_cm,b.tower_num_ly) as tower_num_avg
- from
- (select city_name,cbzx,count(*) as tower_num_cm from sqmdb_cost.tower_yuti where tower_code is not null and smonth='202204' group by city_name,cbzx) a
- join
- (select city_name,cbzx,count(*) as tower_num_ly from sqmdb_cost.tower_yuti where tower_code is not null and smonth='202112' group by city_name,cbzx) b
- on a.city_name=b.city_name and a.cbzx=b.cbzx) yt
- left join sqmdb_cost.dict_grid_code dgc on yt.city_name=dgc.city_name and trim(yt.cbzx)=trim(dgc.grid_name)) tn
- on tc.grid_code=tn.grid_code
- left join sqmdb_cost.dict_code_grid dcg on tc.grid_code=dcg.grid_code
|