Hello Gurus ,
I was going through this blog https://rwijk.blogspot.com/2009/03/calculating-probabilities-with-n-throws.html
can someone please help me understand model clause is used here ?
Lets say we have only 2 faces of a die each with same probability or a different probabilty , so if a die is rolled N time, what is the probability of various sums that can come up in a roll of N ?
create table die as
select 1 face_id , 1 face_value , 0.25 probability from dual union all
select 2 , 2 , 0.25 from dual
for calculating-probabilities-with-n-throws and i was debugging how model clause here is generating various combinations like suppose if it is a 2 face die and rolled 3 times, so these will be face_value combinations that can come
[1,1,1]
[1,1,2]
[1,2,1]
[1,2,2]
[2,2,1]
[2,2,2]
[2,1,1]
[2,1,2]
and then we have to sum their probabilities by looking it from given table
Here is the sql that is being used in this blog .
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 ;
How the rules are exactly evaluating to generate various combinations ? Suppose if a 2 face die is rolled 3 times , (2*2*2) 8 rows are generated through connect clause and reference and main are declared and iteration clause is used to repetitively apply rules but
how these following rules
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]]
How these rules are getting evaluated to generate various sums is not getting clear,
is remainder measure a incremental column which gets incremented when the control gets on a new row each time out of the 8 rows ?
Is value of cv(cnt) number of faces of die which is 2 in this case?
model clause is difficult to understand applied here ,please someone explain