SQL Server NEGHYPGEOM_DIST Function

Updated 2024-03-13 12:33:12.017000

Description

Use the scalar function NEGHYPGEOM_DIST to calculate the probability mass function or the cumulative distributive function of the negative hypergeometric distribution. The negative hypergeometric distribution models the sample size required to achieve a specified number of failures given the number of successes and the size of the population. The probability mass function can be calculated as:

P(x)=\frac{\binom{x-1}{k-1}\binom{N-x}{M-k}}{\binom{N}{M}};k\leq{x}\leq{k+N-M}

Where:

The cumulative distribution function can be calculated as:

cdf=\sum_{x=k}^{num\_trials}\frac{\binom{x-1}{k-1}\binom{N-x}{M-k}}{\binom{N}{M}};k\leq{x}\leq{k+N-M}

Syntax

SELECT [westclintech].[wct].[NEGHYPGEOM_DIST](
  <@num_trials, float,>
 ,<@num_success, float,>
 ,<@pop_success, float,>
 ,<@pop_size, float,>
 ,<@Cumulative, bit,>)

Arguments

@num_trials

the number of trials until @num_success have occurred. @num_trials must be of a type float or of type that intrinsically converts to float.

@num_success

the number of successes to achieve with the sample. @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.

@Cumulative

a bit value identifying whether the probability mass function ( 'False') or the cumulative distribution function ( 'True') is to be returned. @Cumulative is of a type bit or a type that implicitly converts to bit.

Return Type

float

Remarks

@num_trials, @num_success, @pop_success, and @pop_size are truncated; only the integer part is used.

If @num_success < 0 then NULL is returned.

If @pop_success < 0 then NULL is returned.

If @num_trials = 0 then NULL is returned.

If @pop_size = 0 then NULL is returned.

If @pop_success > @pop_size then NULL is returned.

If @num_trials > @pop_size - @pop_success + @num_success then NULL is returned.

Examples

In an urn with 1000 balls of which 300 are white we want to calculate the probability of drawing exactly 40 white balls in 150 draws from the urn.

SELECT wct.NEGHYPGEOM_DIST(   150,    --@num_trials
                              40,     --@num_success
                              300,    --@pop_success
                              1000,   --@pop_size
                              'False' --@Cumulative
                          ) as NEGHYPGEOM_DIST;

This produces the following result.

We can contrast this to the value returned using the binomial coefficients.

SELECT wct.NEGHYPGEOM_DIST(   150,    --@num_trials
                              40,     --@num_success
                              300,    --@pop_success
                              1000,   --@pop_size
                              'False' --@Cumulative
                          ) as NEGHYPGEOM_DIST,
       wct.BICO(150 - 1, 40 - 1) * wct.BICO(1000 - 150, 300 - 40) / wct.BICO(1000,
                 300) as pmf;

This produces the following result.

Using the same data we calculate the cumulative distribution function.

SELECT wct.NEGHYPGEOM_DIST(   150,   --@num_trials
                              40,    --@num_success
                              300,   --@pop_success
                              1000,  --@pop_size
                              'True' --@Cumulative
                          ) as NEGHYPGEOM_DIST;

This produces the following result.

In this example, we demonstrated the relationship between the hypergeometric and the negative hypergeometric distribution .

SELECT wct.NEGHYPGEOM_DIST(   150,   --@num_trials
                              40,    --@num_success
                              300,   --@pop_success
                              1000,  --@pop_size
                              'True' --@Cumulative
                          ) as NEGHYPGEOM_DIST,
       wct.HYPGEOM_DIST(150 - 40, 150, 1000 - 300, 1000, 'True') as HYPGEOM_DIST;

This produces the following result.

In this example we use the XLeratorDB SeriesInt function to show how the cdf is the sum of the pmf values from @num_success to @num_trial.

DECLARE @num_success as float = 40;
DECLARE @num_trials as float = 150;
DECLARE @pop_success as float = 300;
DECLARE @pop_size as float = 1000;
SELECT num_trials,
       pmf,
       SUM(pmf) OVER (ORDER BY num_trials) as cdf
FROM
(
    SELECT SeriesValue as num_trials,
           wct.NEGHYPGEOM_DIST(seriesValue, @num_success, @pop_success, @pop_size,
                     'False') as pmf
    FROM wct.SeriesInt(@num_success, @num_trials, NULL, NULL, NULL)
) n;

This produces the following result.

See Also

BICO - Binomial coefficient

BINOMDIST - Binomial distribution

HYPGEOM_DIST - Hypergeometric distribution

LCHOOSE - Natural logarithm of the binomial coefficient

NEGBINOM_DIST - Negative binomial distribution

NEGHYPGEOM_INV - Calculate the quantiles of the negative hypergeometric distribution.