Calculating Binomial Distribution Probabilities in PL/SQL

Next in my series of combinatoric/probability functions… let’s look at the Binomial Distribution. This distribution describes sequences of independent events that produce true/false results- such as flipping “heads” on a coin or drawing a particular card at random from from a deck and then shuffling it back into the deck before drawing the next card.

Given a set of t trials, with a probability p of a single success occurring, the chances of getting x successes is:

$\displaystyle P ={t \choose x} * p^x * (1-p)^{(t-x)}$

So, the probability of flipping 5 heads out of 7 trials with a fair coin (i.e. p= 0.5) is:

$\displaystyle P ={7 \choose 5} * 0.5^5 * (1-0.5)^{(7-5)} = 0.1640625$

Similarly, the probability of drawing 5 red cards (hearts or diamonds) out of 7 trials from a standard deck would also be 0.1640625 because the probability of drawing a heart or diamond is 26/52 or 1/2, the same as flipping heads on a coin. And the probability doesn’t change each time as long as you shuffle the card back into the deck before each trial.

Something a little harder – the probability of drawing 5 aces out of 7 trials from a standard deck of cards (shuffling the card back in each time) uses p = 4/52 because there are four aces in the deck for each trial.

$\displaystyle P ={7 \choose 5} * (4/52)^5 * (1-(4/52))^{(7-5)} = 0.00004819237401260017029565814280519171472998$

Thus, not very likely.

Using the combination count function from one of my previous articles makes implementing the formula in pl/sql fairly straightforward.

CREATE OR REPLACE FUNCTION binomial_probability(
    p_trial_count           IN INTEGER,
    p_success_count         IN INTEGER,
    p_success_probability   IN NUMBER)
    RETURN NUMBER
IS
BEGIN
    RETURN   combination_cnt(p_trial_count, p_success_count)
           * POWER(p_success_probability, p_success_count)
           * POWER(1 - p_success_probability, p_trial_count - p_success_count);
END;

Using the function to calculate the coin flipping and card drawing examples described above.

SQL> select binomial_probability(7,5,1/2) flipping_heads from dual;

FLIPPING_HEADS
--------------
      .1640625

SQL> select binomial_probability(7,5,4/52) drawing_aces from dual;

DRAWING_ACES
------------
  .000048192

Due to the factorial calculations in the combination counts, large values could still overflow or underflow causing rounding errors. Likewise, exponentiation of small probabilities could also underflow beyond the NUMBER type’s capacity.

Despite these caveats – for real-world calculations, these functions have worked for me with more than enough significant digits of precision. I hope you find them helpful as well.

Thank you for reading. Questions and comments, as always, are welcome.