T-SQL 模拟彩票预测
比较简单,只是模拟彩票出数字的过程,不计算单一数字的出现概率。
传统上来说,每次彩票出号的概率都是独立事件,单纯的在可选数字内随机实现即可。
本文探索的是实现简单的预测分析,包含历史开奖结果的连续事件。
举例说明:(模拟三个数字,数字区域1-10)
第一次开奖 1,2,3
第二次预测 [4-10] + [1-10 ]中按次序随机取三个数字,每随机取一个数字后都要在数字区域移除这个数字。
第二次开奖 4,5,6
第三次预测 [4-10] +[1-3]+[7-10]+[1-10] 中按次序随机取三个数字,每随机取一个数字后都要在数字区域移除这个数字。
..
..
第N次预测 在前N次开奖数字之外的其他数字+本次的数字区域 中按次序随机取三个数字,每随机取一个数字后都要在数字区域移除这个数字。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
思路就是把历史每次开奖数字剩下的数字集合放到下一次抽奖池中进行随机
步骤如下
1.创建一张表 保存历史开奖结果。
2.将每次开奖结果以外的其他数字 放到抽奖池中 。
3.将预测本次开奖结果的全部数字 放到抽奖池中。
3.在抽奖池中逐个随机抽取数字。每轮抽取中的数字都要在抽奖池中排除。
4.输出显示结果。
代码模拟的是大乐透,分前区数字1-35,后区数字1-12,前区和后区相互独立,分开处理
1 /******************************************************************
2 drop table Number
3 create table Number
4 (
5 ID int identity(1,1),
6 Ver varchar(5),
7 Num1 int,
8 Num2 int,
9 Num3 int,
10 Num4 int,
11 Num5 int,
12 Num6 int,
13 Num7 int
14 )
15
16 select * from Number
17
18 insert into Number
19 select '23004','04','13','15','20','22','04','08' union all
20 select '23005','01','03','10','20','29','06','11' union all
21 select '23006','02','04','12','27','35','06','07' union all
22 select '23007','02','03','24','30','32','01','06'
23
24 ******************************************************************/
25 IF(OBJECT_ID('tempdb..#Front')) IS NOT NULL
26 DROP TABLE #Front;
27 CREATE TABLE #Front
28 (
29 Id int identity(1,1),
30 Num int
31 )
32
33 IF(OBJECT_ID('tempdb..#Back')) IS NOT NULL
34 DROP TABLE #Back;
35
36 CREATE TABLE #Back
37 (
38 Id int identity(1,1),
39 Num int
40 )
41
42 DECLARE @FrontNum INT
43 DECLARE @BackNum INT
44 DECLARE @Rolling INT
45
46 SET @Rolling = 1
47
48 WHILE (@Rolling<=(SELECT COUNT(*)+1 FROM Number))
49 BEGIN
50 SET @FrontNum = 1
51
52 WHILE(@FrontNum<=35)
53 BEGIN
54 INSERT INTO #Front
55 SELECT @FrontNum
56 SET @FrontNum = @FrontNum+1
57 END
58 SET @BackNum = 1
59 WHILE(@BackNum<=12)
60 BEGIN
61 INSERT INTO #Back
62 SELECT @BackNum
63 SET @BackNum = @BackNum+1
64 END
65 SET @Rolling = @Rolling+1
66 END
67
68
69
70 IF(OBJECT_ID('tempdb..#BASE')) IS NOT NULL
71 DROP TABLE #BASE;
72
73 select
74 Ver
75 ,KeyNum
76 ,Value
77 INTO #BASE
78 FROM Number
79 UNPIVOT
80 (
81 Value for KeyNum in ([Num1], [Num2], [Num3], [Num4], [Num5], [Num6], [Num7])
82 ) a
83
84
85 DECLARE Front_Cursor Cursor
86 FOR SELECT [Value] as Num FROM #BASE WHERE RIGHT(KeyNum,1)<=5;
87
88 OPEN Front_Cursor;
89 DECLARE @Var_Front int;
90 FETCH NEXT FROM Front_Cursor into @Var_Front
91 WHILE (@@FETCH_STATUS=0)
92 BEGIN
93 DELETE FROM #Front WHERE ID IN (SELECT top 1 id FROM #Front WHERE Num = @Var_Front order by id)
94 FETCH NEXT FROM Front_Cursor into @Var_Front
95 END
96 GO
97 CLOSE Front_Cursor;
98 DEALLOCATE Front_Cursor;
99
100
101 DECLARE Back_Cursor Cursor
102 FOR SELECT [Value] as Num FROM #BASE WHERE RIGHT(KeyNum,1)>5;
103
104 OPEN Back_Cursor;
105 DECLARE @Var_Back int;
106 FETCH NEXT FROM Back_Cursor into @Var_Back
107 WHILE (@@FETCH_STATUS=0)
108 BEGIN
109 DELETE FROM #Back WHERE ID IN (SELECT top 1 id FROM #Back WHERE Num = @Var_Back order by id)
110 FETCH NEXT FROM Back_Cursor into @Var_Back
111 END
112 GO
113 CLOSE Back_Cursor;
114 DEALLOCATE Back_Cursor;
115
116
117
118 /***************************
119 Finial
120 **************************/
121
122 IF(OBJECT_ID('tempdb..#Finial')) IS NOT NULL
123 DROP TABLE #Finial;
124 CREATE TABLE #Finial
125 (
126 Id int identity(1,1),
127 Num int
128 )
129
130 DECLARE @Seq1 int
131 DECLARE @Num1 int
132 SET @Seq1 = 1
133 WHILE (@Seq1<=5)
134 BEGIN
135 SET @Num1 = (SELECT TOP 1 Num from #Front order by NEWID())
136 PRINT @Num1
137 INSERT INTO #Finial SELECT @Num1
138 DELETE FROM #Front WHERE Num = @Num1
139 SET @Seq1 = @Seq1+1
140 END
141
142 DECLARE @Seq2 int
143 DECLARE @Num2 int
144 SET @Seq2 = 1
145 WHILE (@Seq2<=2)
146 BEGIN
147 SET @Num2 = (SELECT TOP 1 Num from #Back order by NEWID())
148 INSERT INTO #Finial SELECT @Num2
149 DELETE FROM #Back WHERE Num = @Num2
150 SET @Seq2 = @Seq2+1
151 END
152
153
154 IF(OBJECT_ID('tempdb..#RESULT')) IS NOT NULL
155 DROP TABLE #RESULT;
156
157 WITH CTE AS
158 (
159 SELECT ROW_NUMBER()over(order by Num) as Seq,Num from #Finial where id<=5
160 UNION ALL
161 SELECT ROW_NUMBER()over(order by Num)+5 as Seq,Num from #Finial where id>5
162 )
163 ,Final as
164 (
165
166 SELECT
167 'Num'+CONVERT(varchar,Seq) as NumSeq
168 ,Num as Value
169 FROM CTE
170 )
171
172
173 SELECT * INTO #RESULT
174 FROM Final
175 PIVOT
176 (
177 SUM(Value) FOR NumSeq in (Num1,Num2,Num3,Num4,Num5,Num6,Num7)
178 ) a
179
180 SELECT * FROM #RESULT