在SQL中计算查准率/召回曲线

Question

假设我有分类决策和信任作为记录，有任何方法可以通过SQL获得精确/召回曲线吗？换句话说，是否有一种方法将以下一组查询折叠为一个查询？

select avg(predicted_label=correct_label) from t where confidence<0
select avg(predicted_label=correct_label) from t where confidence<1
select avg(predicted_label=correct_label) from t where confidence<2
select avg(predicted_label=correct_label) from t where confidence<3
select avg(predicted_label=correct_label) from t where confidence<4
select avg(predicted_label=correct_label) from t where confidence<5
select avg(predicted_label=correct_label) from t where confidence<6
....

Answer 1

要获得avg of predicted_label的信心范围，请尝试以下操作。

编辑：在<=上连接

SELECT FLOOR(t1.confidence), AVG(t2.predicted_label)
FROM Table t1
    JOIN Table t2 ON t2.confidence <= t1.Confidence
GROUP BY FLOOR(t1.confidence)

Answer 2

如果您有一个表(我们称它为具有单列numbers num)，其中包含从0到某个足够大的数字之间的相关数字，那么您可以简单地说：

select avg(t.predicted_label=t.correct_label) 
  from t join numbers on t.confidence < numbers.num
 where numbers.num < 100 -- or whatever makes sense
group by numbers.num