MySQL 当查询有一个 GROUP BY 时如何获得总数的百分比？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14516023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get a percentage of total when the query has a GROUP BY?
提问by neubert
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)
);
I do a SELECT actor, COUNT(1) FROM movies_actors GROUP BY actorto 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) FROM movies_actors GROUP BY actor以找出该演员参演了多少部电影。但我也想知道该演员参演了多少电影。
I guess I could do this:
我想我可以这样做：
SELECT
actor,
COUNT(1) AS total,
COUNT(1) / (SELECT COUNT(1) FROM movies_actors) * 100 AS avg
FROM movies_actors
GROUP BY actor;
But that just seems... idk... yucky.
但这似乎... idk ... 恶心。
Any ideas?
有任何想法吗？
回答by spencer7593
For large sets, a JOIN may perform better than the subquery.
对于大型集合，JOIN 可能比子查询执行得更好。
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
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.
对于大型集合，当返回大部分行时，JOIN 操作通常可以胜过子查询。在您的情况下，它不是相关子查询，因此 MySQL 不必多次执行该查询，因此它可能没有任何区别。
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.
请注意COUNT(1)...的非粉丝，我们可以替换任何和所有出现的COUNT(1)withCOUNT(*)或IFNULL(SUM(1),0)以达到相同的结果。
回答by Sunil shakya
Without using join and multiple query :-
不使用连接和多个查询：-
select actor,counter, 100 * counter / @total as percentage
from(
select actor,
case when actor is null
then @total := count(*)
else count(*)
end as counter
from movies_actors
group by actor
with rollup
) mytable
回答by Pierre Godts
This works for me:
这对我有用：
SELECT tmpTotal.yearmonth, tmpTotal.rec_count,
(tmpTotal.rec_count / @myCumul) * 100 AS myPercentage
FROM
(
SELECT tmpResult.*, @myCumul := @myCumul + tmpResult.rec_count AS myNewCumul
FROM
(
SELECT date_format(d.created_at, '%Y/%m') as yearmonth, count(*) rec_count
FROM cf4a_webapp.factTable d
join cf4a_webapp.dimTable c on (d.client_id = c.id)
WHERE c.id = 25
AND d.created_at >= '2019-01-01 00:00:01'
AND d.created_at < '2020-01-01 00:00:01'
GROUP BY yearmonth
) tmpResult
JOIN (SELECT @myCumul := 0) tmpCumul
) tmpTotal;
回答by lc.
I'm not sure if it's any "better", but you could do a SUM and do the math elsewhere:
我不确定它是否“更好”，但你可以做一个 SUM 并在其他地方做数学：
SELECT actor,
COUNT(1) AS total,
SUM(oneMoviePercentPts) AS percentage
FROM movies_actors
CROSS JOIN
(
SELECT 100 / CAST(COUNT(1) AS DECIMAL(15,4)) AS oneMoviePercentPts
FROM movies_actors
) t
GROUP BY actor
I would hope the MySQL optimizer is smart enough to not execute your subquery more than once but the join syntax makes that explicit.
我希望 MySQL 优化器足够聪明，不会多次执行您的子查询，但连接语法使其明确。
回答by logan
Do a Self cross join whenever you want to get manipulated data from same table.
每当您想从同一个表中获取操作数据时，请执行自交叉联接。
SELECT
m.actor,
COUNT(m.actor) AS total,
(COUNT(m.actor) / t.total_movies) * 100 AS avg
FROM movies_actors m
cross (select count(*) as total_movies from movies_actors) t
GROUP BY m.actor;
