Blog: sql相关代码

SELECT tp, fp, tn, fn, recall, precision, 2*precision*recall / (precision + recall) as f1
FROM 
(
    SELECT tp, fp, tn, fn, tp*1.0/(tp+fn) as recall, tp*1.0/(tp+fp) as precision
    FROM 
    (
        SELECT sum(tp) as tp, sum(fp) as fp, sum(tn) as tn, sum(fn) as fn
        FROM 
        (
            SELECT CAST(SPLIT_PART(flag, ',', 1) AS BIGINT) AS tp, CAST(SPLIT_PART(flag, ',', 2) AS BIGINT) AS fp, 
                   CAST(SPLIT_PART(flag, ',', 3) AS BIGINT) AS tn, CAST(SPLIT_PART(flag, ',', 4) AS BIGINT) AS fn
            FROM 
            (
                SELECT 
                    CASE ---- TP FP TN FN
                        WHEN label_predict = 1 AND label = 1 THEN '1,0,0,0' --tp
                        WHEN label_predict = 1 AND label = 0 THEN '0,1,0,0' --fp
                        WHEN label_predict = 0 AND label = 0 THEN '0,0,1,0' --tn
                        WHEN label_predict = 0 AND label = 1 THEN '0,0,0,1' --fn
                    END AS flag 
                FROM 
                (
                    SELECT label, CASE WHEN score >= 0.5 THEN 1 ELSE 0 END AS label_predict 
                    FROM du_reader_sentence_classify_validation_output_ori
                    WHERE ds = '${bizdate}'
                ) A 
            ) B 
        ) C 
    ) D 
) E 

SELECT D.threshold, D.avg_confidence_rouge, D.number, E.all_number, D.number * 1.0/E.all_number as ratio
FROM 
(
    SELECT "1" AS key, threshold, AVG(confidence_rouge) as avg_confidence_rouge, COUNT(1) as number 
    FROM 
    ( 
        SELECT threshold, answer_score, confidence_rouge
        FROM 
        (
            SELECT "1" as key, answer_score, confidence_rouge FROM search_du_reader_mrc_predict_result
        ) A 
        JOIN 
        (
            SELECT key, CAST(threshold AS DOUBLE) AS threshold
            FROM 
            ( 
                SELECT TRANS_ARRAY(1, ',', key, threshold) AS (key, threshold)
                FROM 
                ( 
                    SELECT "1" as key, '0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1' as threshold FROM dual
                ) B1
            ) B2
        ) B ON A.key = B.key
        WHERE answer_score >= threshold
    ) C 
    GROUP BY threshold
) D 
JOIN 
(
    SELECT "1" as key, COUNT(1) as all_number  FROM search_du_reader_mrc_predict_result
) E ON D.key = E.key
DISTRIBUTE BY "1" SORT BY threshold
;