Вложенная группировка mysql

#mysql #sql #sum #average #unpivot

#mysql #sql #сумма #среднее #открепить

Вопрос:

У меня есть таблица results со следующими важными столбцами:

f_team1 (VARCHAR)
f_team2 (VARCHAR)
f_player1 (VARCHAR)
f_player2 (VARCHAR)
f_total_ftg (INT)

A team может быть в f_team1 f_team2 столбцах или

A player может быть в f_player1 f_player2 столбцах или

Я пытаюсь найти среднее значение f_total_ftg для каждого игрока, а затем беру это среднее значение и применяю его для каждой команды. Таким team_average образом, это будет сумма всех средних значений отдельных игроков.

Моя попытка:

 SELECT team,
(
        SELECT AVG(player_team_average) AS players_team_average
                 FROM
                 (
                   SELECT AVG(CASE WHEN f_team1='arsenal' OR f_team2='arsenal' THEN f_total_ftg END) AS team_average
                    FROM
                    (
                        SELECT f_player1 AS player, f_team1, f_team2, f_total_ftg FROM results
                        UNION
                        SELECT f_player2 AS player, f_team1, f_team2, f_total_ftg FROM results
                    ) x GROUP BY player
                 ) x
) AS team_average FROM (
    
    (SELECT f_team1 AS team, f_total_ftg FROM results)
    UNION
    (SELECT f_team2 AS team, f_total_ftg FROM results)
    ) x GROUP BY team

Текущий вывод:

Как вы можете видеть из моего кода, я поместил команду как arsenal , и это дает правильное общее среднее значение для арсенала для разных игроков. Как я могу сделать это для каждой команды? Мне нужно вычесть строку и применить среднее значение для каждой команды.

Как я могу это сделать?

Таблица выглядит следующим образом:

1. Можете ли вы добавить больше образцов данных из ваших таблиц и добавить ожидаемый результат, спасибо!

2. f_player1 Принадлежит f_team_1 и f_player2 принадлежит f_team_2 ? Если это так, и если один и тот же игрок иногда играл за одну команду, а иногда за другую, вы хотите, чтобы при расчете для обеих команд учитывалось общее среднее значение игрока, или вы хотите рассчитать средние значения этого игрока, связанные с командой?

3. На заметку: среднее значение среднего значения — это то, что редко требуется. Например: команде, состоящей из очень слабых игроков, для которых у вас есть результаты на протяжении многих лет, возможно, дали несколько суперплееров за одно преимущество. Это не сильно повлияет на общее среднее значение, но сильно повлияет на среднее значение игрока.

4. Рассмотрите возможность пересмотра вашей модели данных

Ответ №1:

Это то, что вы хотите?

 select team, sum(avg_ftg) sum_avg_ftg
from (
    select team, player, avg(f_total_ftg) avg_ftg
    from (
        select f_team1 team, f_player1 player, f_total_ftg from mytable
        union all select f_team2, f_player2, f_total_ftg  from mytable
    ) t
    group by team, player
) t
group by team

Это отключает кортежи команды / игроков от строк, затем вычисляет среднее значение ftg для каждого игрока и команды и, наконец, суммирует среднее значение для команды.

В самых последних версиях MySQL это может быть более эффективно выражено с помощью бокового соединения:

 select team, sum(avg_ftg) sum_avg_ftg
from (
    select x.team, x.player, avg(t.f_total_ftg) avg_ftg
    from mytable t
    cross join lateral (
        select t.f_team1 team, t.f_player1 player
        union all select t.f_team2, t.f_player2
    ) x
    group by x.team, x.player
) t
group by team

1. Ах да, да. Я думаю, что это самое элегантное решение. Спасибо!

2. В вашем объединении вы выбираете f_team1 в качестве команды .. это не будет включать f_team2 в группировки и, в конечном итоге, среднее значение, хотя?

3. @arsenal88: team2 есть в другом члене union .