123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 |
- --drop table sqmdb_cost.tower_tmp
- CREATE TABLE sqmdb_cost.tower_tmp (
- sdate numeric NULL,
- service_tracking_id varchar NULL,
- soperator varchar NULL,
- city_operator varchar NULL,
- area_operator varchar NULL,
- city_service varchar NULL,
- city_site varchar NULL,
- site_name varchar NULL,
- site_addr_id varchar NULL,
- require_tracking_id varchar NULL,
- service_property varchar NULL,
- startdate varchar NULL,
- production_type varchar NULL,
- room_type varchar NULL,
- tower_user_count numeric NULL,
- room_user_count numeric NULL,
- pt_user_count numeric NULL,
- total_cost numeric NULL,
- changd_cost numeric NULL,
- tower_share_cost numeric NULL,
- room_share_cost numeric NULL,
- pt_share_cost numeric NULL,
- stype varchar NULL
- );
- --delete from sqmdb_cost.tower3 where sdate='202207'
- insert into sqmdb_cost.tower3
- select *,
- case
- when stype in ('室分','微站') then null
- when stype in ('新建及共享改造','存量注入') and coalesce(tower_user_count,0)<=1 and coalesce(room_user_count,0)<=1 and coalesce(pt_user_count,0)<=1 then '独享'
- else '共享' end,
- coalesce(tower_share_cost,0)+coalesce(room_share_cost,0)+coalesce(pt_share_cost,0)
- from sqmdb_cost.tower_tmp
- --truncate table sqmdb_cost.tower_tmp
- select yt.city_name,dcg.grid_name,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 yt.cbzx=dgc.grid_name
- left join sqmdb_cost.dict_code_grid dcg on dgc.grid_code=dcg.grid_code
|