Как получить процент от общего числа, когда запрос имеет GROUP BY?

Скажем, у меня есть ненормированная таблица с именами киноактеров и фильмами, в которых они были. Например:

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, чтобы узнать, сколько фильмов актер был. Но я также хочу узнать, какой процент фильмов, в которых находился актер.

Думаю, я мог бы сделать это:

SELECT
  actor,
  COUNT(1) AS total,
  COUNT(1) / (SELECT COUNT(1) FROM movies_actors) * 100 AS avg
FROM movies_actors
GROUP BY actor;

Но это просто кажется... idk... yucky.

Любые идеи?

Ответ 1

Для больших наборов 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

Для больших наборов и когда возвращается большой процент строк, операция JOIN обычно может превзойти подзапрос. В вашем случае это не коррелированный подзапрос, поэтому MySQL не должен выполнять его несколько раз, поэтому он может не иметь никакого значения.

Примечание для не поклонников COUNT(1)... мы могли бы заменить любое и все вхождения COUNT(1) на COUNT(*) или IFNULL(SUM(1),0) для достижения эквивалентного результата.

Ответ 2

Без использования соединения и множественного запроса: -

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

Ответ 3

Я не уверен, что это "лучше", но вы можете сделать 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

Я бы надеюсь, что оптимизатор MySQL достаточно умен, чтобы не выполнять ваш подзапрос более одного раза, но синтаксис соединения делает это явным.

Ответ 4

Сделайте Self cross join, когда вы хотите получить управляемые данные из одной таблицы.

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;