mysql - 嵌套分组mysql

Question

我有一个results包含以下重要列的表：

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

Ateam可以在任何一个f_team1或f_team2列中

Aplayer可以在任何一个f_player1或f_player2列中

我正在尝试为每个玩家找到 f_total_ftg 的 AVG，然后获取该 AVG 并将其应用于每个团队。因此，这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，这为不同球员的阿森纳提供了正确的总体平均水平。我怎样才能为每个团队做到这一点？我需要分出字符串并为每个团队应用平均值。

我怎样才能做到这一点？

表如下所示：

标签： mysqlsqlsumaverageunpivot

Answer 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