MySQL彩票分析

Mysql 彩票分析实例

create database CP1; #创建数据库 use cp1; # drop table TableRaw; # 创建表 create table if not exists TableRaw( rawdata varchar(200) not null unique); # 导入数据 load data infile 'C:\\RawData.TXT' into table TableRaw fields terminated by '\t'; select * from TableRaw; desc tableraw; select count(*) from TableRaw; ---------------原始数据为1个字段，对其进行拆分，先建立字段------------------ alter table TableRaw add RowNumber int primary key auto_increment; -- 自增字段，用来记录彩票张数 alter table TableRaw add FNo char(7) not null default ''; -- 本号、票号唯一代表一张彩票 alter table TableRaw add TNo char(3) not null default ''; -- 票号 alter table TableRaw add Mark char(10) not null default ''; -- 图符 alter table TableRaw add Reward char(10) not null default ''; -- 奖金 alter table TableRaw add FloatNo char(10) not null default ''; -- 漂移字符 alter table TableRaw add CNo char(8) not null default ''; -- 手工验奖 alter table TableRaw add LCode char(29) not null default ''; -- 物流码 alter table TableRaw add SCode char(41) not null default ''; -- 保安码 #重新排序 alter table tableraw modify RowNumber int first; desc tableRaw; -- 图符1-10，将图符字段拆分为10个单字母字段 #创建存储过程 delimiter $$ create procedure markfix() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, 'add mark',num,' char(1) not null, '); -- 1.@sqltext = add mark1 char(1) not null, -- 2.@sqltext = add mark1 char(1) not null, add mark2 char(1) not null, -- 3.@sqltext = add mark1 char(1) not null, add mark2 char(1) not null, add mark3 char(1) not null, -- 9.@sqltext = add mark1 char(1) not null, add mark2 char(1) not null,....add mark9 char(1) not null, set n=n+1; end while; set @sqltext = concat('alter table tableraw ',@sqltext, 'add mark10 char(1) not null;'); -- final. @sqltext = alter table tableraw add mark1 char(1) not null, add mark2 char(1) not null,....add mark9 char(1) not null, add mark10 char(1) not null; end $$ delimiter ; -- alter table tableraw add mark1 char(1) not null, -- add mark2 char(1) not null, -- add mark3 char(1) not null, -- add mark4 char(1) not null, -- add mark5 char(1) not null, -- add mark5 char(1) not null, -- add mark6 char(1) not null; #drop procedure markfix; #检查生成的sql语句 call markfix(); select @sqltext; #执行语句 prepare stmt from @sqltext; execute stmt; #检查表结构 desc TableRaw; -- 奖码1-10，将资金字段拆分为10个单字母字段 #创建存储过程 delimiter $$ create procedure rewardfix() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, 'add reward',num,' char(1) not null, '); set n=n+1; end while; set @sqltext = concat('alter table tableraw ',@sqltext, 'add reward10 char(1) not null;'); end $$ delimiter ; #drop procedure markfix; #检查生成的sql语句 call rewardfix(); select @sqltext; #执行语句 prepare stmt1 from @sqltext; execute stmt1; #检查表结构 desc TableRaw; --------原始数据为1个字段，对其进行拆分，对字段进行赋值------------- UPDATE TableRaw SET FNo = substring(rawdata,1,7); UPDATE TableRaw SET TNo = substring(rawdata,9,3); UPDATE TableRaw SET Mark = substring(rawdata,12,10); -- =MID($A3,12,10) UPDATE TableRaw SET Reward = substring(rawdata,22,10); -- =MID($A3,22,10) UPDATE TableRaw SET FloatNo = substring(rawdata,32,10); -- =MID($A3,32,10) UPDATE TableRaw SET CNo = substring(rawdata,42,8); -- =MID($A3,42,8) UPDATE TableRaw SET LCode = substring(rawdata,50,29); -- =MID($A3,50,29) UPDATE TableRaw SET SCode = substring(rawdata,79,41); -- =MID($A3,79,41) select * from tableraw; -- 图符1-10 #创建存储过程 delimiter $$ create procedure markset() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, ' mark',num,' = substring(mark, ',num, ', 1), '); set n=n+1; end while; set @sqltext = concat('update tableraw set',@sqltext, ' mark10 = substring(mark, 10, 1);'); end $$ delimiter ; -- update tableraw set mark1 = substring(mark,1,1), -- mark2 = substring(mark,2,1), -- ... -- mark10=substring(mark,10,1); #drop procedure markset; -- concat(@sqltext, ' mark',num,' = substring(mark, ',num, ', 1), '); -- mark1 = substring(mark, 1, 1), #检查生成的sql语句 call markset(); select @sqltext; prepare stmt2 from @sqltext; execute stmt2; #检查表数据及表结构 select * from tableraw; desc tableraw; -- 奖码1-10 #创建存储过程 delimiter $$ create procedure rewardset() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, ' reward',num,' = substring(reward, ',num, ', 1), '); set n=n+1; end while; set @sqltext = concat('update tableraw set',@sqltext, ' reward10 = substring(reward, 10, 1);'); end $$ delimiter ; #drop procedure rewardset; #检查生成的sql语句 call rewardset(); select @sqltext; prepare stmt3 from @sqltext; execute stmt3; #检查表数据及表结构 select * from tableraw; desc tableraw; ----------------------奖金倍数------------------------------- -- Create MarkMapping CREATE TABLE MarkMapping ( Mark char(1) not null, MarkNo int not null ); insert into MarkMapping values ('m', 1); -- m可获得奖金 insert into MarkMapping values ('n', 2); -- n可获得2倍奖金 insert into MarkMapping values ('o', 2); -- o可获得2倍奖金 insert into MarkMapping values ('p', 2); -- p可获得2倍奖金 select * from MarkMapping; -- 图倍数1-10 #创建存储过程 delimiter $$ create procedure marktimes() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, 'add marktimes',num,' int not null default 0, '); set n=n+1; end while; set @sqltext = concat('alter table tableraw ',@sqltext, 'add marktimes10 int not null default 0;'); end $$ delimiter ; -- alter table TableRaw add [图倍数' + @No + '] [int] not null default 0 #drop procedure marktimes; #检查生成的sql语句 call marktimes(); select @sqltext; #执行语句 prepare stmt4 from @sqltext; execute stmt4; #检查表结构 desc TableRaw; select * from TableRaw; select * from markmapping; -- 为图倍数赋值 update tableraw inner join markmapping on tableraw.mark1 = markmapping.Mark set tableraw.marktimes1 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark2 = markmapping.Mark set tableraw.marktimes2 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark3 = markmapping.Mark set tableraw.marktimes3 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark4 = markmapping.Mark set tableraw.marktimes4 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark5 = markmapping.Mark set tableraw.marktimes5 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark6 = markmapping.Mark set tableraw.marktimes6 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark7 = markmapping.Mark set tableraw.marktimes7 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark8 = markmapping.Mark set tableraw.marktimes8 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark9 = markmapping.Mark set tableraw.marktimes9 = markmapping.markNo; update tableraw inner join markmapping on tableraw.mark10 = markmapping.Mark set tableraw.marktimes10 = markmapping.markNo; #检查表数据及表结构 select * from tableraw; ------------------------奖金金额--------------------------- -- Create MarkMapping CREATE TABLE RewardMapping ( Mark char(1) not null, MarkNo int not null); insert into RewardMapping values ('A', 5); insert into RewardMapping values ('B', 10); insert into RewardMapping values ('C', 20); insert into RewardMapping values ('D', 25); insert into RewardMapping values ('E', 50); insert into RewardMapping values ('F', 100); insert into RewardMapping values ('G', 200); insert into RewardMapping values ('H', 500); insert into RewardMapping values ('I', 1000); insert into RewardMapping values ('J', 100000); -- 查看数据及表结构 select * from RewardMapping; desc rewardmapping; -- 奖金1-10 #创建存储过程 delimiter $$ create procedure rewardvalue() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, 'add rewardvalue',num,' int not null default 0, '); set n=n+1; end while; set @sqltext = concat('alter table tableraw ',@sqltext, 'add rewardvalue10 int not null default 0;'); end $$ delimiter ; #drop procedure rewardvalue; #检查生成的sql语句 call rewardvalue(); select @sqltext; #执行语句 prepare stmt5 from @sqltext; execute stmt5; #检查表结构 desc TableRaw; select * from RewardMapping; select * from tableraw; -- 为奖金列赋值 update tableraw inner join RewardMapping on tableraw.reward1 = RewardMapping.Mark set tableraw.rewardvalue1 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward2 = RewardMapping.Mark set tableraw.rewardvalue2 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward3 = RewardMapping.Mark set tableraw.rewardvalue3 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward4 = RewardMapping.Mark set tableraw.rewardvalue4 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward5 = RewardMapping.Mark set tableraw.rewardvalue5 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward6 = RewardMapping.Mark set tableraw.rewardvalue6 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward7 = RewardMapping.Mark set tableraw.rewardvalue7 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward8 = RewardMapping.Mark set tableraw.rewardvalue8 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward9 = RewardMapping.Mark set tableraw.rewardvalue9 = RewardMapping.markNo; update tableraw inner join RewardMapping on tableraw.reward10 = RewardMapping.Mark set tableraw.rewardvalue10 = RewardMapping.markNo; #检查表数据及表结构 select * from tableraw where rewardvalues<>0; -----------------------中奖金额-------------------------- -- 中奖1-10 #创建存储过程 delimiter $$ create procedure bingo() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, 'add bingo',num,' int not null default 0, '); set n=n+1; end while; set @sqltext = concat('alter table tableraw ',@sqltext, 'add bingo10 int not null default 0;'); end $$ delimiter ; #drop procedure bingo; #检查生成的sql语句 call bingo(); select @sqltext; #执行语句 prepare stmt6 from @sqltext; execute stmt6; #检查数据及表结构 select * from tableraw; desc TableRaw; -- 求中奖金额 #创建存储过程 /* update TableRaw set 中奖金额'+@No+' = TableRaw.图倍数'+@No+' * TableRaw.奖面额'+@No */ update tableraw set bingo1 = marktimes1 * rewardvalue1, bingo2 = marktimes2 * rewardvalue2; select * from tableraw; delimiter $$ create procedure bingoinput() Begin declare n int; declare num varchar(2); set n=1; set @sqltext = ''; while n<10 do set num=cast(n as char); set @sqltext = concat(@sqltext, 'bingo',num,'=marktimes',num,'*rewardvalue',num,', '); set n=n+1; end while; set @sqltext = concat('update tableraw set ',@sqltext, 'bingo10=marktimes10*rewardvalue10;'); end $$ delimiter ; #drop procedure bingoinput; #检查生成的sql语句 call bingoinput(); select @sqltext; #执行语句 prepare stmt7 from @sqltext; execute stmt7; #检查数据及表结构 select * from tableraw; desc TableRaw; -- 总中奖金额 alter table TableRaw add bingovalue int not null default 0; update TableRaw set bingovalue = bingo1 + bingo2 + bingo3 + bingo4 + bingo5 + bingo6 + bingo7 + bingo8 + bingo9 + bingo10; #检查数据及表结构 select * from tableraw where bingovalue<>0; desc TableRaw; -- 导出最终数据 select FNo, TNo, Mark,Reward,bingovalue from tableraw into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/final.csv' fields terminated by '\,' #optionally enclosed by '\"' lines terminated by '\r\n'; create table Final( FNo varchar(10) not null, TNo varchar(10) not null, Mark varchar(20) not null, reward varchar(20) not null, bingovalue int not null ); load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/final.csv' into table Final fields terminated by '\,'; #alter table TableRaw add RowNumber int primary key auto_increment; -- 自增字段，用来记录彩票张数 ------------------- 奖票分析 ------------------------------ #1求总中奖张数及金额 select bingovalue from tableraw; select count(bingovalue) as 中奖总张数, sum(bingovalue) as 中奖总金额 from tableraw where bingovalue <> 0; #2求各不同奖幅的张数及金额 select bingovalue as 奖幅, count(bingovalue) as 张数, sum(bingovalue) as 金额 from tableraw group by bingovalue having bingovalue <> 0; #3求中奖张数与总张数占比，中奖金额与总金额的占比 set @allcount = (select count(bingovalue) from tableraw); set @allsum = (select count(bingovalue) * 5 from tableraw); select count(bingovalue)/@allcount as 中奖张数占比, sum(bingovalue)/@allsum as 中奖金额占比 from tableraw where bingovalue <> 0; #4检查每个本号下有100张彩票 select FNo, count(TNo) from tableraw group by FNo having count(TNo) <> 100; #5检查每个本号下最多有一张中奖票金额超过50元 select FNo, count(FNo) from tableraw where bingovalue > 50 group by FNo having count(FNo)>1; #6检查每本彩票中最多连续7张无奖票 #创建bingonumber1 create table bingonumber1 as ( select Rownumber, bingovalue, FNo from tableraw where bingovalue > 0 order by rownumber); select * from bingonumber1; -- drop table bingonumber1; -- 删除表 #删除第一条记录 delete from bingonumber1 limit 1; alter table bingonumber1 add numberkey int primary key auto_increment; -- 自增 #重新排序 alter table bingonumber1 modify numberkey int first; #创建bingonumber2 create table bingonumber2 as ( select Rownumber, bingovalue, FNo from tableraw where bingovalue > 0 order by rownumber); -- drop table bingonumber2; -- 删除表 alter table bingonumber2 add numberkey int primary key auto_increment; -- 自增 #重新排序 alter table bingonumber2 modify numberkey int first; #检查数据内容及记录行数 select * from bingonumber1; select * from bingonumber2; select count(*) from bingonumber1; select count(*) from bingonumber2; #检查测试结果 select b1.*, b2.*, (b1.rownumber - b2.rownumber) as gap from bingonumber1 as b1, bingonumber2 as b2 where b1.numberkey = b2.numberkey and b1.FNo = b2.FNo and (b1.rownumber - b2.rownumber) > 7;