《[计算机]经营分析报表接口配置维护表说明.docx》由会员分享，可在线阅读，更多相关《[计算机]经营分析报表接口配置维护表说明.docx（11页珍藏版）》请在三一文库上搜索。
1、.经营分析报表接口配置维护表说明region_code表：create table informix.region_code ( region_id integer, region_name varchar(20), code integer );表说明：是一张静态表region_idregion_idcode-1951565403赣州地区752-1819636323抚州地区759-1277628804吉安地区751-872231345新余市753-278404394九江市755-258403725宜春地区75630343799鹰潭市754363575679景德镇市740520456201上饶
2、地区7571001515574南昌市7501089441733萍乡市758使用对象：几乎每个接口文件nrmdb:geo_code表create table informix.geo_code ( region_name varchar(64), geo_name varchar(64), geo_code integer );维护说明：此表是一张预置的静态表Region_nameGeo_nameGeo_code赣州地区龙南县1赣州地区于都县2赣州地区全南县3赣州地区瑞金市4赣州地区宁都县5赣州地区上犹县6SITE_GEO_VIEW表：create view informix.site_geo_
3、view (int_id,geo_id,object_rdn,region_name,city_name) as select x0.int_id ,NVL (x1.zone_id ,x0.city_id ),x0.object_rdn ,x2.region_name ,x2.city_name from informix.objects x0 , informix.btssitemanager x1 ,informix.region_city_local x2 ,outer(informix.objects_manu x3 ) where (x0.int_id = x1.int_id ) A
4、ND (x0.city_id = x2.city_id ) ) AND (x1.zone_id = x3.int_id ) ) AND (x3.object_class = 40000 ) ) AND (x3.confirmed NOT IN (2 ,5 ) ) ;Int_id(基站intid)Geo_idObject_rdnRegion_nameCity_name451885764-1267667780108001:24:1-11吉安县天河吉安地区吉安县1574114412-1267667780108001:101:1-9吉安县万福吉安地区吉安县-1110496060-12676677801
5、08001:24:1-18吉安县卫生局吉安地区吉安县7495803769103102:JJBSC2:54九江市九江市15379007509103102:JJBSC2:55九江市九江市-10294514049103102:JJBSC2:56九江市九江市-83253356610103102:JJBSC1:42九江市九江市-9975631010103102:JJBSC3:45九江市九江市使用说明：不需要进行维护nrmdb:GEO_VIEW表：create view informix.geo_view (geo_id,geo_name,region_id) as select x0.city_id ,
6、x0.city_name ,x0.region_id from informix .region_city_local x0 union select x1.int_id ,x1.userlabel ,x1.old_id from informix.objects_manu x1 where (x1.object_class = 40000 ) AND (x1.confirmed NOT IN (2 ,5 ) ) ;Geo_idGeo_nameRegion_id-132351308莲花县1089441733-90178031万年县520456201-15421361新干县-1277628804
7、1南昌昌北区10015155743南昌城中区10015155744南昌高新区1001515574使用说明：此表不用维护nrmdb:site_geo_map表create table informix.site_geo_map ( int_id integer, geo_name varchar(32), geo_id integer );使用说明：需要每天维护一次，正常调度。delete site_geo_map;insert into site_geo_map(int_id, geo_name,geo_id)select a.int_id, b.geo_name, a.geo_id from
8、 SITE_GEO_VIEW a, GEO_VIEW b where a.geo_id = b.geo_id;Informix:10.17.126.8: 20 17 * * * /opt/BOCO.NPM/DB/task/jf_geo.sh /tmp/jf.log 2&1 VIEW_REGION_GEO_CODE表create view informix.view_region_geo_code (region_id,region_code,geo_name,geo_id,geo_code) as select x1.region_id ,x2.code ,x1.geo_name ,x1.ge
9、o_id ,x0.geo_code from informix.geo_code x0 ,informix.geo_view x1 ,npmdb: informix.region_code x2 where (x0.geo_name = x1.geo_name ) AND (x1.region_id = x2.region_id ) ) ;Region_idRegion_codeGeo_nameGeo_idGeo_code-1951565403752龙南县-20858834881-1951565403752于都县-16783925342-1951565403752全南县-15797273413
10、-1951565403752瑞金市-15314101714-1951565403752宁都县-13186189315-1951565403752上犹县-9111087966使用说明：此表不用维护view_a_traff表原来create view informix.view_a_traff (region_code,type,a_interface_traf_bh,a_interface_traf_d,first_result) as select (select x3.code from informix.region_code x3 where (x3.region_id = x0.ne_
11、id ) ) ,11 ,NVL (NVL (x1.a_interface_traf_bh ,0 )+ NVL (x2.a_interface_traf_bh ,0 ) ,0 ),NVL (NVL (x1.a_interface_traf_d ,0 )+ NVL (x2.a_interface_traf_d ,0 ) ,0 ),x0.first_result from npmuser.tpa_msc_mss_sum_view x0 ,outer(informix.view_a_traff_msc x1 ) ,outer(informix.view_a_traff_sw x2 ) where (x
12、0.ne2_type = -1 ) AND (x0.ne2_id = -1 ) ) AND (x0.sum_level = 1 ) ) AND (press_date = press_date ) ) AND (press_date = press_date ) ) AND (x0.ne_id = x1.ne_id ) ) AND (x0.ne_id= x2.ne_id ) ) AND (x0.ne_type = 10003 ) ) ;修改后：create view view_a_traff (region_code,type,a_interface_traf_bh,a_interface_t
13、raf_d,first_result) as select (select x3.code from region_code x3 where (x3.region_id = x0.ne_id ) ) ,11 ,NVL (x2.a_interface_traf_bh ,0 ),NVL (x2.a_interface_traf_d ,0 ),x0.first_result from tcc_ne_snap_7days x0 ,outer(view_a_traff_sw x2 ) where press_date = press_date AND x0.ne_id = x2.ne_id AND x
14、0.ne_type = 10003 ; tpa_msc_mss_sum_view表联通报表中用到的表view_a_traff_msc表create view informix.view_a_traff_msc (a_interface_traf_bh,a_interface_traf_d,ne_id,compress_date) as select x0.a_interface_traf ,x1.a_interface_traf ,x0.ne_id ,press_date from informix.tpa_unic_msc_sum x0 ,informix.tpa_unic_msc_sum
15、x1 where (x0.s_hour = 19 ) AND (press_date = press_date ) ) AND (x0.ne_type = 10003 ) ) AND (x0.sv_cat_id = 1 ) ) AND (x0.ne_type = x1.ne_type ) ) AND (x0.ne_id = x1.ne_id ) ) AND (x0.sv_cat_id = x1.sv_cat_id ) ) AND (x0.sum_level = 0 ) ) AND (x1.sum_level = 1 ) ) ;使用说明：此表不用维护。view_a_traff_sw表create
16、 view informix.view_a_traff_sw (a_interface_traf_bh,a_interface_traf_d,ne_id,compress_date) as select x0.a_traffic ,x1.a_traffic ,x0.ne_id ,press_date from npmuser.tpa_mss_localnet_sum x0 ,npmuser.tpa_mss_localnet_sum x1 where (x0.s_hour = 19 ) AND (press_date = press_date ) ) AND (x0.ne_type = 1000
17、3 ) ) AND (x0.sv_cat_id = -1 ) ) AND (x0.sv_cat_id = x1.sv_cat_id ) ) AND (x0.ne_type = x1.ne_type ) ) AND (x0.sum_level = 0 ) ) AND (x1.sum_level = 1 ) ) AND (x0.ne_id = x1.ne_id ) ) ;使用说明：此表不用维护。view_tcc_bts表create view informix.view_tcc_bts (ne_id,compress_date,cell_dn) as select x0.ne_id ,press_
18、date ,(x0.lac | _ ) | x0.cell_id ) from informix.tcc_bts x0 ;view_ovrfl表create view view_ovrfl ( tch_traffic,att_tch_ovrfl,tch_call_req,att_tch_ovrfl_h,tch_call_req_h,tch_call_req_nho,ne_id, first_result) asselect x1.tch_traffic , x1.att_tch_ovrfl, x1.tch_call_req,x0.att_tch_ovrfl, x0.tch_call_req,
19、x1.tch_call_req_nho, x0.ne_id ,x0.first_resultfrom tpc_unic_bts_ne x0 , tpa_unic_bts_sum x1where x0.s_hour=19 and press_date = press_dateand x0.ne_type = 300 and x1.sv_cat_id = -1 and x1.sum_level = 1and x0.ne_type = x1.ne_type and x0.ne_id = x1.ne_id ;使用说明：此表不用维护。view_cell_free_fen_zy表create view i
20、nformix.view_cell_free_fen_zy (first_result,region_code,geo_code,type,cell_id,zh_label,bsc_name,sum,sdcch_nbr,tch_nbr,site_type) as select x0.first_result ,(select x1.code from informix.region_code x1 where (x1.region_id = x0.region_id ) ) ,(select x2.geo_code from nrmdb:informix.view_region_geo_cod
21、e x2 where (x2.geo_id = x0.city_id ) ) ,11 ,(x0.lac | _ ) | x0.cell_id ) ,x0.zh_label ,x0.bsc_name ,x0.sum ,x0.sdcch_nbr ,x0.tch_nbr ,x0.site_type from informix.cell_free_fen_zy x0 ;附：create table informix.cell_free_fen_zy ( first_result datetime year to second, cell_id integer, lac integer, zh_label varchar(40), site_name varchar(40), region_id integer, region_name varchar(40), city_id integer, city_name varchar(40), related_bsc integer, bsc_name varchar(40), sum integer, sdcch_nbr integer, tch_nbr integer, site_type varchar(40) );；