在SQL中计算AUC

Question

如何在SQL中计算AUC的最佳方法？

以下是我得到的方法（假设表T（label，confid）且label=0,1）：

SELECT sum(cumneg * label) * 1e0 / (sum(label) * sum(1-label)) AS auc
FROM ( 
  SELECT label,
    sum(1-label) OVER(ORDER BY confid ROWS UNBOUNDED PRECEDING) (BIGINT) cumneg
  FROM T
) t;

在Teradata中，我必须使用1e0进行乘法运算以得到真实的结果。BigInt转换是必要的，以避免溢出。

- iggy

Answer 1

这里是我发现的一个略微不同且可能更简单的解决方案：

SELECT (sum(label*r) - 0.5*sum(label)*(sum(label)+1)) / (sum(label) * sum(1-label)) AS auc
FROM ( 
  SELECT label, row_number() OVER (ORDER BY confid) r
  FROM T
) t;

返回与问题中查询相同结果的函数。

更新

当存在多个具有相同预测（confid）但标签不同的示例时，该SQL查询（以及问题中的查询）是非确定性的。为了使用插值计算确定性AUC，可以将查询修改如下：

SELECT (sum(pos*r) - 0.5*sum(pos)*(sum(pos)+1) - 0.5*sum(pos*neg)) / 
  (sum(pos) * sum(neg)) AS auc
FROM ( 
  SELECT pos, neg, 
    sum(pos+neg) OVER (ORDER BY confid ROWS UNBOUNDED PRECEDING) r
  FROM (
    SELECT confid, sum(label) AS pos, sum(1-label) AS neg
    FROM T
    GROUP BY confid) t
) t;

在AUC公式中，分母是总对数（正样本与负样本的组合数）。分子计算有多少对被正确排序。 sum(pos*r) 计算到目前为止总对数（基于置信度排序）。该数字包括正样本 X 正样本的对数，因此第二项减去这些对数。最后一项减去预测相同的一半正样本 X 负样本对数。

- iggy

---

请您可以分享一下底层的数学解释吗？ - Saurav

看看这个能否帮到你：https://stephanosterburg.gitbook.io/scrapbook/data-science/ds-cheatsheets/machine-learning/fast-computation-of-auc-roc-score - iggy

Answer 2

以下伪SQL利用了AUC ROC与预测分数区分随机正面和负面标签的概率相同的事实。SQL假设两个标签都至少有10000个元素。计算出的AUC不是精确的，而是随机的。也请参阅R中相同问题的答案。

WITH POSITIVE_SCORES AS (
  select
    score as p_pos
  from
    TABLE
    where label = positive
    order by rand()
    limit 10000
),

NEGATIVE_SCORES AS (
  select
    score as p_neg
  from
    TABLE
    where label = negative
    order by rand()
    limit 10000
)

select
  avg(case 
    when p_pos > p_neg then 1 
    when p_pos = p_neg then 0.5 
    else 0 
  end) as auc
from
  POSITIVE_SCORES
  cross join
  NEGATIVE_SCORES

- Jussi Kujala

---

嗯，采样有什么优势，特别是如果需要通过rand()进行完全排序？ - iggy

抽样后进行了交叉连接。如果没有进行抽样，交叉连接可能会激增。 - Jussi Kujala

Answer 3

为了计算精确的确定性AUC分数，我们应该按“confid”聚合以处理不是所有置信度值都唯一的情况。然后，我们只需为每个唯一的置信度值计算梯形面积并将所有值相加。此外，还要检查所有标签都为零或一的情况。请注意，由于乘法可能会导致类型溢出-您可以使用BIGINT来防止它。

MS SQL实现：

select
    IIF(SUM(Ones) * SUM(Zeros) <> 0,
    SUM(IIF(Zeros * Ones > 0, 0.5 * Zeros * Ones + Height * Ones, Height * Ones)) / (SUM(Ones) * SUM(Zeros)), 0)
from (
        select
        Zeros,
        Ones,
        SUM(IIF(Zeros * Ones > 0, 0, Zeros) + IIF(PrevZeros * PrevOnes > 0, PrevZeros, 0)) OVER (ORDER BY PD) as Height
    from (
        select
            confid as PD,
            SUM(label) as Ones,
            SUM(ABS(1 - label)) as Zeros,
            LAG(SUM(label), 1, NULL) OVER (ORDER BY confid) as PrevOnes,
            LAG(SUM(ABS(1 - label)), 1, NULL) OVER (ORDER BY confid) as PrevZeros
        from T
        group by confid
    ) q1
) q2;

- okrn