NEGBINOM_DIST
Updated 2023-11-03 21:29:56.977000
Syntax
SELECT [westclintech].[wct].[NEGBINOM_DIST](
<@Number_f, int,>
,<@Number_s, int,>
,<@Probability_s, float,>
,<@Cumulative, bit,>)
Description
Use the scalar function NEGBINOM_DIST to calculate the probability mass function or the cumulative distributive function of the negative binomial distribution. The negative binomial distribution calculates the probability of k successes occurring before r failures in a sequence of Bernoulli trials.
Arguments
@Number_f
the number of failures. @Number_f must be of a type int or of type that intrinsically converts to int.
@Number_s
the number of successes. @Number_s must be of a type int or of type that intrinsically converts to int.
@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.
@Probability_s
the probability of success. @Probability_s must be of a type float or of type that intrinsically converts to float.
Return Type
float
Remarks
0 <= @Number_f.
0 < @Number_s.
0 ≤ @P ≤ 1
Examples
Stephen Curry of the Golden State Warriors makes 91.4% of his free throws. In a game in which he attempts 7 free throws what is the likelihood the he will miss 2?
SELECT wct.NEGBINOM_DIST( 2, --@Number_f
5, --@Number_s
.914, --@Probability_s
'False' --@Cumulative
) as NEGBINOM_DIST;
This produces the following result.
{"columns":[{"field":"NEGBINOM_DIST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NEGBINOM_DIST":"0.0707651025245018"}]}
This is the probability that he will miss exactly 2 free throws. To calculate the possibility that we will miss no more than 2 three throws we would enter the following SQL.
SELECT wct.NEGBINOM_DIST( 2, --@Number_f
5, --@Number_s
.914, --@Probability_s
'True' --@Cumulative
) as NEGBINOM_DIST;
This produces the following result.
{"columns":[{"field":"NEGBINOM_DIST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NEGBINOM_DIST":"0.98291668545255"}]}
The calculation of the probability mass function is closely related to the combinatorial function.
SELECT wct.NEGBINOM_DIST(failures, trials - failures, p, 'False') as NEGBINOM_DIST,
wct.COMBIN(trials - 1, failures) * POWER(p, trials - failures) * POWER(1 -
p, failures) as COMBIN
FROM
(
VALUES
(2, 7, cast(0.914 as float))
) n (failures, trials, p);
This produces the following result.
{"columns":[{"field":"NEGBINOM_DIST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"COMBIN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NEGBINOM_DIST":"0.0707651025245018","COMBIN":"0.0707651025245018"}]}
The cumulative distribution function is closely related to the Beta distribution.
SELECT wct.NEGBINOM_DIST(failures, trials - failures, p, 'True') as NEGBINOM_DIST,
wct.BETA_DIST(p, trials - failures, failures + 1, 'True', NULL, NULL) as
BETA_DIST
FROM
(
VALUES
(2, 7, cast(0.914 as float))
) n (failures, trials, p);
This produces the following result.
{"columns":[{"field":"NEGBINOM_DIST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BETA_DIST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NEGBINOM_DIST":"0.98291668545255","BETA_DIST":"0.98291668545255"}]}
See Also
BETA_DIST - Calculate pdf or cdf of beta distribution
COMBIN - Combinatorial function