create table st_pt (n int, pt int);
insert into st_pt values (3, 0);
insert into st_pt values (4, 0);
insert into st_pt values (5, 0);
insert into st_pt values (6, 0);
insert into st_pt values (7, 0);
insert into st_pt values (8, 0);
insert into st_pt values (9, 1);
insert into st_pt values (10, 2);
insert into st_pt values (11, 3);
insert into st_pt values (12, 4);
insert into st_pt values (13, 5);
insert into st_pt values (14, 6);
insert into st_pt values (15, 8);
insert into st_pt values (16, 10);
insert into st_pt values (17, 13);
insert into st_pt values (18, 16);
-- used in second run: negative point values for low amounts
-- insert into st_pt values (3, -5);
-- insert into st_pt values (4, -4);
-- insert into st_pt values (5, -3);
-- insert into st_pt values (6, -2);
-- insert into st_pt values (7, -1);
-- insert into st_pt values (8, 0);
-- insert into st_pt values (9, 1);
-- insert into st_pt values (10, 2);
-- insert into st_pt values (11, 3);
-- insert into st_pt values (12, 4);
-- insert into st_pt values (13, 5);
-- insert into st_pt values (14, 6);
-- insert into st_pt values (15, 8);
-- insert into st_pt values (16, 10);
-- insert into st_pt values (17, 13);
-- insert into st_pt values (18, 16);
create table st_mod (n int, m int);
insert into st_mod values (3, -4);
insert into st_mod values (4, -3);
insert into st_mod values (5, -3);
insert into st_mod values (6, -2);
insert into st_mod values (7, -2);
insert into st_mod values (8, -1);
insert into st_mod values (9, -1);
insert into st_mod values (10, 0);
insert into st_mod values (11, 0);
insert into st_mod values (12, 1);
insert into st_mod values (13, 1);
insert into st_mod values (14, 2);
insert into st_mod values (15, 2);
insert into st_mod values (16, 3);
insert into st_mod values (17, 3);
insert into st_mod values (18, 4);
create table d6 (n int, p float);
insert into d6 values (1, 1.0/6.0);
insert into d6 values (2, 1.0/6.0);
insert into d6 values (3, 1.0/6.0);
insert into d6 values (4, 1.0/6.0);
insert into d6 values (5, 1.0/6.0);
insert into d6 values (6, 1.0/6.0);
create table s3d6 (p float, n int, pt int, m int);
insert into s3d6 (p, n, pt, m)
select sum(a.p * b.p * c.p) as p,
(a.n + b.n + c.n) as x,
pt,
m
from d6 a, d6 b, d6 c, st_mod, st_pt
where st_mod.n = (a.n+b.n+c.n)
and st_pt.n = st_mod.n
group by x, pt, m;
create table t4d6 (a int, b int, c int, d int, p float);
insert into t4d6
select a.n, b.n, c.n, d.n, sum(a.p*b.p*c.p*d.p)
from d6 a, d6 b, d6 c, d6 d
group by a.n, b.n, c.n, d.n;
update t4d6 set a = 0 where a <= b and a <= c and a <= d;
update t4d6 set b = 0 where b <= a and b <= c and b <= d;
update t4d6 set c = 0 where c <= a and c <= b and c <= d;
update t4d6 set d = 0 where d <= a and d <= b and d <= c;
create table s4d6 (p float, n int, pt int, m int);
insert into s4d6 (p, n, pt, m)
select sum(p) as p,
(a+b+c+d) as x,
pt,
m
from t4d6, st_mod, st_pt
where st_mod.n = (a+b+c+d)
and st_pt.n = st_mod.n
group by x, pt, m;
create table stats3d6 (st_p float, st_pt int, st_m int, st_s int,
a int, b int, c int, d int, e int, f int);
select 'Started at ' || current_time;
insert into stats3d6 (a, b, c, d, e, f, st_p, st_pt, st_m, st_s)
select a.n as a,
b.n as b,
c.n as c,
d.n as d,
e.n as e,
f.n as f,
a.p*b.p*c.p*d.p*e.p*f.p as st_p,
a.pt + b.pt + c.pt + d.pt + e.pt + f.pt as st_pt,
a.m+b.m+c.m+d.m+e.m+f.m as st_m,
a.n+b.n+c.n+d.n+e.n+f.n as st_s
from s3d6 a, s3d6 b, s3d6 c, s3d6 d, s3d6 e, s3d6 f;
select 'Finished at ' || current_time;
create table stats4d6 (st_p float, st_pt int, st_m int, st_s int,
a int, b int, c int, d int, e int, f int);
select 'Started at ' || current_time;
insert into stats4d6 (a, b, c, d, e, f, st_p, st_pt, st_m, st_s)
select a.n as a,
b.n as b,
c.n as c,
d.n as d,
e.n as e,
f.n as f,
a.p*b.p*c.p*d.p*e.p*f.p as st_p,
a.pt + b.pt + c.pt + d.pt + e.pt + f.pt as st_pt,
a.m+b.m+c.m+d.m+e.m+f.m as st_m,
a.n+b.n+c.n+d.n+e.n+f.n as st_s
from s4d6 a, s4d6 b, s4d6 c, s4d6 d, s4d6 e, s4d6 f;
select 'Finished at ' || current_time;
create table stats3d6l (st_p float, st_pt int, st_m int, st_s int);
insert into stats3d6l (st_p, st_pt, st_m, st_s)
select sum(st_p),
st_pt,
avg(st_m),
avg(st_s)
from stats3d6
where (a > 13 or b > 13 or c > 13 or d > 13 or e > 13 or f > 13)
and st_m > 0
group by st_pt;
create table stats4d6l (st_p float, st_pt int, st_m int, st_s int);
select 'Started at ' || current_time;
insert into stats4d6l (st_p, st_pt, st_m, st_s)
select sum(st_p),
st_pt,
avg(st_m),
avg(st_s)
from stats4d6
where (a > 13 or b > 13 or c > 13 or d > 13 or e > 13 or f > 13)
and st_m > 0
group by st_pt;
select 'Finished at ' || current_time;
-- Normalize
create table stats3d6l_norm (st_p float, st_pt int, st_m int, st_s int);
insert into stats3d6l_norm (st_p, st_pt, st_m, st_s)
select (st_p / (select sum(st_p) from stats3d6l)), st_pt, st_m, st_s
from stats3d6l;
create table stats4d6l_norm (st_p float, st_pt int, st_m int, st_s int);
insert into stats4d6l_norm (st_p, st_pt, st_m, st_s)
select (st_p / (select sum(st_p) from stats4d6l)), st_pt, st_m, st_s
from stats4d6l;
-- And the percentile output
select
round(cast ((select sum(b.st_p) from stats3d6l_norm b
where b.st_pt >= a.st_pt) as numeric)*100.0, 4) as st_pc,
st_pt, st_m, st_s
from stats3d6l_norm a
order by st_pt;
select
round(cast ((select sum(b.st_p) from stats4d6l_norm b
where b.st_pt >= a.st_pt) as numeric)*100.0, 4) as st_pc,
st_pt, st_m, st_s
from stats4d6l_norm a
order by st_pt;
