create table probabilities(
sum_of_dies number
,percentage number
)
/
create or replace package probabilities_sql
as
--
-- The procedure "calculate" calculates all probabilities with
-- p_number_of_dies throws of a die. The results of the calculation
-- are inserted into the table probabilities.
--
procedure calculate (p_number_of_throws in number)
;
end probabilities_sql;
/
create or replace package body probabilities_sql
as
procedure calculate (p_number_of_throws in number)
is
begin
insert into probabilities
( sum_of_dies
, percentage
)
with number_of_die_faces as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
, prob
, i
from --
-- Generate as many rows as there are possible combinations of the
-- dies. This equals: power(,p_number_of_throws).
-- For example: with a traditional die (6 faces) and 3 throws, there
-- are power(6,3) = 216 rows with a l-value running from 1 until 216.
--
( select level l
from number_of_die_faces
connect by level <= power(cnt,p_number_of_throws)
)
, number_of_die_faces
model
--
-- A reference model to be able to quickly lookup the face_value
-- and probability when provided a face_id
--
reference r on (select face_id, face_value, probability from die)
dimension by (face_id)
measures (face_value,probability)
main m
--
-- Each combination is in a different partition.
-- Which means it is easy to parallellize if necessary.
--
partition by (l rn, cnt)
dimension by (0 i)
measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
--
-- Iterate as many times as there are throws of the die.
--
rules iterate (1000) until (iteration_number+1=p_number_of_throws)
--
-- For each throw of the die, calculate the face_id, remainder, the
-- sum and probability. For the sum and probability, the reference
-- model is used as a lookup. Each iteration overwrites the previous
-- one.
--
( 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]]
)
)
--
-- All probabilities of each possible combination are now calculated.
-- Now, sum them all up per sum of all face_values.
--
select sum_value
, sum(prob)
from all_probabilities
group by sum_value
;
end calculate;
end probabilities_sql;
/
create or replace package probabilities_plsql
as
--
-- The procedure "calculate" calculates all probabilities with
-- p_number_of_dies throws of a die. The results of the calculation
-- are inserted into the table probabilities.
--
procedure calculate (p_number_of_throws in number)
;
end probabilities_plsql;
/
create package body probabilities_plsql
as
g_number_of_die_faces number(4)
;
procedure initialization
--
-- Calculate the number of die faces (6 in case of a traditional die) only once.
--
is
begin
select count(*)
into g_number_of_die_faces
from die
;
end initialization
;
function face_value
( p_face_id in die.face_id%type
) return die.face_value%type result_cache relies_on (die)
--
-- A lookup function returning the face_value of a given face_id.
-- This function is called multiple times for the same face_id's and
-- is therefore optimized by the result_cache hint.
--
is
l_face_value die.face_value%type;
begin
select face_value
into l_face_value
from die
where face_id = p_face_id
;
return l_face_value;
end face_value
;
function probability
( p_face_id in die.face_id%type
) return die.probability%type result_cache relies_on (die)
--
-- A lookup function returning the probability of a given face_id.
-- This function is called multiple times for the same face_id's and
-- is therefore optimized by the result_cache hint.
--
is
l_probability die.probability%type;
begin
select probability
into l_probability
from die
where face_id = p_face_id
;
return l_probability;
end probability
;
procedure calculate (p_number_of_throws in number)
is
l_die_face_id die.face_id%type;
l_remainder number(10);
l_sum probabilities.sum_of_dies%type;
l_probability probabilities.percentage%type
;
type ta_probabilities is table of probabilities%rowtype index by pls_integer;
a_probabilities ta_probabilities;
begin
--
-- Loop as many times as there are possible combinations of the
-- dies. This number equals: power(,p_number_of_throws).
-- For example: with a traditional die (6 faces) and 3 throws, there
-- are power(6,3) = 216 iterations.
--
for i in 1 .. power(g_number_of_die_faces,p_number_of_throws)
loop
l_remainder := i;
l_sum := 0;
l_probability := 1;
--
-- For each combination, iterate over all throws of each individual die,
-- and calculate the face_id of that die (using l_die_face_id and
-- l_remainder) and use that face_id to calculate the sum of the die
-- face values and the probability.
--
for j in 1 .. p_number_of_throws
loop
l_die_face_id := 1 + mod(l_remainder-1, g_number_of_die_faces);
l_remainder := ceil((l_remainder-l_die_face_id+1)/g_number_of_die_faces);
l_sum := l_sum + face_value(l_die_face_id);
l_probability := l_probability * probability(l_die_face_id);
end loop;
--
-- Sum up all the probabilities with the same sum.
--
a_probabilities(l_sum).sum_of_dies := l_sum;
a_probabilities(l_sum).percentage :=
nvl(a_probabilities(l_sum).percentage,0) + l_probability
;
end loop;
--
-- Bulk insert all calculated probabilities into the table PROBABILIIES.
--
forall i in indices of a_probabilities
insert into probabilities
values a_probabilities(i)
;
end calculate
;
begin
initialization;
end probabilities_plsql;
/