1
\$\begingroup\$

I have a table containing two versions of results for some predictors, one by human labeling and the other by machine predicting, and I want to calculate some metrics.

CREATE TABLE log_table (
  `id` VARCHAR(5),
  `version` VARCHAR(15),
  `result` FlOAT
);

INSERT INTO log_table
  (`id`, `version`, `result`)
VALUES
  ('001', '2020-12-10', 0.8),
  ('002', '2020-12-10', 0.1),
  ('003', '2020-12-10', 0.2),
  ('004', '2020-12-10', 0.9),
  ('001', 'ground_truth', 1),
  ('002', 'ground_truth', 0),
  ('003', 'ground_truth', 1),
  ('004', 'ground_truth', 0),
  ('005', 'ground_truth', 1);

Here I tried to work out the recall and precision.

SELECT true_positive / ( true_positive + false_negative )  AS recall,
       true_positive / ( true_positive + false_positive ) AS _precision
FROM   (SELECT (SELECT Count(*) AS true_positive
                FROM   (SELECT id,
                               IF(result > 0.5, 1, 0) AS predict_result
                        FROM   log_table
                        WHERE  version = "2020-12-10") AS t_left
                       JOIN (SELECT id,
                                    result
                             FROM   log_table
                             WHERE  version = "ground_truth"
                                    AND result = 1) AS t_right
                         ON t_left.id = t_right.id
                            AND t_left.predict_result = t_right.result)  AS
                      true_positive,
               (SELECT Count(*) AS false_negative
                FROM   (SELECT id,
                               IF(result > 0.5, 1, 0) AS predict_result
                        FROM   log_table
                        WHERE  version = "2020-12-10") AS t_left
                       JOIN (SELECT id,
                                    result
                             FROM   log_table
                             WHERE  version = "ground_truth"
                                    AND result = 1) AS t_right
                         ON t_left.id = t_right.id
                            AND t_left.predict_result != t_right.result) AS
                      false_negative,
               (SELECT Count(*) AS false_positive
                FROM   (SELECT id,
                               IF(result > 0.5, 1, 0) AS predict_result
                        FROM   log_table
                        WHERE  version = "2020-12-10") AS t_left
                       JOIN (SELECT id,
                                    result
                             FROM   log_table
                             WHERE  version = "ground_truth"
                                    AND result = 0) AS t_right
                         ON t_left.id = t_right.id
                            AND t_left.predict_result != t_right.result) AS
                      false_positive,
               (SELECT Count(*) AS true_negative
                FROM   (SELECT id,
                               IF(result > 0.5, 1, 0) AS predict_result
                        FROM   log_table
                        WHERE  version = "2020-12-10") AS t_left
                       JOIN (SELECT id,
                                    result
                             FROM   log_table
                             WHERE  version = "ground_truth"
                                    AND result = 0) AS t_right
                         ON t_left.id = t_right.id
                            AND t_left.predict_result = t_right.result)  AS
                      true_negative) AS metrics; 

The code above seems not only complicated but also repetitive, then I wonder if it would be optimized and simplified? Here is its fiddle link.

\$\endgroup\$
2
\$\begingroup\$

Fundamentally you just need to rethink how your aggregation is done. The RDBMS you use - MySQL - is broken and does not support standard aggregates.

Applying some filters and replacing your conditional with a round makes this problem trivial if you're able to use PostgreSQL instead:

select cast(true_pos as real) / (true_pos + false_neg) as recall,
       cast(true_pos as real) / (true_pos + false_pos) as precision
from (
  select
      count(*) filter(where t_act.result=0 and t_exp.result=1) as false_neg,
   -- count(*) filter(where t_act.result=0 and t_exp.result=0) as true_neg,
      count(*) filter(where t_act.result=1 and t_exp.result=0) as false_pos,
      count(*) filter(where t_act.result=1 and t_exp.result=1) as true_pos
  from (
    select id, round(result) as result
    from log_table
    where version = '2020-12-10'
  ) as t_act
  join log_table as t_exp
    on t_act.id = t_exp.id and t_exp.version = 'ground_truth'
) as metrics;

sqlfiddle.com also supports this.

However, since you're stuck with MySQL you could use the case workaround proposed in that modern-sql site:

select true_pos / (true_pos + false_neg) as recall,
       true_pos / (true_pos + false_pos) as 'precision'
from (
  select
      count(case when t_act.result=0 and t_exp.result=1 then 1 end) as false_neg,
      count(case when t_act.result=1 and t_exp.result=0 then 1 end) as false_pos,
      count(case when t_act.result=1 and t_exp.result=1 then 1 end) as true_pos
  from (
    select id, round(result) as result
    from log_table
    where version = '2020-12-10'
  ) as t_act
  join log_table as t_exp
    on t_act.id = t_exp.id and t_exp.version = 'ground_truth'
) as metrics;
\$\endgroup\$
5
  • \$\begingroup\$ The code is neat, but it seems not to work in MySQL. \$\endgroup\$ Jul 2 '21 at 13:31
  • \$\begingroup\$ @Lnz right; which is why I mentioned that explicitly along with a workaround. Why are you using MySQL? \$\endgroup\$
    – Reinderien
    Jul 2 '21 at 13:33
  • \$\begingroup\$ We can only use MySQL in our company. :( \$\endgroup\$ Jul 2 '21 at 13:35
  • \$\begingroup\$ Facetiously: sounds like you need a new company. But anyway, the link above describes a workaround for MySQL. \$\endgroup\$
    – Reinderien
    Jul 2 '21 at 13:36
  • \$\begingroup\$ @Lnz done; and confirmed in db-fiddle.com \$\endgroup\$
    – Reinderien
    Jul 3 '21 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.