As I'm sure will be apparent to most here, I'm not a statistician or a programmer, but one of my hobbies is politics. I've created a (very simple) USA electoral college simulation, seen below.
The idea is that if you think you have a good estimate of the probabilities of the different states, you can simply run a simulation to get the odds of victory for the candidates. Technically, the way this works is, say you assume the R candidate has a 45% of victory in, say, Wisconsin. The SQL rand() function should return a value (roughly speaking) less than or equal to 45% roughly 45% of the time. So over a million rows you'd expect for rand() to produce around 450,000 rows that are 45% or less. So for each row where this is "true" I assign electoral college points, but I do it for the same randomized number across all states.
I guess my question is, does this simple SQL model make sense or am I fooling myself into thinking that if I can get good state probabilities that this will actually produce a decent overall probability? The actual state odds I have in there right now are of course completely fictitious as I've been playing with different numbers.
--run once
Create Table database.dbo.Numbers(
[Num] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED
([Num] ASC ))
Insert into database.dbo.Numbers
select top 10000000 row_number() over (order by s1.name)
from sysobjects s1 cross join sysobjects s2, sysobjects s3
--run many times
--input the probability of R win for each state
declare @AK as float
Set @AK = .99
declare @AL as float
Set @AL = .99
declare @AR as float
Set @AR = .999
declare @AZ as float
Set @AZ = .4
declare @CA as float
Set @CA = .01
declare @CO as float
Set @CO = .5
declare @CT as float
Set @CT = .01
declare @DE as float
Set @DE = .01
declare @FL as float
Set @FL = .5
declare @GA as float
Set @GA = .5
declare @HI as float
Set @HI = .01
declare @IA as float
Set @IA = .6
declare @ID as float
Set @ID = .99
declare @IL as float
Set @IL = .01
declare @IN as float
Set @IN = .8
declare @KS as float
Set @KS = .8
declare @KY as float
Set @KY = .7
declare @LA as float
Set @LA = .7
declare @MA as float
Set @MA = .01
declare @MD as float
Set @MD = .01
declare @ME as float
Set @ME = .01
declare @MI as float
Set @MI = .45
declare @MN as float
Set @MN = .5
declare @MO as float
Set @MO = .8
declare @MS as float
Set @MS = .9
declare @MT as float
Set @MT = .99
declare @NC as float
Set @NC = .55
declare @ND as float
Set @ND = .99
declare @NE as float
Set @NE = .7
declare @NH as float
Set @NH = .1
declare @NJ as float
Set @NJ = .1
declare @NM as float
Set @NM = .3
declare @NV as float
Set @NV = .5
declare @NY as float
Set @NY = .01
declare @OH as float
Set @OH = .6
declare @OK as float
Set @OK = .8
declare @OR as float
Set @OR = .99
declare @PA as float
Set @PA = .5
declare @RI as float
Set @RI = .1
declare @SC as float
Set @SC = .99
declare @SD as float
Set @SD = .99
declare @TN as float
Set @TN = .8
declare @TX as float
Set @TX = .55
declare @UT as float
Set @UT = .99
declare @VA as float
Set @VA = .1
declare @VT as float
Set @VT = .2
declare @WA as float
Set @WA = .1
declare @WI as float
Set @WI = .6
declare @WV as float
Set @WV = .99
declare @WY as float
Set @WY = .9
declare @DC as float
Set @DC = .01
declare @ME1 as float
set @ME1 = .2
declare @ME2 as float
set @ME2 = .8
declare @NE1 as float
set @NE1 = .99
declare @NE2 as float
set @NE2 = .5
declare @NE3 as float
set @NE3 = .99
Select F.WinLose,cast(F.Count as float) / cast(F.Sumtotal as float) as Chance
from
(
Select E.WinLose,E.Count,sum(E.Count) over () as Sumtotal
from
(Select D.WinLose,count(D.Winlose) as Count
from
(Select C.Num,(case when (C.[R-AK]+C.[R-AL]+C.[R-AR]+C.[R-AZ]+C.[R-CA]+C.[R-CO]+C.[R-CT]+C.[R-DC]+C.[R-DE]+C.[R-FL]+C.[R-GA]
+C.[R-HI]+c.[R-IA]+c.[R-ID]+c.[R-IL]+c.[R-IN]+c.[R-KS]+c.[R-KY]+c.[R-LA]+c.[R-MA]+c.[R-MD]+c.[R-ME]+c.[R-MI]+c.[R-MN]+c.[R-MO]
+c.[R-MS]+c.[R-MT]+c.[R-NC]+c.[R-ND]+c.[R-NE]+c.[R-NH]+c.[R-NJ]+c.[R-NM]+c.[R-NV]+c.[R-NY]+c.[R-OH]+c.[R-OK]+c.[R-OR]+c.[R-PA]+c.[R-RI]+
c.[R-SC]+c.[R-SD]+c.[R-TN]+c.[R-TX]+c.[R-UT]+c.[R-VA]+c.[R-VT]+c.[R-WA]+c.[R-WI]+c.[R-WV]+c.[R-WY]+c.[R-NE1]+c.[R-NE2]+c.[R-NE3]+c.[R-ME1]+c.[R-ME2])
>= 269 then 'Win' else 'NotWin' end) as WinLose
from
(Select B.Num,(case when B.AK = 'TRUE' then 3 else 0 end) as 'R-AK',
(case when B.AL = 'TRUE' then 9 else 0 end) as 'R-AL',
(case when B.AR = 'TRUE' then 6 else 0 end) as 'R-AR',
(case when B.AZ = 'TRUE' then 11 else 0 end) as 'R-AZ',
(case when B.CA = 'TRUE' then 55 else 0 end) as 'R-CA',
(case when B.CO = 'TRUE' then 9 else 0 end) as 'R-CO',
(case when B.CT = 'TRUE' then 7 else 0 end) as 'R-CT',
(case when B.DE = 'TRUE' then 3 else 0 end) as 'R-DE',
(case when B.FL = 'TRUE' then 29 else 0 end) as 'R-FL',
(case when B.GA = 'TRUE' then 16 else 0 end) as 'R-GA',
(case when B.HI = 'TRUE' then 4 else 0 end) as 'R-HI',
(case when B.IA = 'TRUE' then 6 else 0 end) as 'R-IA',
(case when B.ID = 'TRUE' then 4 else 0 end) as 'R-ID',
(case when B.IL = 'TRUE' then 20 else 0 end) as 'R-IL',
(case when B.[IN] = 'TRUE' then 11 else 0 end) as 'R-IN',
(case when B.KS = 'TRUE' then 6 else 0 end) as 'R-KS',
(case when B.KY = 'TRUE' then 8 else 0 end) as 'R-KY',
(case when B.LA = 'TRUE' then 8 else 0 end) as 'R-LA',
(case when B.MA = 'TRUE' then 11 else 0 end) as 'R-MA',
(case when B.MD = 'TRUE' then 10 else 0 end) as 'R-MD',
(case when B.MI = 'TRUE' then 16 else 0 end) as 'R-MI',
(case when B.MN = 'TRUE' then 10 else 0 end) as 'R-MN',
(case when B.MO = 'TRUE' then 10 else 0 end) as 'R-MO',
(case when B.MS = 'TRUE' then 6 else 0 end) as 'R-MS',
(case when B.MT = 'TRUE' then 3 else 0 end) as 'R-MT',
(case when B.NC = 'TRUE' then 15 else 0 end) as 'R-NC',
(case when B.ND = 'TRUE' then 3 else 0 end) as 'R-ND',
(case when B.NH = 'TRUE' then 4 else 0 end) as 'R-NH',
(case when B.NJ = 'TRUE' then 14 else 0 end) as 'R-NJ',
(case when B.NM = 'TRUE' then 5 else 0 end) as 'R-NM',
(case when B.NV = 'TRUE' then 6 else 0 end) as 'R-NV',
(case when B.NY = 'TRUE' then 29 else 0 end) as 'R-NY',
(case when B.OH = 'TRUE' then 18 else 0 end) as 'R-OH',
(case when B.OK = 'TRUE' then 7 else 0 end) as 'R-OK',
(case when B.[OR] = 'TRUE' then 7 else 0 end) as 'R-OR',
(case when B.PA = 'TRUE' then 20 else 0 end) as 'R-PA',
(case when B.RI = 'TRUE' then 4 else 0 end) as 'R-RI',
(case when B.SC = 'TRUE' then 9 else 0 end) as 'R-SC',
(case when B.SD = 'TRUE' then 3 else 0 end) as 'R-SD',
(case when B.TN = 'TRUE' then 11 else 0 end) as 'R-TN',
(case when B.TX = 'TRUE' then 38 else 0 end) as 'R-TX',
(case when B.UT = 'TRUE' then 6 else 0 end) as 'R-UT',
(case when B.VA = 'TRUE' then 13 else 0 end) as 'R-VA',
(case when B.VT = 'TRUE' then 3 else 0 end) as 'R-VT',
(case when B.WA = 'TRUE' then 12 else 0 end) as 'R-WA',
(case when B.WI = 'TRUE' then 10 else 0 end) as 'R-WI',
(case when B.WV = 'TRUE' then 5 else 0 end) as 'R-WV',
(case when B.WY = 'TRUE' then 3 else 0 end) as 'R-WY',
(case when B.DC = 'TRUE' then 3 else 0 end) as 'R-DC',
(case when B.NE = 'TRUE' then 2 else 0 end) as 'R-NE',
(case when B.ME = 'TRUE' then 2 else 0 end) as 'R-ME',
(case when B.ME1 = 'TRUE' then 1 else 0 end) as 'R-ME1',
(case when B.ME2 = 'TRUE' then 1 else 0 end) as 'R-ME2',
(case when B.NE1 = 'TRUE' then 1 else 0 end) as 'R-NE1',
(case when B.NE2 = 'TRUE' then 1 else 0 end) as 'R-NE2',
(case when B.NE3 = 'TRUE' then 1 else 0 end) as 'R-NE3'
from
(Select A.Num,A.RanSelection,
(Case when A.RanSelection <= @AK then 'TRUE' else 'FALSE' end) as AK,
(Case when A.RanSelection <= @AL then 'TRUE' else 'FALSE' end) as AL,
(Case when A.RanSelection <= @AR then 'TRUE' else 'FALSE' end) as AR,
(Case when A.RanSelection <= @AZ then 'TRUE' else 'FALSE' end) as AZ,
(Case when A.RanSelection <= @CA then 'TRUE' else 'FALSE' end) as CA,
(Case when A.RanSelection <= @CO then 'TRUE' else 'FALSE' end) as CO,
(Case when A.RanSelection <= @CT then 'TRUE' else 'FALSE' end) as CT,
(Case when A.RanSelection <= @DE then 'TRUE' else 'FALSE' end) as DE,
(Case when A.RanSelection <= @FL then 'TRUE' else 'FALSE' end) as FL,
(Case when A.RanSelection <= @GA then 'TRUE' else 'FALSE' end) as GA,
(Case when A.RanSelection <= @HI then 'TRUE' else 'FALSE' end) as HI,
(Case when A.RanSelection <= @IA then 'TRUE' else 'FALSE' end) as IA,
(Case when A.RanSelection <= @ID then 'TRUE' else 'FALSE' end) as ID,
(Case when A.RanSelection <= @IL then 'TRUE' else 'FALSE' end) as IL,
(Case when A.RanSelection <= @IN then 'TRUE' else 'FALSE' end) as "IN",
(Case when A.RanSelection <= @KS then 'TRUE' else 'FALSE' end) as KS,
(Case when A.RanSelection <= @KY then 'TRUE' else 'FALSE' end) as KY,
(Case when A.RanSelection <= @LA then 'TRUE' else 'FALSE' end) as LA,
(Case when A.RanSelection <= @MA then 'TRUE' else 'FALSE' end) as MA,
(Case when A.RanSelection <= @MD then 'TRUE' else 'FALSE' end) as MD,
(Case when A.RanSelection <= @ME then 'TRUE' else 'FALSE' end) as ME,
(Case when A.RanSelection <= @MI then 'TRUE' else 'FALSE' end) as MI,
(Case when A.RanSelection <= @MN then 'TRUE' else 'FALSE' end) as MN,
(Case when A.RanSelection <= @MO then 'TRUE' else 'FALSE' end) as MO,
(Case when A.RanSelection <= @MS then 'TRUE' else 'FALSE' end) as MS,
(Case when A.RanSelection <= @MT then 'TRUE' else 'FALSE' end) as MT,
(Case when A.RanSelection <= @NC then 'TRUE' else 'FALSE' end) as NC,
(Case when A.RanSelection <= @ND then 'TRUE' else 'FALSE' end) as ND,
(Case when A.RanSelection <= @NE then 'TRUE' else 'FALSE' end) as NE,
(Case when A.RanSelection <= @NH then 'TRUE' else 'FALSE' end) as NH,
(Case when A.RanSelection <= @NJ then 'TRUE' else 'FALSE' end) as NJ,
(Case when A.RanSelection <= @NM then 'TRUE' else 'FALSE' end) as NM,
(Case when A.RanSelection <= @NV then 'TRUE' else 'FALSE' end) as NV,
(Case when A.RanSelection <= @NY then 'TRUE' else 'FALSE' end) as NY,
(Case when A.RanSelection <= @OH then 'TRUE' else 'FALSE' end) as OH,
(Case when A.RanSelection <= @OK then 'TRUE' else 'FALSE' end) as OK,
(Case when A.RanSelection <= @OR then 'TRUE' else 'FALSE' end) as "OR",
(Case when A.RanSelection <= @PA then 'TRUE' else 'FALSE' end) as PA,
(Case when A.RanSelection <= @RI then 'TRUE' else 'FALSE' end) as RI,
(Case when A.RanSelection <= @SC then 'TRUE' else 'FALSE' end) as SC,
(Case when A.RanSelection <= @SD then 'TRUE' else 'FALSE' end) as SD,
(Case when A.RanSelection <= @TN then 'TRUE' else 'FALSE' end) as TN,
(Case when A.RanSelection <= @TX then 'TRUE' else 'FALSE' end) as TX,
(Case when A.RanSelection <= @UT then 'TRUE' else 'FALSE' end) as UT,
(Case when A.RanSelection <= @VA then 'TRUE' else 'FALSE' end) as VA,
(Case when A.RanSelection <= @VT then 'TRUE' else 'FALSE' end) as VT,
(Case when A.RanSelection <= @WA then 'TRUE' else 'FALSE' end) as WA,
(Case when A.RanSelection <= @WI then 'TRUE' else 'FALSE' end) as WI,
(Case when A.RanSelection <= @WV then 'TRUE' else 'FALSE' end) as WV,
(Case when A.RanSelection <= @WY then 'TRUE' else 'FALSE' end) as WY,
(Case when A.RanSelection <= @DC then 'TRUE' else 'FALSE' end) as DC,
(Case when A.RanSelection <= @ME1 then 'TRUE' else 'FALSE' end) as ME1,
(Case when A.RanSelection <= @ME2 then 'TRUE' else 'FALSE' end) as ME2,
(Case when A.RanSelection <= @NE1 then 'TRUE' else 'FALSE' end) as NE1,
(Case when A.RanSelection <= @NE2 then 'TRUE' else 'FALSE' end) as NE2,
(Case when A.RanSelection <= @NE3 then 'TRUE' else 'FALSE' end) as NE3
from
(SELECT TOP 1000000 [Num],RAND(convert(varbinary, newid())) as RanSelection
FROM [Database].[dbo].[Numbers])A)B)C)D
group by D.WinLose)E
)F
```
