英文:
How to count classification_report ( precision recall f1-score support) in sql
问题
我可以帮你编写SQL查询来生成类似classification_report的结果。以下是一个可能的查询示例：
SELECT
'False' AS class,
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,
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,
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,
SUM(CASE WHEN real_value = 'false' THEN 1 ELSE 0 END) AS support
FROM your_table_name
UNION ALL
SELECT
'True' AS class,
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,
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,
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,
SUM(CASE WHEN real_value = 'true' THEN 1 ELSE 0 END) AS support
FROM your_table_name;
请将代码中的"your_table_name"替换为您的表格名称。这个查询将生成与classification_report类似的结果，包括精确度、召回率、F1分数和支持度，分别针对"False"和"True"两个类别。
英文:
I have table with classification result of binary classification.
something like
id, predict_value, real_value
1, true, true
2, true, false
...
I want to get with sql classification_report, something like
class precision recall f1-score support
False 0.97 1.00 0.98 57241
True 0.68 0.22 0.33 2323
accuracy NULL NULL 0.97 59564
Can you help me with sql query ?
答案1
得分: 2
以下可能会证明有用。
WITH classes AS (SELECT my_data.predict_value AS class
FROM my_data
UNION
SELECT my_data.real_value AS class
FROM my_data),
t AS (SELECT c.class,
COUNT(*) FILTER (WHERE c.class = d.predict_value) AS 支持,
(COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value = d.real_value))::float AS tp,
(COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value <> d.real_value))::float AS fp,
(COUNT(*) FILTER (WHERE c.class <> d.predict_value AND c.class <> d.real_value))::float AS tn,
(COUNT(*) FILTER (WHERE c.class <> d.predict_value AND c.class = d.real_value))::float AS fn
FROM classes c
CROSS JOIN my_data d
GROUP BY c.class),
pr AS (SELECT t.class::text AS 类别,
CASE t.支持 WHEN 0 THEN NULL ELSE t.tp / t.支持 END AS 精确度,
CASE t.支持 WHEN 0 THEN NULL ELSE tp / (t.tp + t.fn) END AS 召回率,
t.支持
FROM t
UNION ALL
SELECT '准确度' AS 类别,
SUM(t.tp) / SUM(t.支持) AS 精确度,
SUM(t.tp) / (SUM(t.tp) + SUM(t.fn)) AS 召回率,
SUM(t.支持) AS 支持
FROM t)
SELECT pr.类别,
pr.精确度,
pr.召回率,
2 * pr.精确度 * pr.召回率 / (pr.精确度 + pr.召回率) AS "f1-score",
pr.支持
FROM pr
ORDER BY pr.类别;
英文:
The following might prove useful.
WITH classes AS (SELECT my_data.predict_value AS class
FROM my_data
UNION
SELECT my_data.real_value AS class
FROM my_data),
t AS (SELECT c.class,
COUNT(*) FILTER (WHERE c.class = d.predict_value) AS SUPPORT,
(COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value = d.real_value))::float AS tp,
(COUNT(*) FILTER (WHERE c.class = d.predict_value AND d.predict_value <> d.real_value))::float AS fp,
(COUNT(*) FILTER (WHERE c.class <> d.predict_value AND c.class <> d.real_value))::float AS tn,
(COUNT(*) FILTER (WHERE c.class <> d.predict_value AND c.class = d.real_value))::float AS fn
FROM classes c
CROSS JOIN my_data d
GROUP BY c.class),
pr AS (SELECT t.class::text AS class,
CASE t.support WHEN 0 THEN NULL ELSE t.tp / t.support END AS precision,
CASE t.support WHEN 0 THEN NULL ELSE tp / (t.tp + t.fn) END AS recall,
t.support
FROM t
UNION ALL
SELECT 'Accuracy' AS class,
SUM(t.tp) / SUM(t.support) AS precision,
SUM(t.tp) / (SUM(t.tp) + SUM(t.fn)) AS recall,
SUM(t.support) AS support
FROM t)
SELECT pr.class,
pr.precision,
pr.recall,
2 * pr.precision * pr.recall / (pr.precision + pr.recall) AS "f1-score",
pr.support
FROM pr
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
with _tmp as (select 'accuracy' as class,
null as precision,
null as recall,
count(*) as support
from my_data
UNION
select 'True' as class,
(count(*) filter ( where predict_value = True and real_value = True ))::float
/
(count(*) filter ( where predict_value = True))::numeric(12, 6) as precision,
(count(*) filter ( where predict_value = True and real_value = True ))::float
/
(
count(*) filter ( where predict_value = FALSE and real_value = True)
+
count(*) filter ( where predict_value = True and real_value = True )
)::float as recall,
null as f1_score,
count(*) filter ( where predict_value = True) as support
from my_data
Union
select 'False' as class,
(count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
/
(count(*) filter ( where predict_value = FALSE))::numeric(12, 6) as precision,
(count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
/
(
count(*) filter ( where predict_value = True and real_value = FALSE)
+
count(*) filter ( where predict_value = FALSE and real_value = FALSE )
)::float as recall,
null as f1_score,
count(*) filter ( where fixed_predict = FALSE) as support
from my_data)
select "class",
precision::numeric(12, 3),
recall::numeric(12, 3),
(2 * precision * recall / (precision + recall))::numeric(12, 3) as f1_score,
support
from _tmp
order by support
英文:
I have next ugly sql as soltion
with _tmp as (select 'accuracy' as class,
null as pricission,
null as recall,
count(*) as support
from my_data
UNION
select 'True' as class,
(count(*) filter ( where predict_value = True and real_value = True ))::float
/
(count(*) filter ( where predict_value = True))::numeric(12, 6) as pricission,
(count(*) filter ( where predict_value = True and real_value = True ))::float
/
(
count(*) filter ( where predict_value = FALSE and real_value = True)
+
count(*) filter ( where predict_value = True and real_value = True )
)::float as recall,
null as f1_score,
count(*) filter ( where predict_value = True) as support
from my_data
Union
select 'False' as class,
(count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
/
(count(*) filter ( where predict_value = FALSE))::numeric(12, 6) as pricission,
(count(*) filter ( where predict_value = FALSE and real_value = FALSE ))::float
/
(
count(*) filter ( where predict_value = True and real_value = FALSE)
+
count(*) filter ( where predict_value = FALSE and real_value = FALSE )
)::float as recall,
null as f1_score,
count(*) filter ( where fixed_predict = FALSE) as support
from my_data)
select "class",
pricission::numeric(12, 3),
recall::numeric(12, 3),
(2 * pricission * recall / (pricission + recall))::numeric(12, 3) as f1_score,
support
from _tmp
order by support
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论