SQL Server ROCTable Function

Updated 2023-11-02 12:19:09.947000

Description

Use the table-valued function ROCTABLE to show the calculation of the area under the ROC curve. The function accepts either raw or grouped data as input.

The function has a single input parameter which is an SQL SELECT statement which, when executed, returns a resultant table where the first column is the predicted probabilities. For the raw case, the SQL will return one additional column, consisting of zeroes and ones indicating the absence (0) or presence (1) of the characteristic of interest. You may also think of these as indicating the failure (0) or success (1) of the observation.

For the grouped case, the input SQL will return two additional columns containing the count of failures and successes for a predicted probability.

The function returns a table (described below) sorted by ascending predicted probability which calculates the True Positive Rate, the False Positive Rate, and the area under the ROC curve (AUROC). This is the same value returned by the LOGIT and LOGITSUM functions.

Syntax

SELECT * FROM [westclintech].[wct].[ROCTable](
   <@Matrix_RangeQuery, nvarchar(max),>)

Arguments

@Matrix_RangeQuery

the SELECT statement, as a string, which, when executed, creates the resultant table of predicted probabilities and Y values.

Return Type

table

Remarks

The first column returned by @Matrix_RangeQuery should contain the predicted probabilities where 0 <= predicted probability <= 1.

The resultant table returned by @Matrix_RangeQuery should return either 2 columns or 3 columns.

When the resultant table contains 2 columns the function assumes that the second column contains binary responses consisting of zero (0) or (1); the use of other values will produce unreliable results.

When the resultant table contains 3 columns the function assumes that the second column contains a count of the failures or absences and the third column contains a count of the successes or presence.

Examples

In this example we use the same data as was used in the LOGIT documentation, consisting of the Coronary Heart Disease data from Applied Logistic Regression, Third Edition by David W. Hosmer, Jr., Stanley Lemeshow, and Rodney X. Sturdivant . The data consist of a single independent variable (age) and an outcome (chd) which indicates the absence (0) or presence (1) of coronary heart disease. We will put the data into a temporary table, run the logistic regression, use the coefficients from the logistic to create the predicted probabilities and then produce the ROC table. Note that the AUROC value is actually returned by the LOGIT function; this example simply explains the calculation.

--Put the Hosmer data into the #chd table
SELECT *
INTO   #chd
  FROM (   VALUES (20, 0),
                  (23, 0),
                  (24, 0),
                  (25, 0),
                  (25, 1),
                  (26, 0),
                  (26, 0),
                  (28, 0),
                  (28, 0),
                  (29, 0),
                  (30, 0),
                  (30, 0),
                  (30, 0),
                  (30, 0),
                  (30, 0),
                  (30, 1),
                  (32, 0),
                  (32, 0),
                  (33, 0),
                  (33, 0),
                  (34, 0),
                  (34, 0),
                  (34, 1),
                  (34, 0),
                  (34, 0),
                  (35, 0),
                  (35, 0),
                  (36, 0),
                  (36, 1),
                  (36, 0),
                  (37, 0),
                  (37, 1),
                  (37, 0),
                  (38, 0),
                  (38, 0),
                  (39, 0),
                  (39, 1),
                  (40, 0),
                  (40, 1),
                  (41, 0),
                  (41, 0),
                  (42, 0),
                  (42, 0),
                  (42, 0),
                  (42, 1),
                  (43, 0),
                  (43, 0),
                  (43, 1),
                  (44, 0),
                  (44, 0),
                  (44, 1),
                  (44, 1),
                  (45, 0),
                  (45, 1),
                  (46, 0),
                  (46, 1),
                  (47, 0),
                  (47, 0),
                  (47, 1),
                  (48, 0),
                  (48, 1),
                  (48, 1),
                  (49, 0),
                  (49, 0),
                  (49, 1),
                  (50, 0),
                  (50, 1),
                  (51, 0),
                  (52, 0),
                  (52, 1),
                  (53, 1),
                  (53, 1),
                  (54, 1),
                  (55, 0),
                  (55, 1),
                  (55, 1),
                  (56, 1),
                  (56, 1),
                  (56, 1),
                  (57, 0),
                  (57, 0),
                  (57, 1),
                  (57, 1),
                  (57, 1),
                  (57, 1),
                  (58, 0),
                  (58, 1),
                  (58, 1),
                  (59, 1),
                  (59, 1),
                  (60, 0),
                  (60, 1),
                  (61, 1),
                  (62, 1),
                  (62, 1),
                  (63, 1),
                  (64, 0),
                  (64, 1),
                  (65, 1),
                  (69, 1)) n (age, chd);

--Run LOGIT and store the results in #mylogit
SELECT *
INTO   #mylogit
  FROM wct.LOGIT('SELECT age,chd FROM #chd', 2);

--Calculate the predicted probabilities for each row in #chd and store the
--predicted probability and the chd value in #t
SELECT wct.LOGITPRED('SELECT stat_val FROM #mylogit where stat_name = ''b'' ORDER BY idx', cast(age as varchar(max))) as [p predicted],
       chd as y
INTO   #t
  FROM #chd;

--Run the ROCTable function
SELECT *
  FROM wct.ROCTable('SELECT * FROM #t');

This produces the following result.

You can see from the table that the cumulative AUROC value is 0.789881680946553. This is the same as the value returned by LOGIT .

--Get the AUROC value from #mylogit
SELECT stat_val
  FROM #mylogit
 WHERE stat_name = 'AUROC';

This produces the following result.

However, ROCTABLE does return the False Positive Rate and the True Positive Rate, which can be graphed using SSRS, Excel, or any tool that you prefer. In this example, I have simply copied the FalsePositiveRate and TruePositiveRate from ROCTABLE , pasted them into Excel and then produced the following graph.

It is worth noting that our input data consisted of 100 rows, yet ROCTABLE only returned 43 rows of data from the temporary table #t, even though we generated a predicted probability for all 100 rows. This is because there were not 100 unique predicted probabilities. We can get the number of unique predicted probabilities using the following SQL.

SELECT COUNT(DISTINCT [p predicted]) as [COUNT p predicted]
  FROM #t;

This produces the following result, which matches what was returned by ROCTable .

As Hosmer points out in section 5.4.2 "let n₁ denote the number of subjects with y = 1 and n₀ denote the number of subjects with y = 0. We can then create n₁ x n₀ pairs; each subject with y = 1 , paired with each subject with y = 0 . Of these n₁ x n₀ pairs, we determine the proportion of the pairs where the subject with y = 1 had the higher of the two probabilities. This proportion may be shown to be equal to the area under the ROC Curve."

The technique that he is suggesting lends itself quite well to SQL and we can use it to check the AUROC calculation in both LOGIT and ROCTable . We would not recommend this calculation as a practical matter as it requires a Cartesian product; in this case 57 x 43 combinations.

--Calculate the area under the ROC Curve using a Cartesian product
;with mycte
as (SELECT n1.y as y1,
           n1.[p predicted] as p1,
           n0.y as y0,
           n0.[p predicted] as p0
    FROM #t n1,
         #t n0
    WHERE n1.y = 1
          AND n0.y = 0)
SELECT COUNT(m1.y1) / cast(n.pairs as float) As AUROC,
       n.pairs
FROM
(SELECT SUM(y1)FROM mycte) n(pairs) ,
mycte m1
WHERE m1.p1 > m1.p0
GROUP BY n.pairs;

This produces the following result.

Now, let's look at an example using grouped data. The data consist of 3 independent variables; x1, x2, and x3, and 2 additional columns; the number of successes for that combination of independent variables and the number of observations for that combination of independent variables.

--Put grouped data into a temporary table #x
SELECT *
INTO #x
FROM
(
    VALUES
        (100, 1, 10, 28, 156),
        (150, 1, 10, 33, 144),
        (200, 1, 10, 44, 171),
        (250, 1, 10, 56, 196),
        (300, 1, 10, 55, 158),
        (350, 1, 10, 44, 100),
        (400, 1, 10, 57, 126),
        (450, 1, 10, 77, 166),
        (500, 1, 10, 84, 166),
        (100, 2, 10, 23, 153),
        (150, 2, 10, 31, 165),
        (200, 2, 10, 40, 179),
        (250, 2, 10, 42, 152),
        (300, 2, 10, 55, 181),
        (350, 2, 10, 68, 200),
        (400, 2, 10, 59, 148),
        (450, 2, 10, 69, 156),
        (500, 2, 10, 75, 157),
        (100, 1, 11, 19, 164),
        (150, 1, 11, 23, 147),
        (200, 1, 11, 35, 182),
        (250, 1, 11, 46, 196),
        (300, 1, 11, 41, 143),
        (350, 1, 11, 60, 189),
        (400, 1, 11, 59, 162),
        (450, 1, 11, 75, 187),
        (500, 1, 11, 59, 129),
        (100, 2, 11, 9, 105),
        (150, 2, 11, 22, 179),
        (200, 2, 11, 30, 182),
        (250, 2, 11, 32, 155),
        (300, 2, 11, 41, 164),
        (350, 2, 11, 58, 200),
        (400, 2, 11, 60, 181),
        (450, 2, 11, 75, 199),
        (500, 2, 11, 59, 141)
) n (x1, x2, x3, success, N);
--Run LOGIT and store the results in #mylogit
SELECT *
INTO #mylogit
FROM wct.LOGITSUM('SELECT x1,x2,x3,success,n-success from #x', 4, 5);

--Calculate the predicted probabilities using LOGITPROB for each row in #x and store the
--predicted probability and the group totals in #t
SELECT wct.LOGITPROB(n.x, m.stat_val) as [p predicted],
       n - Success as failure,
       success as success
INTO #t
FROM #x
    CROSS APPLY
(
    VALUES
        (0, 1),
        (1, x1),
        (2, x2),
        (3, x3)
) n (idx, x)
    INNER JOIN #mylogit m
        ON m.idx = n.idx
WHERE m.stat_name = 'b'
GROUP BY n - Success,
         success;
--Run ROCTable function
SELECT *
FROM wct.ROCTable('SELECT * FROM #t');

This produces the following result.

You can see from the table that the cumulative AUROC value is 0.639751323614436. This is the same as the value returned by LOGITSUM .

--Get the AUROC value from #mylogit
SELECT stat_val
FROM #mylogit
WHERE stat_name = 'AUROC';

This produces the following result.

We can modify our SQL slightly from the previous example in order to verify the calculation of the area under the ROC curve using the Cartesian product.

--Calculate the area under the ROC Curve using a Cartesian product
SELECT SUM(n.y0 * n.y1) / cast(p.pairs as float) as AUROC,
       p.pairs
FROM
(
    SELECT t1.success as y1,
           t1.[p predicted] as p1,
           t2.failure as y0,
           t2.[p predicted] as p0
    FROM #t t1,
         #t t2
) n ,
(SELECT SUM(success) * SUM(failure)FROM #t) p(pairs)
WHERE p1 > p0
GROUP BY p.pairs;

This produces the following result.

See Also

LINEST - the Ordinary Least Squares (OLS) solution for a series of x-values and y-values

LOGEST - Logarithmic regression

LOGIT - Logit regression

LOGITPRED - Calculate predicted values based on a logit regression

LOGITPROB - Calculate the probability that Y = 1 given a set of coefficients from a logistic regression

LOGITSUM - Logit regression using summary data

VIF - Variance inflation factors