Updated: 01 February 2009
Use wct.PROB to calculate the probability that values in a range are between two limits.
Syntax
SELECT [wctStatistics].[wct].[PROB] (
<@TableName, nvarchar(4000),>
,<@Prob_range_ColumnName, nvarchar(4000),>
,<@X_range_ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@Lower_limit, float,>
,<@Upper_limit, float,>)
Arguments
@TableName
the name, as text, of the table or view that contains the x-values and probabilities to be used in the PROB calculation.
@Prob_range_ ColumnName
the name, as text, of the column in the table or view specified by @TableName that contains the set of probabilities associated with values in @X_range_ ColumnName to be used in the PROB calculation.
@X_range_ ColumnName
the name, as text, of the column in the table or view specified by @TableName that contains the numeric values of x with which there are associated probabilities.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Values_TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
@Lower_limit
is the lower bound on the value for which you want a probability. @Quart 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. @Quart 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 returns an error.
· If the sum of the values in the @Prob_range_ ColumnName <> 1, PROB returns an error.
· If @Upper_Limit is NULL, PROB 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 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('#p1','prob','x','',NULL,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('#p1','prob','x','',NULL,1,5)
This produces the following result
----------------------
0.52
(1 row(s) affected)