chengben/Scripts/铁塔表更新.sql

--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