[计算机]经营分析报表接口配置维护表说明

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景德镇市74

2、0520456201上饶地区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

3、 view "informix".site_geo_view (int_id,geo_id,object_rdn,region_name,city_name) as select _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_loc

4、al x2 ,outer("informix".objects_manu x3 ) where (_id = _id ) AND (x0.city_id = x2.city_id ) ) AND (x1.zone_id = _id ) ) AND (x3.object_class = 40000 ) ) AND (x3.confirmed NOT IN (2 ,5 ) ) ;Int_id(基站intid)Geo_idObject_rdnRegion_nameCity_name451885764-1267667780108001:24:1-

5、11吉安县天河吉安地区吉安县1574114412-1267667780108001:101:1-9吉安县万福吉安地区吉安县-1110496060-1267667780108001:24:1-18吉安县卫生局吉安地区吉安县7495803769103102:JJBSC2:54九江市九江市15379007509103102:JJBSC2:55九江市九江市-10294514049103102:JJBSC2:56九江市九江市-83253356610103102:JJBSC1:42九江市九江市-9975631010103102:JJBSC3:45九江市九江市使用说明：不需要进行维护nrmdb:GEO_VI

6、EW表：create view "informix".geo_view (geo_id,geo_name,region_id) as select x0.city_id ,x0.city_name ,x0.region_id from "informix" .region_city_local x0 union select _id ,x1.userlabel ,x1.old_id from "informix".objects_manu x1 where (x1.object_class = 40000 ) AND (x

7、1.confirmed NOT IN (2 ,5 ) ) ;Geo_idGeo_nameRegion_id-132351308莲花县1089441733-90178031万年县520456201-15421361新干县-12776288041南昌昌北区10015155743南昌城中区10015155744南昌高新区1001515574使用说明：此表不用维护nrmdb:site_geo_map表create table "informix".site_geo_map ( int_id integer, geo_name varchar(32), geo_id integer

8、);使用说明：需要每天维护一次，正常调度。delete site_geo_map;insert into site_geo_map(int_id, geo_name,geo_id)select _id, b.geo_name, a.geo_id from SITE_GEO_VIEW a, GEO_VIEW b where a.geo_id = b.geo_id;Informix:: 20 17 * * * /opt/BOCO.NPM/DB/task/jf_geo.sh >> /tmp/jf.log 2>&1 VIEW_REGION_GE

9、O_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.geo_id ,x0.geo_code from "informix".geo_code x0 ,"informix".geo_view x1 ,npmdb: "informix".region_code x2 where

10、 (x0.geo_name = x1.geo_name ) AND (x1.region_id = x2.region_id ) ) ;Region_idRegion_codeGeo_nameGeo_idGeo_code-1951565403752龙南县-20858834881-1951565403752于都县-16783925342-1951565403752全南县1951565403752瑞金市1951565403752宁都县1951565403752上犹县-9111087966使用说明：此表不用维护view_a

11、_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_id ) ) ,11 ,NVL (NVL (x1.a_interface_traf_bh ,0 )+ NVL (x2.a_interface_traf_bh

12、,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 (x0.ne2_type = -1 ) AND (x0.ne2_id = -1 ) ) A

13、ND (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_traf_d,first_result) as select (select x3.co

14、de 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 x0.ne_type = 10003 ; tpa_msc_mss_sum_view表联通

15、报表中用到的表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 x1 wher

16、e (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 view &

17、quot;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_da

18、te ) ) AND (x0.ne_type = 10003 ) ) 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

19、_date,cell_dn) as select x0.ne_id ,press_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

20、_tch_ovrfl, x1.tch_call_req,x0.att_tch_ovrfl, x0.tch_call_req, 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 "informix".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".re