Updated: 9 August 2010
Use PROB_q to calculate the probability that values in a range are between two limits.
Syntax
SELECT [wctStatistics].[wct].[PROB_q] (
<@Prob_range_X_range_RangeQuery, nvarchar(4000),>
,<@Lower_limit, float,>
,<@Upper_limit, float,>)
Arguments
@Prob_range_X_range_RangeQuery
the select statement, as text, used to obtain the probabilities and x-values for the PROB_q calculation.
@Lower_limit
is the lower bound on the value for which you want a probability. @Lower_limit is an expression of type float or of a type that can be implicitly converted to float.
@Upper_limit
is the upper bound on the value for which you want a probability. @Upper_limit is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If any value in the @Prob_range_ ColumnName ≤ 0 or if any value in the @Prob_range_ ColumnName >1, PROB_q returns an error.
· If the sum of the values in the @Prob_range_ ColumnName <> 1, PROB_q returns an error.
· If @Upper_limit is NULL, PROB_q returns that probability of being equal to the lower limit.
· If the number of data point in @Prob_range_ColumnName <> the number of data points in @X_range_ColumnName, PROB_q returns an error.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #p1(
[x] [float] NOT NULL,
[prob] [float] NOT NULL
)
INSERT INTO #p1 VALUES (0,0.1)
INSERT INTO #p1 VALUES (1,0.01)
INSERT INTO #p1 VALUES (2,0.15)
INSERT INTO #p1 VALUES (3,0.25)
INSERT INTO #p1 VALUES (5,0.05)
INSERT INTO #p1 VALUES (5,0.06)
INSERT INTO #p1 VALUES (6,0.07)
INSERT INTO #p1 VALUES (7,0.12)
INSERT INTO #p1 VALUES (8,0.09)
INSERT INTO #p1 VALUES (0,0.1)
To return the the probability of x being equal to zero:
SELECT wct.PROB_q('SELECT prob, x from #p1',0,0)
This produces the following result
----------------------
0.2
(1 row(s) affected)
To return the probability of x being between 1 and 5:
SELECT wct.PROB_q('SELECT prob, x from #p1',1,5)
This produces the following result
----------------------
0.52
(1 row(s) affected)