CREATE TABLE die(
face_id INT NOT NULL,
face_value INT NOT NULL,
probability REAL NOT NULL,
CONSTRAINT pk_die PRIMARY KEY (face_id));
INSERT INTO die VALUES (1, 1, 1/6 + 1/12);
INSERT INTO die VALUES (2, 3, 1/6 + 1/12);
INSERT INTO die VALUES (3, 4, 1/6 + 1/12);
INSERT INTO die VALUES (4, 5, 1/6 - 1/12);
INSERT INTO die VALUES (5, 6, 1/6 - 1/12);
INSERT INTO die VALUES (6, 8, 1/6 - 1/12);
select * from die order by face_id
/
var N number
exec :N := 2
with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
, prob
, i
from (select level l from number_of_dies connect by level <= power(cnt,:N))
, number_of_dies
model
reference r on (select face_id, face_value, probability from die)
dimension by (face_id)
measures (face_value,probability)
main m
partition by (l rn, cnt)
dimension by (0 i)
measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
rules iterate (1000) until (iteration_number + 1 = :N)
( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
, remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
, sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
, prob[0] = prob[0] * probability[die_face_id[0]]
)
)
select sum_value "Sum"
, sum(prob) "Probability"
from all_probabilities
group by rollup(sum_value)
order by sum_value
/
exec :N := 3
with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
, prob
, i
from (select level l from number_of_dies connect by level <= power(cnt,:N))
, number_of_dies
model
reference r on (select face_id, face_value, probability from die)
dimension by (face_id)
measures (face_value,probability)
main m
partition by (l rn, cnt)
dimension by (0 i)
measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
rules iterate (1000) until (iteration_number + 1 = :N)
( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
, remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
, sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
, prob[0] = prob[0] * probability[die_face_id[0]]
)
)
select sum_value "Sum"
, sum(prob) "Probability"
from all_probabilities
group by rollup(sum_value)
order by sum_value
/
update die
set face_value = face_id
, probability = 1/6
/
select * from die order by face_id
/
with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
, prob
, i
from (select level l from number_of_dies connect by level <= power(cnt,:N))
, number_of_dies
model
reference r on (select face_id, face_value, probability from die)
dimension by (face_id)
measures (face_value,probability)
main m
partition by (l rn, cnt)
dimension by (0 i)
measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
rules iterate (1000) until (iteration_number + 1 = :N)
( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
, remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
, sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
, prob[0] = prob[0] * probability[die_face_id[0]]
)
)
select sum_value "Sum"
, sum(prob) "Probability"
from all_probabilities
group by rollup(sum_value)
order by sum_value
/
exec :N := 4
with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
, prob
, i
from (select level l from number_of_dies connect by level <= power(cnt,:N))
, number_of_dies
model
reference r on (select face_id, face_value, probability from die)
dimension by (face_id)
measures (face_value,probability)
main m
partition by (l rn, cnt)
dimension by (0 i)
measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
rules iterate (1000) until (iteration_number + 1 = :N)
( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
, remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
, sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
, prob[0] = prob[0] * probability[die_face_id[0]]
)
)
select sum_value "Sum"
, sum(prob) "Probability"
from all_probabilities
group by rollup(sum_value)
order by sum_value
/