--经分_用户数 --drop table sqmdb_cost.baobiao_jf_yonghush create table sqmdb_cost.baobiao_jf_yonghush( kpi_code varchar, kpi_name varchar, month_id varchar, prov_id numeric, pro_name varchar, grade varchar, city_id varchar, city_name varchar, kpi_value numeric, m_ly_value numeric, preinfo1 varchar, preinfo2 varchar, preinfo3 varchar, preinfo4 varchar, preinfo5 varchar, preinfo6 varchar) --经分_用户数（北10指标，手动整理 固网用户数202203.xlsx 移网用户数202203.xlsx） create table sqmdb_cost.baobiao_jf_yonghush_b10( smonth varchar, sname varchar, user_kd numeric, user_yw numeric) --IP端口 --drop table sqmdb_cost.baobiao_ipduankou create table sqmdb_cost.baobiao_ipduankou( kpi_code varchar, kpi_name varchar, month_id varchar, pro_name varchar, prov_id numeric, grade varchar, city_desc varchar, area_no varchar, kpi_value numeric, m_ly_value numeric, tongbi_daoda numeric, by_szl numeric, qn_szl numeric, tongbi_szl numeric, byjz numeric, syjz numeric, ljjz numeric) --IP端口（北10指标，手动整理 移动业务发展及业务量本月月报分省-IP接入端口总数202203.xls） --drop table sqmdb_cost.baobiao_ipduankou_b10 create table sqmdb_cost.baobiao_ipduankou_b10( smonth varchar, sname varchar, kpi_value numeric, m_ly_value numeric, tongbi_daoda numeric, by_szl numeric, qn_szl numeric, tongbi_szl numeric, byjz numeric, syjz numeric, ljjz numeric) --kuandai_ftth create table sqmdb_cost.kuandai_ftth( local_net_name varchar, area_name varchar, areal_type_name varchar, dslam_port_count numeric, dslam_port_idle_count numeric, dslam_port_p varchar, fttb_port_count numeric, fttb_port_sts_count numeric, fttb_port_p varchar, pon_ad_port_count numeric, pon_ad_port_sts_count numeric, pon_ad_port_p varchar, pon_lan_port_count numeric, pon_lan_port_sts_count numeric, pon_lan_port_p varchar, ftth_port_count numeric, ftth_port_sts_count numeric, ftth_port_p varchar, ftth_zj_port_count numeric, ftth_zj_port_sts_count numeric, ftth_zj_port_p varchar, ftth_hz_port_count numeric, ftth_hz_port_sts_count numeric, ftth_hz_port_p varchar, sdate timestamp) --省内成本（自定义，后续可增加，保留现有字段） create table sqmdb_cost.chengben_shengnei( smonth varchar, stype varchar, sname varchar, income numeric, cost_total numeric, cost_fix numeric, cost_liao numeric) --网格字典 --drop table sqmdb_cost.dict_grid; create table sqmdb_cost.dict_grid( city_code numeric, city_name varchar, area_code numeric, area_name varchar, city_pro varchar, grid_city_code numeric, grid_city_name varchar, grid_code numeric, grid_name varchar, grid_city_kd varchar, grid_name_kd varchar, city_sname varchar) --活动ONU --DROP TABLE sqmdb_cost.active_onu; create table sqmdb_cost.active_onu( smonth varchar, province_code numeric, city_code numeric, city_name varchar, active_onu_num numeric) --装机移机工单数 create table sqmdb_cost.zhuangji( smonth varchar, city_name varchar, area_name varchar, order_num numeric, order_type varchar) --合同预提表（带成本中心） --drop FOREIGN table sqmdb_cost.hetong_yuti_e; --drop table sqmdb_cost.hetong_yuti; --alter foreign table sqmdb_cost.hetong_yuti_e options(set filename '/data/chengben_e/hetong_yuti.suc'); create foreign table sqmdb_cost.hetong_yuti_e( --create table sqmdb_cost.hetong_yuti( sob_id numeric, sob_code varchar, sob_name varchar, pro_code varchar, pro_name varchar, le_cate_code varchar, le_cate_name varchar, le_code varchar, le_name varchar, com_code varchar, com_name varchar, contract_com_code varchar, contract_com_name varchar, contract_dept_code numeric, contract_dept_name varchar, period_name varchar, period_year numeric, period_num varchar, period_id varchar, prebill_arrair_no varchar, prebill_contract_no varchar, contract_no_local varchar, contract_no_global varchar, contract_name varchar, contract_desc varchar, repository_name_unicom varchar, repository_name_vendor varchar, contract_type_code numeric, contract_type_name varchar, contract_status_code numeric, contract_status_name varchar, contract_changetype_code numeric, contract_changetype_name varchar, contract_cate_l1_code numeric, contract_cate_l1_name varchar, contract_cate_l2_code numeric, contract_cate_l2_name varchar, contract_cate_l3_code numeric, contract_cate_l3_name varchar, prebillno varchar, vendor_name varchar, start_date varchar, end_date varchar, relatedparty_name varchar, projectaccount_desc varchar, billrowid varchar, longvalue numeric, prebill_type_name varchar, prebill_cate_l1_code numeric, prebill_cate_l1_name varchar, prebill_cate_l2_code numeric, prebill_cate_l2_name varchar, cost_center_id varchar, cost_center_name varchar, profess_id varchar, profess_name varchar, cost_index_id varchar, cost_index_name varchar, debit_account_code varchar, debit_account_desc varchar, credit_account_code varchar, credit_account_desc varchar, contract_taker_code numeric, contract_taker_name varchar, contract_taker_mobile varchar, contract_currency_code numeric, contract_currency_name varchar, contract_exchange_rate numeric, contract_amount numeric, contract_rmbmoney numeric, amount_current_amortize numeric, amount_provision numeric, amount_offset numeric, amount_current_provision numeric, amount_accruedm numeric, amount_beach numeric, amount_handle numeric, prebill_record_id numeric, prebill_record_rowid numeric, prebill_certificate_id numeric, prebill_period_id numeric, prebill_contract_id numeric, orgouname varchar, customer_account varchar, customer_account_desc varchar, money_benefit numeric, remark varchar, dwa_creation_date varchar, month_id varchar, day_id varchar, prov_id varchar, zd1 varchar) SERVER pg_file_server OPTIONS (filename '/data/jyc/chengben/hetong_yuti_suc', format 'text', header 'false', delimiter '|', null ''); --合同基本信息，带当前版本/历史版本 create foreign table sqmdb_cost.hetong_jibenxinxi_e( sob_id numeric, sob_code varchar, sob_name varchar, pro_code varchar, pro_name varchar, le_cate_code varchar, le_cate_name varchar, le_code varchar, le_name varchar, com_code varchar, com_name varchar, dept_code numeric, dept_name varchar, vision_flag varchar, arrair_no varchar, contract_no_local varchar, contract_no_global varchar, contract_name varchar, contract_desc varchar, repository_name_unicom varchar, repository_name_vendor varchar, contract_type_code numeric, contract_type_name varchar, arrair_status_code numeric, arrair_status_name varchar, contract_status_code numeric, contract_status_name varchar, contract_cate_l1_code numeric, contract_cate_l1_name varchar, contract_cate_l2_code numeric, contract_cate_l2_name varchar, contract_cate_l3_code numeric, contract_cate_l3_name varchar, contract_select_code numeric, contract_select_name varchar, contract_degree_code numeric, contract_degree_name varchar, contract_priority_code numeric, contract_priority_name varchar, contract_payment_type_code numeric, contract_payment_type_name varchar, contract_approve_level_code numeric, contract_approve_level_name varchar, contract_changetype_code numeric, contract_changetype_name varchar, contract_timelimit_code numeric, contract_timelimit_name varchar, contract_budget_type_code numeric, contract_budget_type_name varchar, perform_type_code numeric, perform_type_name varchar, is_renew numeric, account_rate varchar, taker_code numeric, taker_name varchar, taker_mobile varchar, sign_name varchar, currency_code numeric, currency_name varchar, exchange_rate numeric, finance_amount numeric, finance_rmbmoney numeric, finance_gbkmoney varchar, finance_total numeric, finance_vat_rate varchar, finance_un_vat_amount numeric, finance_rmb_un_vat_amount numeric, finance_vatax numeric, finance_rmb_vatax numeric, arrair_amount numeric, arrair_amount_gbk4 varchar, arrair_pay_plan numeric, arrair_pay_actual numeric, arrair_balance numeric, audit_money4 numeric, audit_ratio4 numeric, audit_sum4 numeric, audit_vatax numeric, audit_unvat_amount numeric, audit_vat_rate varchar, fixed_amount numeric, arrair_id numeric, arrair_id_contract numeric, contract_id numeric, contract_id_finance numeric, contract_id_arrair_now numeric, contract_id_arrair_origin numeric, provinceid numeric, companyid numeric, companyname varchar, draftouttab varchar, performtab varchar, last_approver_code numeric, last_approver_name varchar, sign_status_code numeric, sign_date varchar, start_date varchar, end_date varchar, sign_dept_name varchar, approval_date_send varchar, approval_date varchar, relationid numeric, relationname varchar, arrair_id_old numeric, purchase_result_id varchar, arrair_deleted_flag numeric, content_deleted_flag numeric, creation_date varchar, created_by varchar, last_update_date varchar, dwa_creation_date varchar, month_id varchar, day_id varchar, prov_id varchar, zd1 varchar) SERVER pg_file_server OPTIONS (filename '/data/jyc/chengben/hetong_jibenxinxi_suc', format 'text', header 'false', delimiter '|', null ''); --alter foreign table sqmdb_cost.hetong_jibenxinxi_e options(set filename '/data/chengben_e/hetong_jibenxinxi.suc'); --新合同建表（合同基本信息关联合同预提中的成本中心，计算合同月数和月租金，后经过Python整理） --DROP TABLE sqmdb_cost.hetong_new create table sqmdb_cost.hetong_new( ht_date varchar, city_name varchar, sid varchar, sname varchar, bdate timestamp, edate timestamp, cost numeric, cname varchar, dname varchar, cfname varchar, cbzx varchar, ename varchar, signdate timestamp, status varchar, stype varchar, taker_code varchar, taker_name varchar, taker_mobile varchar, nmonth numeric, yuezujin numeric, is_tc varchar) --庞翀载频载扇业务量表 create table sqmdb_cost.zpzs( smonth varchar, city_name varchar, grid_name varchar, zps_num_234g numeric, yewul numeric, zps_num_5g numeric) --电费-预提明细 --drop foreign table sqmdb_cost.ytmx_e; create foreign table sqmdb_cost.ytmx_e( org_name varchar, unit_name varchar, "period" varchar, build_name varchar, group_name varchar, resource_id varchar, is5g varchar, build_code varchar, build_type_name varchar, meter_name varchar, power_value numeric, ratio_value numeric, meter_code varchar, meter_type_name varchar, meter_state_name varchar, source_type_name varchar, electric_price numeric, last_read_time date, read_time date, day_cost numeric, day_value numeric, last_read_value numeric, read_value numeric, accrued_value numeric, cost_unit_name varchar, cost_name varchar, account_name varchar, index_name varchar, current_number varchar, differ_cost numeric, actual_cost numeric, offset_cost numeric, adjust_cost numeric, account_cost numeric, accrued_cost numeric, accrued_type_name varchar, accrued_state_name varchar, check_value numeric, check_time date, check_day_value numeric) SERVER pg_file_server OPTIONS (filename '/data/jyc/chengben/dianfei/ytmx_202103.csv', format 'csv', header 'true', delimiter ',', null '',encoding 'gb18030'); --预提明细（新格式） create table sqmdb_cost.ytmx_new( smonth varchar, province varchar, city_name varchar, area_name varchar, build_type_name varchar, build_code_short numeric, build_code varchar, build_name varchar, meter_type_name varchar, cost_unit_name varchar, accrued_cost numeric, accrued_power numeric) --铁塔放置点表（每周给庞翀算的铁塔站址关联率输出结果表：008铁塔站址校验_表12合并去重.xlsx） create table sqmdb_cost.tower_fzd( city_name varchar, fzd_id varchar, tower_code varchar, st varchar) --铁塔成本中心 create table sqmdb_cost.dict_tower_cbzx( tower_code varchar, tower_name varchar, cbzx varchar) --华为中兴45G-bbu能耗 create table sqmdb_cost.nenghao_bbu( smonth varchar, changjia varchar, net_type varchar, sitename varchar, siteid varchar, nh_kwh numeric) --华为中兴45G-rru能耗 --drop foreign table sqmdb_cost.nenghao_rru_e create foreign table sqmdb_cost.nenghao_rru_e( --create table sqmdb_cost.nenghao_rru( smonth varchar, changjia varchar, net_type varchar, cellname varchar, siteid varchar, kid varchar, ci varchar, nh_kwh numeric) SERVER pg_file_server OPTIONS (filename '/data/jyc/chengben/nenghao_rru_new_202203.csv', format 'csv', header 'true', delimiter ',', null '',encoding 'gb18030'); --马亮提供的局站与aau对应关系 create foreign table sqmdb_cost.station_aau_e( --create table sqmdb_cost.station_aau( sid numeric, sname varchar, reslvl varchar, city_code numeric, area_code numeric, factory varchar, equip_model varchar, property_type varchar, classcode varchar, profession varchar, station_sid numeric, maintenace_person varchar, device_maintenace_person varchar, cell_oid varchar, siteid varchar, kid varchar) SERVER pg_file_server OPTIONS (filename '/data/jyc/chengben/aau_new_20220418.csv', format 'csv', header 'true', delimiter ',', null '',encoding 'gb18030'); --马亮提供的局站与rru对应关系 create foreign table sqmdb_cost.station_rru_e( --create table sqmdb_cost.station_rru( sid numeric, sname varchar, reslvl varchar, city_code numeric, area_code numeric, factory varchar, equip_model varchar, property_type varchar, classcode varchar, profession varchar, station_sid numeric, maintenace_person varchar, device_maintenace_person varchar, cell_oid varchar) SERVER pg_file_server OPTIONS (filename '/data/jyc/chengben/RRU20220418.csv', format 'csv', header 'false', delimiter ',', null ''); --载频载扇_地市 create table sqmdb_cost.zpzs_city( smonth varchar, city_name varchar, zps_g900 numeric, zps_g1800 numeric, zps_w numeric, zps_lte_fdd numeric, zps_lte_tdd numeric, zps_5g numeric, zps_total numeric) --载频载扇_皮长公里（北10指标，手动整理 通信能力主要指标分省月报202203.xls） --注意原始指标里23g载频载扇数单位是：万个，4g单位是：个，计算载频载扇总数时需要统一为：万个 create table sqmdb_cost.zpzs_gl_b10( smonth varchar, sname varchar, zps_gsm numeric, zps_w numeric, zps_lte_fdd numeric, zps_lte_tdd numeric, total_gl numeric, zps_total_no5g numeric) --省内预算进度 create table sqmdb_cost.yusuan_shengnei( smonth varchar, city_name varchar, total_cost numeric, total_ys numeric, cost_rate numeric) --单olt承载用户数 create table sqmdb_cost.olt_user( smonth varchar, city_code numeric, city_name varchar, area_code numeric, area_name varchar, kd_num numeric, olt_num numeric, per_olt_user numeric) --单OLT承载活动ONU数（原始数据手动添加了smonth和city_code） create table sqmdb_cost.olt_onu( smonth varchar, prov numeric, city numeric, city_code numeric, olt_active_onu numeric) --宽带用户光衰质差率 create table sqmdb_cost.kd_low_quality( smonth varchar, city_name varchar, low_27 numeric, onu_all numeric, low_rate numeric) --31项指标 create table sqmdb_cost.rpt_kpi31( month_id varchar, zb_type varchar, prov_name varchar, city_id numeric, city_name varchar, grid_id numeric, grid_name varchar, zb_code numeric, zb_name varchar, qz_fz numeric, qz_fm numeric, by_qz numeric) --铁塔成本中心（暂估预提明细表：塔类去重，室分不去重） --drop table sqmdb_cost.tower_cbzx create table sqmdb_cost.tower_cbzx( smonth varchar, city_name varchar, cbzx varchar, tower_code varchar, stype varchar) --新字典-a（各系统不同的网格名称对应相同的网格编码） create table sqmdb_cost.dict_grid_code( city_name varchar, grid_name varchar, cbzx_code numeric, grid_code numeric, grid_sc varchar) --新字典-b（唯一网格编码对应规范的地市名称、网格名称） create table sqmdb_cost.dict_code_grid( grid_code numeric, cbzx_code numeric, city_name varchar, city_code numeric, area_name varchar, area_code numeric, grid_name varchar, cbzx_name varchar) --各地市理论电费单价配置表 create table sqmdb_cost.cfg_eprice_city( smonth varchar, city_name varchar, city_eprice numeric ) --组织架构字典（大屏指标用） create table sqmdb_cost.dict_zzjg( area_id numeric, area_name varchar, p_id numeric, p_name varchar ) --地市字典（各系统不同的地市名字对应地市编码） create table sqmdb_cost.dict_city( city_name varchar, city_code numeric ) --合同白名单（剔除表，合同校验需剔除） create table sqmdb_cost.hetong_white( tc_id varchar, hetong_sid varchar ) --资源系统局站表（新，适配接口数据） --drop table sqmdb_cost.station3 CREATE TABLE sqmdb_cost.station3 ( station_sid numeric, station_sname varchar, city_name varchar, area_name varchar, property_type varchar, property_unit varchar, longitude numeric, latitude numeric, res_level varchar, station_type varchar, station_level varchar, tower_code varchar, grade_name varchar, wx_type varchar, oss_station numeric ); --资源系统局站表接口数据 --drop table sqmdb_cost.station create table sqmdb_cost.station( station_sid numeric, station_sname varchar, city_code numeric, area_code numeric, station_type numeric, baidu_longitude numeric, baidu_latitude numeric, property_type numeric, property_unit numeric, stationlvl numeric, tower_code varchar, grade_name varchar, wx_type varchar, oss_station numeric, station_level numeric) --指标配置项（指标名称，输出值保留小数点位数等） create table sqmdb_cost.cfg_kpi( zb_type varchar, zb_code numeric, zb_name varchar, unit varchar, round_n numeric) --省内成本（成本报表用）accum-累计值，month-当月值 --drop table sqmdb_cost.chengben_heb_month; create table sqmdb_cost.chengben_heb_accum( --create table sqmdb_cost.chengben_heb_month( smonth varchar, kpi_code varchar, kpi_name varchar, heb_city1 numeric, heb_city2 numeric, heb_city3 numeric, heb_city4 numeric, heb_city5 numeric, heb_city6 numeric, heb_city7 numeric, heb_city8 numeric, heb_city9 numeric, heb_city10 numeric, heb_city11 numeric, heb_city12 numeric, heb_city13 numeric) --北十成本（成本报表用）accum-累计值，month-当月值 --drop table sqmdb_cost.chengben_b10_month; create table sqmdb_cost.chengben_b10_accum( --create table sqmdb_cost.chengben_b10_month( smonth varchar, kpi_code varchar, kpi_name varchar, prov_1 numeric, prov_2 numeric, prov_3 numeric, prov_4 numeric, prov_5 numeric, prov_6 numeric, prov_7 numeric, prov_8 numeric, prov_9 numeric, prov_10 numeric)