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

[计算机]经营分析报 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 接口配置维护表说明081202 经营分析报表接口配置维护表说明 region_code表: create table "informix".region_code ( region_id integer, region_name varchar(20), code integer ); 表说明:是一张静态表 region_id region_id code -1951565403 赣州地区 752 -1819636323 抚州地区 759 -1277628804 吉安地区 751 -872231345 新余市 753 -278404394 九江市 755 -258403725 宜春地区 756 30343799 鹰潭市 754 363575679 景德镇市 740 520456201 上饶地区 757 1001515574 南昌市 750 1089441733 萍乡市 758 使用对象:几乎每个接口文件 nrmdb:geo_code表 create table "informix".geo_code ( region_name varchar(64), geo_name varchar(64), geo_code integer ); 维护说明:此表是一张预置的静态表 Region_name Geo_name Geo_code 赣州地区 龙南县 1 赣州地区 于都县 2 赣州地区 全南县 3 赣州地区 瑞金市 4 赣州地区 宁都县 5 赣州地区 上犹县 6 SITE_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_id Object_rdn Region_name City_name 451885764 -1267667780 108001:24:1-11吉安地区 吉安县 吉安县天河 1574114412 -1267667780 108001:101:1-9吉安地区 吉安县 吉安县万福 -1110496060 -1267667780 108001:24:1-18吉安地区 吉安县 吉安县卫生局 749580376 9 103102:JJBSC2:54 九江市 九江市 1537900750 9 103102:JJBSC2:55 九江市 九江市 -1029451404 9 103102:JJBSC2:56 九江市 九江市 -832533566 10 103102:JJBSC1:42 九江市 九江市 -99756310 10 103102: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_id Geo_name Region_id -132351308 莲花县 1089441733 -90178031 万年县 520456201 -15421361 新干县 -1277628804 1 南昌昌北区 1001515574 3 南昌城中区 1001515574 4 南昌高新区 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_id Region_code Geo_name Geo_id Geo_code -1951565403 752 龙南县 -2085883488 1 -1951565403 752 于都县 -1678392534 2 -1951565403 752 全南县 -1579727341 3 -1951565403 752 瑞金市 -1531410171 4 -1951565403 752 宁都县 -1318618931 5 -1951565403 752 上犹县 -911108796 6 使用说明:此表不用维护 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) );