mysql - 如何按列值的计数计算百分比

Question

给定这样的表格。

+----+-------+-----+
| id | class | tag |
+----+-------+-----+
|  1 |     1 | aaa |
|  2 |     1 | abb |
|  3 |     1 | acc |
|  4 |     1 | baa |
|  5 |     2 | bbb |
|  6 |     2 | aaa |
+----+-------+-----+

如何计算表中每个类在同一类中以 a 开头的标签数？上表的结果将是:

+-------+---------+
| class | percent |
+-------+---------+
|     1 |  0.7500 |
|     2 |  0.5000 |
+-------+---------+

PS:我已经有解决方案了，但是表很大(几百万行)，你能优化一下吗？

SELECT class, (CAST(cnt_1 AS DECIMAL) / cnt_2) AS percent 
FROM (
  SELECT class, count(tag) AS cnt_1 FROM (
    SELECT class, tag FROM t_test 
    WHERE tag LIKE 'a%'
  ) AS tmp1 
  GROUP BY class
) AS tmp2 
NATURAL JOIN (
  SELECT class, count(tag) AS cnt_2 
  FROM t_test GROUP BY class
) AS tmp3;

Answer 1

我会使用条件聚合:

select class, avg( tag like 'a%' ) as a_ratio
from t_test
group by class;

百分比通常从 0 到 100。ratio 是您的列的更好名称。

Here是一个 db<>fiddle 说明这是可行的。