-- customer_service.cfg_p_netconf_std 更新
select
'' as id,
city_code as city_id,
city_name as city_name ,
district_name as area_id,
'' as core_ne_name,
'' as radio_ne_name,
bbu_name as site_name,
enbid as site_id,
cell_id as cell_no,
cell_name as cell_name ,
46001000000000 + enbid * 256 + cell_id as cell_id ,
enbid * 256 + cell_id as ecgi,
'' as gnb_cu_id,
'' as gnb_du_id,
'' as gsm_cell_id,
4 as network_type,
'' as type_5g,
'' as tac,
(case
station_type when '宏站' then '室外'
else '室内'
end) as cover_type,
'' as nodeb_type,
'' as rac,
'' as lac,
'' as grid,
scene as areatype,
'' as sub_areatype,
'' as area3,
lon as longitude,
lat as latitude,
vender as vendor,
height as height ,
m_downtilt + e_downtilt as downtilt,
direction as azimuth,
down_freq as fr,
'' as scramber,
'河北省' as province_id,
'' as bsbusip,
'' as bsbusip_1,
'' as isanchor,
construction as "operator",
'' as isshare,
'' as slice_type
from
cfm.cfg_0_4g_siteinfo cgs
-- customer_service.cfg_p_netconf_std 更新
select
'' as id,
city_code as city_id,
city_name as city_name ,
district_name as area_id,
'' as core_ne_name,
'' as radio_ne_name,
station_name as site_name,
gnbid as site_id,
cell_id as cell_no,
cell_name as cell_name ,
4600100000000000 + gnbid * 4096 + cell_id as cell_id ,
gnbid * 4096 + cell_id as ecgi,
'' as gnb_cu_id,
'' as gnb_du_id,
'' as gsm_cell_id,
4 as network_type,
'' as type_5g,
'' as tac,
(case
station_type when '宏站' then '室外'
else '室内'
end) as cover_type,
'' as nodeb_type,
'' as rac,
'' as lac,
'' as grid,
scene as areatype,
'' as sub_areatype,
'' as area3,
lon as longitude,
lat as latitude,
vender as vendor,
height as height ,
m_downtilt + e_downtilt as downtilt,
direction as azimuth,
down_freq as fr,
'' as scramber,
'河北省' as province_id,
'' as bsbusip,
'' as bsbusip_1,
'' as isanchor,
construction as "operator",
'' as isshare,
'' as slice_type
from
cfm.cfg_0_5g_siteinfo cgs
select '' as id, city_code as city_id, city_name as city_name , district_name as area_id, '' as core_ne_name, '' as radio_ne_name, bbu_name as site_name, enbid as site_id, cell_id as cell_no, cell_name as cell_name , 46001000000000 + enbid * 256 + cell_id as cell_id , enbid * 256 + cell_id as ecgi, '' as gnb_cu_id, '' as gnb_du_id, '' as gsm_cell_id, 4 as network_type, '' as type_5g, '' as tac, (case station_type when '宏站' then '室外' else '室内' end) as cover_type, '' as nodeb_type, '' as rac, '' as lac, '' as grid, scene as areatype, '' as sub_areatype, '' as area3, lon as longitude, lat as latitude, vender as vendor, height as height , m_downtilt + e_downtilt as downtilt, direction as azimuth, down_freq as fr, '' as scramber, '河北省' as province_id, '' as bsbusip, '' as bsbusip_1, '' as isanchor, construction as "operator", '' as isshare, '' as slice_type from cfm.cfg_0_4g_siteinfo cgs;
select '' as id, city_code as city_id, city_name as city_name , district_name as area_id, '' as core_ne_name, '' as radio_ne_name, station_name as site_name, gnbid as site_id, cell_id as cell_no, cell_name as cell_name , 4600100000000000 + gnbid * 4096 + cell_id as cell_id , gnbid * 4096 + cell_id as ecgi, '' as gnb_cu_id, '' as gnb_du_id, '' as gsm_cell_id, 4 as network_type, '' as type_5g, '' as tac, (case station_type when '宏站' then '室外' else '室内' end) as cover_type, '' as nodeb_type, '' as rac, '' as lac, '' as grid, scene as areatype, '' as sub_areatype, '' as area3, lon as longitude, lat as latitude, vender as vendor, height as height , m_downtilt + e_downtilt as downtilt, direction as azimuth, down_freq as fr, '' as scramber, '河北省' as province_id, '' as bsbusip, '' as bsbusip_1, '' as isanchor, construction as "operator", '' as isshare, '' as slice_type from cfm.cfg_0_5g_siteinfo cgs;
select cell_id, site_name, city_name , longitude , latitude , tac from customer_service.cfg_p_netconf_std;
psql
\c sqmmt
# 5G工参
\copy (select '' as id, city_code as city_id, city_name as city_name , district_name as area_id, '' as core_ne_name, '' as radio_ne_name, station_name as site_name, gnbid as site_id, cell_id as cell_no, cell_name as cell_name , 4600100000000000 + gnbid * 4096 + cell_id as cell_id , gnbid * 4096 + cell_id as ecgi, '' as gnb_cu_id, '' as gnb_du_id, '' as gsm_cell_id, 4 as network_type, '' as type_5g, '' as tac, (case station_type when '宏站' then '室外' else '室内' end) as cover_type, '' as nodeb_type, '' as rac, '' as lac, '' as grid, scene as areatype, '' as sub_areatype, '' as area3, lon as longitude, lat as latitude, vender as vendor, height as height , m_downtilt + e_downtilt as downtilt, direction as azimuth, down_freq as fr, '' as scramber, '河北省' as province_id, '' as bsbusip, '' as bsbusip_1, '' as isanchor, construction as "operator", '' as isshare, '' as slice_type from cfm.cfg_0_5g_siteinfo cgs) TO '/data1/cfg_0_5g_siteinfo.csv' with (FORMAT csv, NULL '',DELIMITER ',', HEADER true);
# 4G工参
\copy (select '' as id, city_code as city_id, city_name as city_name , district_name as area_id, '' as core_ne_name, '' as radio_ne_name, bbu_name as site_name, enbid as site_id, cell_id as cell_no, cell_name as cell_name , 46001000000000 + enbid * 256 + cell_id as cell_id , enbid * 256 + cell_id as ecgi, '' as gnb_cu_id, '' as gnb_du_id, '' as gsm_cell_id, 4 as network_type, '' as type_5g, '' as tac, (case station_type when '宏站' then '室外' else '室内' end) as cover_type, '' as nodeb_type, '' as rac, '' as lac, '' as grid, scene as areatype, '' as sub_areatype, '' as area3, lon as longitude, lat as latitude, vender as vendor, height as height , m_downtilt + e_downtilt as downtilt, direction as azimuth, down_freq as fr, '' as scramber, '河北省' as province_id, '' as bsbusip, '' as bsbusip_1, '' as isanchor, construction as "operator", '' as isshare, '' as slice_type from cfm.cfg_0_4g_siteinfo cgs) TO '/data1/cfg_0_4g_siteinfo.csv' with (FORMAT csv, NULL '',DELIMITER ',', HEADER true);
# 备份
\copy (select * from customer_service.cfg_p_netconf_std cpns) TO '/data1/cfg_p_netconf_std_20220704_bk.csv' with (FORMAT csv, NULL '',DELIMITER ',', HEADER true);
\copy (select * from customer_service.cfg_cell_info) TO '/data1/cfg_cell_info_20220704_bk.csv' with (FORMAT csv, NULL '',DELIMITER ',', HEADER true);
# cfg_cell_info
\copy (select cell_id, site_name, city_name , longitude , latitude , tac from customer_service.cfg_p_netconf_std) TO '/data1/cfg_cell_info.csv' with (FORMAT csv, NULL '',DELIMITER ',', HEADER true);
