NEGHYPGEOM_INV

Updated 2023-11-03 21:56:06.520000

Syntax

SELECT [westclintech].[wct].[NEGHYPGEOM_INV](
  <@p, float,>
 ,<@num_success, float,>
 ,<@pop_success, float,>
 ,<@pop_size, float,>)

Description

Use the scalar function 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.

Arguments

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

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

@P

the probability to be evaluated. @P 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.

{"columns":[{"field":"NEGHYPGEOM_INV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NEGHYPGEOM_INV":"106"}]}

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.

{"columns":[{"field":"Trial","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"num_success","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Trial":"2","num_success":"23"},{"Trial":"30","num_success":"21"},{"Trial":"47","num_success":"23"},{"Trial":"84","num_success":"21"},{"Trial":"92","num_success":"23"}]}