一、建表
1、建表语句
CREATE TABLE if not exists `pl_account_repay_daily_fact` (
`main_order_id` bigint(32) NOT NULL COMMENT ‘总order_id‘,
`split_order_id` bigint(32) NOT NULL COMMENT ‘拆分order_id‘,
`user_id` bigint(11) NOT NULL COMMENT ‘用户id‘,
`term_no` smallint(6) NOT NULL COMMENT ‘期号‘,
`term_start_date` date NOT NULL COMMENT ‘本期起始日‘,
`term_end_date` date NOT NULL COMMENT ‘本期到期日‘,
`loan_vintage_date` date NOT NULL COMMENT ‘首期到期日‘,
`term_principal` decimal(16,2) NOT NULL DEFAULT ‘0.00‘ COMMENT ‘本期到期应还本金‘,
`term_interest` decimal(16,2) NOT NULL DEFAULT ‘0.00‘ COMMENT ‘本期到期应还利息‘,
`act_date` date NOT NULL COMMENT ‘统计截止日‘,
`past_days` int(11) NOT NULL COMMENT ‘统计截止日距当期到期日天数‘,
`actual_repay_principal` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期实还本期本金‘,
`actual_repay_interest` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期实还本期利息‘,
`actual_repay_overdue_fine` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期实还本期罚金‘,
`actual_repay_tot` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期实还总额度‘,
`accum_actual_repay_principal` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期累计实还本期本金‘,
`accum_actual_repay_interest` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期累计实还本期利息‘,
`accum_actual_repay_overdue_fine` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期累计实还本期罚金‘,
`accum_actual_repay_tot` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期累计实还总额度‘,
`term_balance` decimal(16,2) DEFAULT NULL COMMENT ‘本期合同余额‘,
`loan_balance` decimal(16,2) DEFAULT NULL COMMENT ‘贷款合同余额‘,
`due_date` date NOT NULL COMMENT ‘当日‘,
`due_overdue_days` smallint(6) DEFAULT NULL COMMENT ‘逾期天数‘,
`due_is_overdue` smallint(6) DEFAULT NULL COMMENT ‘是否逾期‘,
`due_overdue_status` smallint(6) DEFAULT NULL COMMENT ‘逾期状态（逾期期数）‘,
`due_overdue_fine_daily` decimal(16,2) DEFAULT NULL COMMENT ‘本期当日产生逾期罚金‘,
`accum_due_overdue_fine` decimal(16,2) DEFAULT NULL COMMENT ‘本期当日累计产生逾期罚金‘,
`due_repay_principal` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期当日应还本金‘,
`due_repay_interest` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期当日应还利息‘,
`due_repay_overdue_fine` decimal(16,2) DEFAULT ‘0.00‘ COMMENT ‘本期当日应还逾期罚金‘,
KEY `main_order_id` (`main_order_id`),
KEY `split_order_id` (`split_order_id`),
KEY `user_id` (`user_id`),
KEY `term_start_date` (`term_start_date`),
KEY `term_end_date` (`term_end_date`),
KEY `term_no` (`term_no`),
KEY `due_date` (`due_date`),
KEY `act_date` (`act_date`),
KEY `loan_balance` (`loan_balance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、删除表
drop table if exists pl_account_repay_daily_fact
3、清空表数据
truncate table pl_account_repay_daily_fact
二、修改表结构或数据
1、添加(多)列
alter table pl_performance_loan_apply_fact
add column `company` varchar(50) DEFAULT NULL;
2、修改列名（或格式）
alter table dim_collect_period_company
change `bind_user_name` `bind_user_name_new` varchar(50) NOT NULL COMMENT ‘催收员‘;
3、更新列的数据
update tmp_account_loan_overdue_daily_pre set term_overdue_fine=‘20000.00‘ where split_order_id=‘236‘ and due_date=‘2020-01-16‘;
#update tmp_account_loan_overdue_daily_pre set term_overdue_fine=‘30000.00‘ where split_order_id=‘236‘ and due_date=‘2020-01-17‘;