当查询具有GROUP BY时,如何获得总计的百分比? [英] How to get a percentage of total when the query has a GROUP BY?
问题描述
说我有一个非标准化的表,其中包含电影演员姓名及其所去过的电影.例如
Say I have a non-normalized table with movie actor names and the movies they've been in. eg.
CREATE TABLE movies_actors (
movies_actors_id INT,
movie VARCHAR(255),
actor VARCHAR(255),
PRIMARY KEY (movies_actors_id)
);
我执行SELECT actor, COUNT(1) FROM movies_actors GROUP BY actor
来查找演员参加过多少部电影.但是我也想知道演员参加过多少部电影.
I do a SELECT actor, COUNT(1) FROM movies_actors GROUP BY actor
to find out how many movies the actor has been in. But I also want to find out what percentage of movies that actor has been in.
我想我可以做到:
SELECT
actor,
COUNT(1) AS total,
COUNT(1) / (SELECT COUNT(1) FROM movies_actors) * 100 AS avg
FROM movies_actors
GROUP BY actor;
但是这似乎... idk ...令人讨厌.
But that just seems... idk... yucky.
有什么想法吗?
推荐答案
对于大型集合,JOIN的性能可能优于子查询.
For large sets, a JOIN may perform better than the subquery.
SELECT ma.actor
, COUNT(1) AS total
, COUNT(1) / t.cnt * 100 AS `percentage`
FROM movies_actors ma
CROSS
JOIN (SELECT COUNT(1) AS cnt FROM movies_actors) t
GROUP
BY ma.actor
, t.cnt
对于大型集合,当返回很大比例的行时,JOIN操作通常可以胜过子查询.在您的情况下,它不是相关的子查询,因此MySQL不必多次执行该子查询,因此它可能没有任何区别.
For large sets, and when a large percentage of the rows are being returned, the JOIN operation can usually outperform a subquery. In your case, it's not a correlated subquery, so MySQL shouldn't have to execute that multiple times, so it may not make any difference.
注意COUNT(1)
的非粉丝...我们可以用COUNT(*)
或IFNULL(SUM(1),0)
替换所有出现的COUNT(1)
,以实现相同的结果.
Note to non-fans of COUNT(1)
... we could replace any and all occurrences of COUNT(1)
with COUNT(*)
or IFNULL(SUM(1),0)
to achieve equivalent result.
这篇关于当查询具有GROUP BY时,如何获得总计的百分比?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!