Curve di ROC tramite query sql

Di seguito è illustrata una procedura sql per la generazione delle curve di ROC in rialto, con la possibilità di ottenere informazioni e statistiche per ogni punto della curva.

Si supponga di avere una tabella Tab composta nel seguente modo: Tab(realClass, prediction). Si noti che ‘realClass’ può assumere due soli valori ‘yes’ e ‘no’, mentre prediction è la probabilità predetta, dal modello, che la tupla abbia classe target, ad esempio ‘yes’.

Per prima cosa serve una tabella con solo le soglie di predizione, che chiameremo Threshold_Tab(threshold):

SELECT 0.5 AS threshold UNION SELECT DISTINCT prediction AS threshold FROM Tab

Per ogni soglia bisogna calcolare la relativa matrice di confusione. Tutte le matrici di confusione verranno memorizzate in una nuova tabella, chiamata CONFUSION_MATRICES(tp, fn, fp, tn, threshold):

SELECT TP.tp, FN.fn, FP.fp, TN.tn, threshold

FROM

(SELECT COUNT(*) AS tp, threshold

FROM Tab, Threshold_Tab

WHERE Tab.prediction >= threshold AND Tab.realClass = ‘yes’

GROUP BY threshold) AS TP

LEFT OUTER JOIN

(SELECT COUNT(*) AS fn, threshold

FROM Tab, Threshold_Tab

WHERE Tab.prediction < threshold AND Tab.realClass = ‘yes’

GROUP BY threshold) AS FN

ON TP.threshold = FN.threshold LEFT OUTER JOIN

(SELECT COUNT(*) AS fp, threshold

FROM Tab, Threshold_Tab

WHERE Tab.prediction => threshold AND Tab.realClass <> ‘yes’

GROUP BY threshold) AS FP

ON TP.threshold = FP.threshold LEFT OUTER JOIN

(SELECT COUNT(*) AS tn, threshold

FROM Tab, Threshold_Tab

WHERE Tab.prediction < threshold AND Tab.realClass <> ‘yes’

GROUP BY threshold) AS TN

ON TP.threshold = TN.threshold

Per risolvere i casi estremi ed eventuali errori in CONFUSION_MATRICES effettuiamo una query di pulizia che crei la tabella CLEANED_CONFUSION_MATRICES(tp, fn, fp, tn, threshold):

SELECT

CASE

WHEN tp IS NULL THEN 0

ELSE tp

END AS tp,

CASE

WHEN fn IS NULL THEN 0

ELSE fn

END AS fn,

CASE

WHEN fp IS NULL THEN 0

ELSE fp

END AS fp,

CASE

WHEN tn IS NULL THEN 0

ELSE tn

END AS tn,

threshold

FROM CONFUSION_MATRICES

Infine generiamo la tabella che conterrà la curva di ROC (alla quale si aggiunge il punto in posizione [0, 0]): ROC(fpr, tpr, precision, recall, accuracy, cost, threshold). Per il calcolo del costo supponiamo di disporre di una matrice (con una sola tupla): COST(tpCost, fnCost, fpCost, tnCost).

ROC(fpr, tpr, precision, recall, accuracy, cost, threshold):

SELECT

0 AS fpr, 0 AS tpr, 0 as precision, 0 as recall, 0 as accuracy, 99999999 as cost, 0 as threshold

UNION

SELECT

CASE

WHEN (fp + tn) > 0 THEN fp / (fp + tn)

ELSE 0

END AS fpr,

CASE

WHEN (tp + fn) > 0 THEN tp / (tp + fn)

ELSE 0

END AS tpr,

CASE

WHEN (tp + fp) > 0 THEN tp / (tp + fp)

ELSE 0

END AS precision,

CASE

WHEN (fp + tn) > 0 THEN fp / (fp + tn)

ELSE 0

END AS recall,

(tp + tn) / (tp + fn + fp + tn) AS accuracy,

tpCost * tp + fnCost * fn + fpCost * fp + tnCost * tn AS cost,

threshold

FROM CLEANED_CONFUSION_MATRICES, COST

Come si può notare, la tabella ROC è la forma esplicita della curva di roc del modello target, e contiene tutte le informazioni necessarie per una buona analisi comparativa.

A questo punto per visualizzare la curva di roc è sufficiente creare uno scatter plot sui dati della tabella ROC, in particolare l’asse delle ascisse (x) conterrà i valori del campo FPR, mentre le ordinate (y) conterranno i valori del campo TPR

Lascia un Commento