[计算机]经营分析报表接口配置维护表说明
(11页)
本资源提供全文预览,点击全文预览即可全文预览,如果喜欢文档就下载吧,查找使用更方便哦!
14.90 积分
经营分析报表接口配置维护表说明region_code表:create table "informix".region_code ( region_id integer, region_name varchar(20), code integer );表说明:是一张静态表region_id region_idcode-1951565403赣州地区752-1819636323抚州地区759-1277628804吉安地区751-872231345新余市753-278404394九江市755-258403725宜春地区75630343799鹰潭市754363575679景德镇市740520456201上饶地区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_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 ) AND (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-1267667780108001: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 ,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新干县-12776288041南昌昌北区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 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.geo_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-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_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 ((((((((x0.ne2_type = -1 ) AND (x0.ne2_id = -1 ) ) AND (x0.sum_level = 1 ) ) AND (x0.compress_date = x1.compress_date ) ) AND (x0.compress_date = x2.compress_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.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 x0.compress_date = x2.compress_date AND x0.ne_id = x2.ne_id AND x0.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 ,x0.compress_date from "informix".tpa_unic_msc_sum x0 ,"informix".tpa_unic_msc_sum x1 where (((((((((x0.s_hour = 19 ) AND (x1.compress_date = x0.compress_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 "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 ,x0.compress_date from "npmuser".tpa_mss_localnet_sum x0 ,"npmuser".tpa_mss_localnet_sum x1 where (((((((((x0.s_hour = 19 ) AND (x1.compress_date = x0.compress_date ) ) 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_date,cell_dn) as select x0.ne_id ,x0.compress_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) as select x1.tch_traffic , x1.att_tch_ovrfl, x1.tch_call_req,x0.att_tch_ovrfl, x0.tch_call_req, x1.tch_call_req_nho, x0.ne_id ,x0.first_result from tpc_unic_bts_ne x0 , tpa_unic_bts_sum x1 where x0.s_hour=19 and x0.compress_date = x1.compress_date and x0.ne_type = 300 and x1.sv_cat_id = -1 and x1.sum_level = 1 and 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".region_code x1 where (x1.region_id = x0.region_id ) ) ,(select x2.geo_code from nrmdb:"informix".view_region_geo_code 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) ); 关 键 词: 计算机 经营 分析 报表 接口 配置 维护 说明
天天文库所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。