Hive SQL 计算AUC

前置条件：
    数据表中已有正负样本标签，和预测分值。

TRUE,    9
FALSE,  8
TRUE,   7
FALSE,  6
这四个样本对应的AUC是0.75。

-- ########## 测试一：##########

Lable为True / False.

create table tmp.test_score_label(score int,label boolean
);insert into tmp.test_score_label values (9, true),(8, false),(7, true),(6, false);select * from tmp.test_score_label;select(ry - 0.5*n1*(n1+1))/n0/n1 as auc
from(selectsum(if(y=false, 1, 0)) as n0,sum(if(y=true, 1, 0)) as n1,sum(if(y=true, r, 0)) as ryfrom(select y, row_number() over(order by score asc) as rfrom(select label y, scorefrom tmp.test_score_label)A)B
)C
;

-- ########## 测试二：##########

Lable为 0 / 1.

create table tmp.test_score_label2(score int,label TINYINT
);insert into tmp.test_score_label2 values (9, 1),(8, 0),(7, 1),(6, 0);select * from tmp.test_score_label2;select(ry - 0.5*n1*(n1+1))/n0/n1 as auc
from(selectsum(if(y=0, 1, 0)) as n0,sum(if(y=1, 1, 0)) as n1,sum(if(y=1, r, 0)) as ryfrom(select y, row_number() over(order by score asc) as rfrom(select label y, scorefrom tmp.test_score_label2)A)B
)C
;

代码参考:

sql 计算 auc https://tracholar.github.io/

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！