使用sql快速计算AUC&gAUC

原理我就不细说了，通过一次排序就可以计算出auc

整体计算

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

分场景计算

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

后面有空再补上gauc