chengben/Scripts/建表语句.sql

--经分_用户数
--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)