如何在SQL中计算classification_report（精确度、召回率、F1分数和支持）？

huangapple
go评论96阅读模式
英文:

How to count classification_report ( precision recall f1-score support) in sql

问题

我可以帮你编写SQL查询来生成类似classification_report的结果。以下是一个可能的查询示例：

  1. SELECT
  2.     'False' AS class,
  3.     SUM(CASE WHEN real_value = 'true' AND predict_value = 'false' THEN 1 ELSE 0 END) / SUM(CASE WHEN real_value = 'false' THEN 1 ELSE 0 END) AS precision,
  4.     SUM(CASE WHEN real_value = 'true' AND predict_value = 'false' THEN 1 ELSE 0 END) / SUM(CASE WHEN predict_value = 'true' THEN 1 ELSE 0 END) AS recall,
  5.     2 * (SUM(CASE WHEN real_value = 'true' AND predict_value = 'false' THEN 1 ELSE 0 END) / SUM(CASE WHEN real_value = 'false' THEN 1 ELSE 0 END) * SUM(CASE WHEN real_value = 'true' AND predict_value = 'false' THEN 1 ELSE 0 END) / SUM(CASE WHEN predict_value = 'true' THEN 1 ELSE 0 END)) / (SUM(CASE WHEN real_value = 'true' AND predict_value = 'false' THEN 1 ELSE 0 END) / SUM(CASE WHEN real_value = 'false' THEN 1 ELSE 0 END) + SUM(CASE WHEN real_value = 'true' AND predict_value = 'false' THEN 1 ELSE 0 END) / SUM(CASE WHEN predict_value = 'true' THEN 1 ELSE 0 END)) AS f1_score,
  6.     SUM(CASE WHEN real_value = 'false' THEN 1 ELSE 0 END) AS support
  7. FROM your_table_name
  8. UNION ALL
  9. SELECT
  10.     'True' AS class,
  11.     SUM(CASE WHEN real_value = 'true' AND predict_value = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN real_value = 'true' THEN 1 ELSE 0 END) AS precision,
  12.     SUM(CASE WHEN real_value = 'true' AND predict_value = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN predict_value = 'true' THEN 1 ELSE 0 END) AS recall,
  13.     2 * (SUM(CASE WHEN real_value = 'true' AND predict_value = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN real_value = 'true' THEN 1 ELSE 0 END) * SUM(CASE WHEN real_value = 'true' AND predict_value = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN predict_value = 'true' THEN 1 ELSE 0 END)) / (SUM(CASE WHEN real_value = 'true' AND predict_value = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN real_value = 'true' THEN 1 ELSE 0 END) + SUM(CASE WHEN real_value = 'true' AND predict_value = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN predict_value = 'true' THEN 1 ELSE 0 END)) AS f1_score,
  14.     SUM(CASE WHEN real_value = 'true' THEN 1 ELSE 0 END) AS support
  15. FROM your_table_name;

请将代码中的"your_table_name"替换为您的表格名称。这个查询将生成与classification_report类似的结果，包括精确度、召回率、F1分数和支持度，分别针对"False"和"True"两个类别。

英文:

I have table with classification result of binary classification.

something like

  1. id, predict_value, real_value
  2. 1,  true, true
  3. 2,  true, false
  4. ...

I want to get with sql classification_report, something like

  1.    class   precision    recall  f1-score   support
  2. False       0.97      1.00      0.98     57241
  3. True       0.68      0.22      0.33      2323
  4. accuracy       NULL      NULL      0.97     59564

Can you help me with sql query ?

答案1

得分: 2

以下可能会证明有用。

  1. WITH classes AS (SELECT my_data.predict_value AS class
  2.                    FROM my_data
  3.                  UNION
  4.                  SELECT my_data.real_value AS class
  5.                    FROM my_data),
  6.      t AS (SELECT c.class,
  7.                   COUNT(*) FILTER (WHERE c.class = d.predict_value)                                              AS 支持,
  8.                   (COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value = d.real_value))::float  AS tp,
  9.                   (COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value <> d.real_value))::float AS fp,
  10.                   (COUNT(*) FILTER (WHERE c.class <> d.predict_value AND c.class <> d.real_value))::float        AS tn,
  11.                   (COUNT(*) FILTER (WHERE c.class <> d.predict_value AND c.class = d.real_value))::float         AS fn
  12.              FROM classes c
  13.                CROSS JOIN my_data d
  14.              GROUP BY c.class),
  15.      pr AS (SELECT t.class::text                                               AS 类别,
  16.                    CASE t.支持 WHEN 0 THEN NULL ELSE t.tp / t.支持 END   AS 精确度,
  17.                    CASE t.支持 WHEN 0 THEN NULL ELSE tp / (t.tp + t.fn) END AS 召回率,
  18.                    t.支持
  19.               FROM t
  20.             UNION ALL
  21.             SELECT '准确度'                          AS 类别,
  22.                    SUM(t.tp) / SUM(t.支持)          AS 精确度,
  23.                    SUM(t.tp) / (SUM(t.tp) + SUM(t.fn)) AS 召回率,
  24.                    SUM(t.支持)                      AS 支持
  25.               FROM t)
  26. SELECT pr.类别,
  27.        pr.精确度,
  28.        pr.召回率,
  29.        2 * pr.精确度 * pr.召回率 / (pr.精确度 + pr.召回率) AS "f1-score",
  30.        pr.支持
  31.   FROM pr
  32.   ORDER BY pr.类别;
英文:

The following might prove useful.

  1. WITH classes AS (SELECT my_data.predict_value AS class
  2. FROM my_data
  3. UNION
  4. SELECT my_data.real_value AS class
  5. FROM my_data),
  6. t AS (SELECT c.class,
  7. COUNT(*) FILTER (WHERE c.class = d.predict_value)                                              AS SUPPORT,
  8. (COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value = d.real_value))::float  AS tp,
  9. (COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value &lt;&gt; d.real_value))::float AS fp,
  10. (COUNT(*) FILTER (WHERE c.class &lt;&gt; d.predict_value AND c.class &lt;&gt; d.real_value))::float        AS tn,
  11. (COUNT(*) FILTER (WHERE c.class &lt;&gt; d.predict_value AND c.class = d.real_value))::float         AS fn
  12. FROM classes c
  13. CROSS JOIN my_data d
  14. GROUP BY c.class),
  15. pr AS (SELECT t.class::text                                               AS class,
  16. CASE t.support WHEN 0 THEN NULL ELSE t.tp / t.support END   AS precision,
  17. CASE t.support WHEN 0 THEN NULL ELSE tp / (t.tp + t.fn) END AS recall,
  18. t.support
  19. FROM t
  20. UNION ALL
  21. SELECT &#39;Accuracy&#39;                          AS class,
  22. SUM(t.tp) / SUM(t.support)          AS precision,
  23. SUM(t.tp) / (SUM(t.tp) + SUM(t.fn)) AS recall,
  24. SUM(t.support)                      AS support
  25. FROM t)
  26. SELECT pr.class,
  27. pr.precision,
  28. pr.recall,
  29. 2 * pr.precision * pr.recall / (pr.precision + pr.recall) AS &quot;f1-score&quot;,
  30. pr.support
  31. FROM pr
  32. ORDER BY pr.class;

This query works for any positive number of classes, not just binary classification.

答案2

得分: 1

I have next ugly sql as solution

  1. with _tmp as (select 'accuracy' as class,
  2.                      null       as precision,
  3.                      null       as recall,
  4.                      count(*)   as support
  5.               from my_data
  6.               UNION
  7.               select 'True'                                                          as class,
  8.                      (count(*) filter ( where predict_value = True and real_value = True ))::float
  9.                          /
  10.                      (count(*) filter ( where predict_value = True))::numeric(12, 6) as precision,
  11.                      (count(*) filter ( where predict_value = True and real_value = True ))::float
  12.                          /
  13.                      (
  14.                                      count(*) filter ( where predict_value = FALSE and real_value = True)
  15.                              +
  16.                                      count(*) filter ( where predict_value = True and real_value = True )
  17.                          )::float                                                    as recall,
  18.                      null                                                            as f1_score,
  19.                      count(*) filter ( where predict_value = True)                   as support
  20.               from my_data
  21.               Union
  22.               select 'False'                                                          as class,
  23.                      (count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
  24.                          /
  25.                      (count(*) filter ( where predict_value = FALSE))::numeric(12, 6) as precision,
  26.                      (count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
  27.                          /
  28.                      (
  29.                                      count(*) filter ( where predict_value = True and real_value = FALSE)
  30.                              +
  31.                                      count(*) filter ( where predict_value = FALSE and real_value = FALSE )
  32.                          )::float                                                     as recall,
  33.                      null                                                             as f1_score,
  34.                      count(*) filter ( where fixed_predict = FALSE)                   as support
  35.               from my_data)
  36. select "class",
  37.        precision::numeric(12, 3),
  38.        recall::numeric(12, 3),
  39.        (2 * precision * recall / (precision + recall))::numeric(12, 3) as f1_score,
  40.        support
  41. from _tmp
  42. order by support
英文:

I have next ugly sql as soltion

  1. with _tmp as (select &#39;accuracy&#39; as class,
  2. null       as pricission,
  3. null       as recall,
  4. count(*)   as support
  5. from my_data
  6. UNION
  7. select &#39;True&#39;                                                          as class,
  8. (count(*) filter ( where predict_value = True and real_value = True ))::float
  9. /
  10. (count(*) filter ( where predict_value = True))::numeric(12, 6) as pricission,
  11. (count(*) filter ( where predict_value = True and real_value = True ))::float
  12. /
  13. (
  14. count(*) filter ( where predict_value = FALSE and real_value = True)
  15. +
  16. count(*) filter ( where predict_value = True and real_value = True )
  17. )::float                                                    as recall,
  18. null                                                            as f1_score,
  19. count(*) filter ( where predict_value = True)                   as support
  20. from my_data
  21. Union
  22. select &#39;False&#39;                                                          as class,
  23. (count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
  24. /
  25. (count(*) filter ( where predict_value = FALSE))::numeric(12, 6) as pricission,
  26. (count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
  27. /
  28. (
  29. count(*) filter ( where predict_value = True and real_value = FALSE)
  30. +
  31. count(*) filter ( where predict_value = FALSE and real_value = FALSE )
  32. )::float                                                     as recall,
  33. null                                                             as f1_score,
  34. count(*) filter ( where fixed_predict = FALSE)                   as support
  35. from my_data)
  36. select &quot;class&quot;,
  37. pricission::numeric(12, 3),
  38. recall::numeric(12, 3),
  39. (2 * pricission * recall / (pricission + recall))::numeric(12, 3) as f1_score,
  40. support
  41. from _tmp
  42. order by support

huangapple
  • 本文由 发表于 2023年5月14日 05:14:05
  • 转载请务必保留本文链接：https://go.coder-hub.com/76244904.html
  • classification
  • machine-learning
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定