NEGHYPGEOM_INV
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.
Syntax
SELECT [wct].[NEGHYPGEOM_INV](
<@p, float,>
,<@num_success, float,>
,<@pop_success, float,>
,<@pop_size, float,>)
Arguments
@P
the probability to be evaluated. @P must be of a type float or of type that intrinsically converts to float.
@num_success
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.
@pop_success
the number of successes in the population. @pop_success must be of a type float or of type that intrinsically converts to float.
@pop_size
the size of the population. @pop_size must be of a type float or of a type that intrinsically converts to float.
Return Type
float
Remarks
· @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.
Examples
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.
SELECT
wct.NEGHYPGEOM_INV(
0.95 --@p
,25 --@num_success
,300 --@pop_success
,1000 --@pop_size
) 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
SELECT
Trial,
COUNT(*) as num_success
FROM (
SELECT
--Divide the random numbers into 100 groups of 106
wct.TRUNC((seq-1)/106,1) as trial,
--Classify the balls as B or W
CASE
WHEN SeriesValue < 301 Then 'W'
ELSE 'B'
END as ball
FROM
--Generate 100 trials of 106 draws
wct.SERIESINT(1,1000,1,10600,'R')
)n
WHERE
ball = 'W'
GROUP BY
trial
--Only interested in results < 25
HAVING
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.
See Also