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()Begindeclare 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()Begindeclare 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()Begindeclare 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()Begindeclare 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 MarkMappingCREATE 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()Begindeclare 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 MarkMappingCREATE 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()Begindeclare 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()Begindeclare 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()Begindeclare 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 tablerawwhere 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 tablerawgroup by FNohaving count(TNo) <> 100;#5检查每个本号下最多有一张中奖票金额超过50元select FNo, count(FNo) from tablerawwhere bingovalue > 50group by FNohaving count(FNo)>1;#6检查每本彩票中最多连续7张无奖票#创建bingonumber1create table bingonumber1 as (select Rownumber, bingovalue, FNo from tablerawwhere bingovalue > 0order 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;#创建bingonumber2create table bingonumber2 as ( select Rownumber, bingovalue, FNo from tablerawwhere bingovalue > 0order 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 b2where b1.numberkey = b2.numberkeyand b1.FNo = b2.FNoand (b1.rownumber - b2.rownumber) > 7;