auc - 在 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 强制转换对于避免溢出是必要的。

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;

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

更新

当有多个具有相同预测(可信)但标签不同的示例时，此 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公式中，分母是对的总数(正X负)。分子计算有多少对被正确排序。 sum(pos*r) 计算到目前为止的对总数(基于置信度顺序)。该数字包括正 X 正对，因此第二项减去这些。最后，最后一项减去具有相同预测的正 X 负对的一半。