Updated: 30 September 2015
Use NEGHYPGEOM_INV to calculate the quantiles of the negative hypergeometric distribution. NEGHYPGEOM_INV calculates the smallest number of trials that returns a p-value greater than equal to the supplied p-value for a given number of trial successes in a population having a specified number of successes.
the probability to be evaluated. @P must be of a type float or of type that intrinsically converts to float.
the number of successes to be achieved in the trial. @num_success must be of a type float or of type that intrinsically converts to float.
the number of successes in the population. @pop_success must be of a type float or of type that intrinsically converts to float.
the size of the population. @pop_size must be of a type float or of a type that intrinsically converts to float.
· @num_success, @pop_success, and @pop_size are truncated; only the integer part is used.
· If @P < 0 or @P > 1 then NULL is returned.
· If @pop_size < 0 then NULL is returned.
· If @pop_success < 0 then NULL is returned.
· If @num_success < 0 then NULL is returned.
· If @num_success > @pop_success then NULL is returned.
In an urn with 1000 balls of which 300 are white we want to calculate the number of trials required to randomly select at least 25 white balls 95% of the time.
) as NEGHYPGEOM_INV
This produces the following result
In other words, in a random sample of 106 balls, we would expect to have 25 or more white balls 95% of the time.
You could run the following SQL to simulate this calculation
COUNT(*) as num_success
--Divide the random numbers into 100 groups of 106
wct.TRUNC((seq-1)/106,1) as trial,
--Classify the balls as B or W
WHEN SeriesValue < 301 Then 'W'
END as ball
--Generate 100 trials of 106 draws
ball = 'W'
--Only interested in results < 25
COUNT(*) < 25
The previous SQL uses the SeriesInt function to randomly generate numbers between 1 and 1,000. If the randomly generated number is less than 301, we count that as a white ball otherwise it is counted as a black ball. We generate 10,600 random numbers which are then grouped together into 100 groups and count the number of groups that have less than 25 white balls. We would expect, on average, to return about 5 rows 95% of the time.
Here are the results, though your results will be different.