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.

SELECT [wct].[NEGHYPGEOM_INV](

<@p, float,>

,<@num_success, float,>

,<@pop_success, float,>

,<@pop_size, float,>)

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**.

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.

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.