【mysql项目】模拟银行数据合并,一个身份证号可开通多个银行的信用卡的数据
需求
拉取过来的数据(比如浦发,兴业,农行等),格式如:
兴业:
手机号,姓名,开通信用卡功能(10万条)
浦发:
手机号,姓名,开通信用卡功能(10万条)
......
将上面的数据合并,因为每个身份证号有可能在多家银行开有信用卡,需要将20万条数据,假如这时只有18万不重复身份证号的数据。
数据效果展示
原始数据(黄色底为重复身份证号的数据):
工商银行(共20条)
农业银行(共20条):
去重后的数据:
此时因为有五个身份证号在两个银行开通了信用卡业务,故该五个身份证在两个表中都存在,去重后剩下35条数据,并记录开通的银行业务名称。
基础表:
备注:对于身份证、手机号和姓名等敏感数据均从网络随机生成,如有雷同纯属巧合。
数据存放的环境
系统版本:CentOS 7.5
数据库:MySQL 5.7.23
-
数据库设计
-
设计概述
-
ODS表为原始数据,数据从客户业务系统拉取,包含中文数据;
DW表去掉中文后的数据,数据从ODS表获取;
DM为展示的数据,即系统最后需求的数据;
T为基础表。
银行表:t_bank
原始数据表:
ods_bankData_gs,ods_bankData_ny,ods_bankData_zg,ods_bankData_js……
DW数据表:
dw_bankData_gs,dw_bankData_ny,dw_bankData_zg,dw_bankData_js……
DM展示表:dm_bankFor
-
- 具体设计
#创建数据库
create database dbBank;
/*
需求:
拉取过来的数据(比如浦发,兴业,农行等),格式如:
兴业:
手机号,姓名,开通信用卡功能(10万条)
浦发:
手机号,姓名,开通信用卡功能(10万条)
......
将上面的数据合并,因为每个身份证号有可能在多家银行开有信用卡,需要将20万条数据,假如这时只有18万不重复身份证号的数据
*/
#################################################
################## BASE ##################
#################################################
/*银行表:t_bank
序号,代码,银行名,银行简写
bankId
bankCode
bankName
bankAD
*/
create table t_bank(
bankId int primary key auto_increment comment '主键自增',
bankCode varchar(50) comment '银行代码',
bankName varchar(200) not null unique comment '银行名',
bankAD varchar(20) not null unique comment '银行简写'
)comment='银行表';
insert into t_bank values(1,'','工商银行','gs');
insert into t_bank values(2,'','农业银行','ny');
insert into t_bank values(3,'','中国银行','zg');
insert into t_bank values(4,'','建设银行','js');
insert into t_bank values(5,'','交通银行','jt');
insert into t_bank values(6,'','进出口银行','jck');
insert into t_bank values(7,'','国家开发银行','kf');
insert into t_bank values(8,'','农业发展银行','nf');
insert into t_bank values(9,'','招商银行','zs');
insert into t_bank values(10,'','浦发银行','pf');
insert into t_bank values(11,'','中信银行','zx');
insert into t_bank values(12,'','光大银行','gd');
insert into t_bank values(13,'','华夏银行','hx');
insert into t_bank values(14,'','民生银行','ms');
insert into t_bank values(15,'','广发银行','gf');
insert into t_bank values(16,'','兴业银行','xy');
insert into t_bank values(17,'','平安银行','pa');
insert into t_bank values(18,'','浙商银行','zhes');
insert into t_bank values(19,'','恒丰银行','hf');
insert into t_bank values(20,'','渤海银行','bh');
insert into t_bank values(21,'','广西农村信用社','gxnxs');
insert into t_bank values(22,'','邮政储蓄银行','yz');
commit;
##################################################
################## ODS ####################
##################################################
/*原始数据表:根据银行类型分表:ods_bankData_gs,ods_bankData_ny,ods_bankData_zg,ods_bankData_js,
序号,姓名,手机号,身份证号,银行名,插入时间,备注
id
name
phone
idNumber
bankName
insTime
notes
*/
--工商银行_ods
create table ods_bankData_gs(
id bigint primary key auto_increment comment '序号',
name varchar(50) comment '姓名',
phone bigint comment '手机号',
idNumber varchar(20) comment '身份证号',
bankName varchar(100) comment '银行名',
insTime date comment '插入时间',
notes varchar(200) comment '备注'
)comment='工商银行原始表';
insert into ods_bankData_gs values(1,'王正初',18502461576,'450204198004293000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(2,'李娟丽',18845873946,'653101198412151000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(3,'赵语山',19957322330,'320621197702127000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(4,'张善和',16322872701,'150722198112018000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(5,'张南烟',12850907936,'52262819870913206X','工商银行',sysdate(),'');
insert into ods_bankData_gs values(6,'王梦桃',15816875148,'440403199007149000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(7,'王清涵',15875793602,'430923197805092000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(8,'李春娇',12610912365,'130500199411174000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(9,'王冰凡',14968798213,'141026198706191000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(10,'王铃语',19781652609,'533323198803306000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(11,'李博裕',11897455113,'340826198707281000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(12,'李昊然',16237208490,'530181198712230000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(13,'李沛凝',18044968073,'150100198704294000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(14,'张诗蕾',16534073687,'610327198502213000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(15,'张怀玉',18873064922,'210181198503183000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(16,'张晨希',14113640518,'150105199306017000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(17,'赵安露',13071845241,'441825198011015000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(18,'赵凝芙',18809594871,'433126199306095000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(19,'罗初晴',19594219248,'500108199111039000','工商银行',sysdate(),'');
insert into ods_bankData_gs values(20,'罗飞昂',17786947697,'411724197606164000','工商银行',sysdate(),'');
commit;
--农业银行_ods
create table ods_bankData_ny(
id bigint primary key auto_increment comment '序号',
name varchar(50) comment '姓名',
phone bigint comment '手机号',
idNumber varchar(20) comment '身份证号',
bankName varchar(100) comment '银行名',
insTime date comment '插入时间',
notes varchar(200) comment '备注'
)comment='农业银行原始表';
insert into ods_bankData_ny values(1,'张善和',16322872701,'150722198112018000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(2,'李昊然',16237208490,'530181198712230000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(3,'赵安露',13071845241,'441825198011015000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(4,'罗飞昂',17786947697,'411724197606164000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(5,'赵凝芙',18809594871,'433126199306095000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(6,'陈弘扬',18492438399,'231283197705204000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(7,'陈晶晶',12552681742,'15010019870622882X','农业银行',sysdate(),'');
insert into ods_bankData_ny values(8,'陈奇思',18725476234,'441601199201178000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(9,'陈凝远',13876150809,'621224198305040000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(10,'李学',16162095706,'150102198008201000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(11,'张戈',17573568191,'140524199110122000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(12,'卜洪',12939298114,'370826198206133000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(13,'罗明',18581723138,'411224197509134000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(14,'欧阳伟大',18972010816,'610822198907180000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(15,'钱怀柔',18910192175,'513426199302189000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(16,'钱采波',13645247225,'451221199203159000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(17,'钱秋芳',18550469783,'321324198203030000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(18,'朱天青',13827526397,'450901198007240000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(19,'朱思凡',18075584434,'450981199210025000','农业银行',sysdate(),'');
insert into ods_bankData_ny values(20,'朱天薇',17391130746,'451122198505270000','农业银行',sysdate(),'');
commit;
################################################
################ DW #####################
################################################
/*DW表:根据银行类型分表:dw_bankData_gs,dw_bankData_ny,dw_bankData_zg,dw_bankData_js,
序号,姓名,手机号,身份证号,银行名,银行简写,插入时间
id
idNumber
phone
bankId
bankAD
insTime
*/
--工商银行_dw
create table dw_bankData_gs(
id bigint primary key auto_increment comment '序号',
idNumber varchar(20) comment '身份证号',
phone bigint comment '手机号',
bankId int comment '银行序号',
bankAD varchar(100) comment '银行简写',
insTime date comment '插入时间'
)comment='工商银行DW';
--农业银行_dw
create table dw_bankData_ny(
id bigint primary key auto_increment comment '序号',
idNumber varchar(20) comment '身份证号',
phone bigint comment '手机号',
bankId int comment '银行序号',
bankAD varchar(100) comment '银行简写',
insTime date comment '插入时间'
)comment='农业银行DW';
##################################
######## DM ###############
##################################
/*
**************************
实际需求的表--数据表:dm_bankFor
序号,身份证号,手机号,银行简写,插入时间
id
idNumber
phone
bankAD
insTime
**************************
*/
create table dm_bankFor(
id bigint primary key auto_increment comment '序号',
idNumber varchar(20) not null unique comment '身份证号',
phone bigint comment '手机号',
bankAD varchar(100) comment '银行简写汇总',
insTime date comment '插入时间'
)comment='信用卡开通数据表';
##################################################
################ 存储过程 #################
##################################################
/*
**************************
ods到dw,工商银行
**************************
*/
DELIMITER //
CREATE PROCEDURE pro_dw_gs()
BEGIN
#声明一个标志done,用来判断游标是否遍历完成
DECLARE v_done INT DEFAULT FALSE;
#特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL
DECLARE v_idNumber VARCHAR ( 255 ) DEFAULT NULL;
DECLARE v_phone BIGINT DEFAULT NULL;
DECLARE v_insTime date DEFAULT NULL;
#声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR SELECT idNumber,phone,insTime FROM ods_bankData_gs;
#在游标循环到最后会将 done 设置为 TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
#执行查询
OPEN cur;
#遍历游标每一行
read_loop :LOOP
#提取游标里的数据
FETCH cur INTO v_idNumber,v_phone,v_insTime;
#声明结束的时候
IF v_done THEN
LEAVE read_loop;
END IF;
INSERT INTO dw_bankData_gs ( idNumber, phone, bankId, bankAD, insTime )
VALUES( v_idNumber, v_phone, 1, 'gs', v_insTime );
END LOOP;
#关闭游标
CLOSE cur;
END
//
DELIMITER ;
#执行存储过程
call pro_dw_gs();
#删除存储过程
#drop procedure pro_dw_gs;
/*
**************************
ods到dw,农业银行
**************************
*/
DELIMITER //
CREATE PROCEDURE pro_dw_ny()
BEGIN
#声明一个标志done,用来判断游标是否遍历完成
DECLARE v_done INT DEFAULT FALSE;
#特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL
DECLARE v_idNumber VARCHAR ( 255 ) DEFAULT NULL;
DECLARE v_phone BIGINT DEFAULT NULL;
DECLARE v_insTime date DEFAULT NULL;
#声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR SELECT idNumber,phone,insTime FROM ods_bankData_ny;
#在游标循环到最后会将 done 设置为 TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
#执行查询
OPEN cur;
#遍历游标每一行
read_loop :LOOP
#提取游标里的数据
FETCH cur INTO v_idNumber,v_phone,v_insTime;
#声明结束的时候
IF v_done THEN
LEAVE read_loop;
END IF;
INSERT INTO dw_bankData_ny ( idNumber, phone, bankId, bankAD, insTime )
VALUES( v_idNumber, v_phone, 2, 'ny', v_insTime );
END LOOP;
#关闭游标
CLOSE cur;
END
//
DELIMITER ;
#执行存储过程
call pro_dw_ny();
#删除存储过程
#drop procedure pro_dw_ny;
/*
**************************
DM 汇总数据_工商银行
**************************
*/
DELIMITER //
drop procedure if exists pro_dm_all_gs;
CREATE PROCEDURE pro_dm_all_gs()
BEGIN
#声明一个标志done,用来判断游标是否遍历完成
DECLARE v_done INT DEFAULT FALSE;
#特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL
DECLARE v_idNumber VARCHAR ( 255 ) DEFAULT NULL;
DECLARE v_phone BIGINT DEFAULT NULL;
DECLARE v_bankAD varchar(200) DEFAULT null;
DECLARE v_insTime date DEFAULT NULL;
#定义两个判断符
DECLARE v_exists varchar(10) DEFAULT NULL;
DECLARE v_exists_gs varchar(10) DEFAULT NULL;
#声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR select idNumber,phone,bankAD,insTime from dw_bankData_gs;
#在游标循环到最后会将 done 设置为 TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
#执行查询
OPEN cur;
#遍历游标每一行
read_loop :LOOP
#提取游标里的数据
FETCH cur INTO v_idNumber,v_phone,v_bankAD,v_insTime;
#声明结束的时候
IF v_done THEN
LEAVE read_loop;
END IF;
#判断是否有这个身份证号码
select count(*) into v_exists from dm_bankFor where idNumber = v_idNumber;
IF v_exists = 0 then
INSERT INTO dm_bankFor ( idNumber, phone, bankAD, insTime )
VALUES( v_idNumber, v_phone, v_bankAD, v_insTime );
else
UPDATE dm_bankFor set bankAD='gs'
where idNumber = v_idNumber and length(bankAD) = 0;
#LEAVE read_loop;
UPDATE dm_bankFor set bankAD=concat(bankAD,',',v_bankAD)
where idNumber = v_idNumber and bankAD not like ('%gs%') and length(bankAD) <> 0;
end if;
END LOOP;
#关闭游标
CLOSE cur;
END
//
DELIMITER ;
执行存储过程
call pro_dm_all_gs();
#删除存储过程
#drop procedure pro_dm_all_gs;
/*
**************************
DM 汇总数据_农业银行
**************************
*/
DELIMITER //
drop procedure if exists pro_dm_all_ny;
CREATE PROCEDURE pro_dm_all_ny()
BEGIN
#声明一个标志done,用来判断游标是否遍历完成
DECLARE v_done INT DEFAULT FALSE;
#特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL
DECLARE v_idNumber VARCHAR ( 255 ) DEFAULT NULL;
DECLARE v_phone BIGINT DEFAULT NULL;
DECLARE v_bankAD varchar(200) DEFAULT null;
DECLARE v_insTime date DEFAULT NULL;
#定义两个判断符
DECLARE v_exists varchar(10) DEFAULT NULL;
DECLARE v_exists_ny varchar(10) DEFAULT NULL;
#声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR select idNumber,phone,bankAD,insTime from dw_bankData_ny;
#在游标循环到最后会将 done 设置为 TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
#执行查询
OPEN cur;
#遍历游标每一行
read_loop :LOOP
#提取游标里的数据
FETCH cur INTO v_idNumber,v_phone,v_bankAD,v_insTime;
#声明结束的时候
IF v_done THEN
LEAVE read_loop;
END IF;
#判断是否有这个身份证号码
select count(*) into v_exists from dm_bankFor where idNumber = v_idNumber;
IF v_exists = 0 then
INSERT INTO dm_bankFor ( idNumber, phone, bankAD, insTime )
VALUES( v_idNumber, v_phone, v_bankAD, v_insTime );
else
UPDATE dm_bankFor set bankAD='ny'
where idNumber = v_idNumber and length(bankAD) = 0;
#LEAVE read_loop;
UPDATE dm_bankFor set bankAD=concat(bankAD,',',v_bankAD)
where idNumber = v_idNumber and bankAD not like ('%ny%') and length(bankAD) <> 0;
end if;
END LOOP;
#关闭游标
CLOSE cur;
END
//
DELIMITER ;
执行存储过程
call pro_dm_all_ny();
#删除存储过程
#drop procedure pro_dm_all_ny;