sql计算AUC

AUC可以直接根据ROC曲线，利用梯形积分进行计算。此外，还有一个比较有意思的是，可以利用AUC与Wilcoxon-Mann-Whitney检验的U统计量的关系，来计算AUC。这可以从AUC的概率意义推导而来。

假设我们将测试集的正负样本按照模型预测得分从小到大排序，对于第j个正样本，假设它的排序为$r_j$，那么说明排在这个正样本前面的总样本有$r_j-1$个，其中正样本有$j-1$个（因为这$j$个正样本在所有的正样本里面排第$j$），所以排在第$j$个正样本前面（得分比它小）的负样本个数为$r_j-j$。也就是说，对于第$j$个正样本来说，其得分比随机挑的一个负样本（排在它后面的概率）是：

$$\frac{r_j-j}{N_{-}}$$

其中$N_{-}$是总的负样本数目。所以，平均下来，随机挑的正样本得分比负样本大的概率为：

$$\frac{1}{N_{+}}\sum _{j=1}^{N_{+}}\frac{r_j-j}{N_{-}}=\frac{{\sum }_{j=1}^{N_{+}}{r}_j-N_{+}(N_{+}+1)/2}{N_{+}{N}_{-}}$$

所以：

$$AUC=\frac{{\sum }_{j=1}^{N_{+}}{r}_j-N_{+}(N_{+}+1)/2}{N_{+}{N}_{-}}$$

因此，很容易写出计算AUC的SQL代码：

select
	(ry - 0.5*n1*(n1+1))/n0/n1 as auc
from(
	select
		sum(if(y=0, 1, 0)) as n0,
		sum(if(y=1, 1, 0)) as n1,
		sum(if(y=1, r, 0)) as ry
	from(
		select y, row_number() over(order by score asc) as r
		from(
			select y, score
			from some.table
		)A
	)B
)C