mysql - 优化百分比计算SQL查询

Question

我有一个这样的表:

------------------
sl no | Error type
------------------
1     | err1
------------------
2     | err2
------------------
3     | err1
------------------
4     | err2
------------------
5     | err2
------------------
6     | err3
------------------

我想要的结果:

---------------------------
Error type | Count | %
---------------------------
err1       | 2     | 33.33
---------------------------
err2       | 3     | 50
---------------------------
err3       | 1     | 16.66
---------------------------

我正在使用以下查询来获得上述结果:

select 
    error as Error,
    count(*) as Count, 
    100* count(*)/(select count(*) from logs) as Percentage 
from logs 
group by error;

是否针对任务进行了优化？如果没有，我可以通过哪些其他有效方式获取该信息？

Answer 1

此查询将对每行执行 count(*) 次。我会查询一次，然后将其交叉连接到各个计数:

SELECT     a.error, a.cnt, 100 * a.cnt / b.total_count AS percentage
FROM       (SELECT   error, COUNT(*) as cnt
            FROM     logs
            GROUP BY error) a
CROSS JOIN (SELECT COUNT(*) AS total_count FROM logs) b