mysql - 在同一个表中为 2 个不同的分组依据函数创建 2 列

Question

我有以下 mysql 表:

team_id match_performance_rating    opponent_rating
1          500                    700
1          400                    625
2          600                    400
3          500                    525
2          400                    200

我希望能够测量每个团队的平均表现评分(1 到 3)，具体取决于他们是否面对评分 > 500 或低于 500 的对手，并将其与 match_performance_ rating 进行比较。因此，我会将行的值与 group by 子句的值进行比较。

最终结果应该是这样的:

 team_id match_performance_rating  avg_pm_opp_over_500 avg_pm_opp_less_500
    1          500                    450                Null
    1          400                    450                Null
    2          600                    Null               500
    3          500                    500                Null
    2          400                    Null               500

我尝试执行某种类型的联合查询，但它没有创建额外的列:

select team_id, pm,pm2 from
(
select team_id,avg(match_performance_rating) as pm
from game_team_rating gtr
where gtr.opponent_rating > 500
group by team_id
union

select team_id as t2,avg(match_performance_rating) as pm2
from game_team_rating gtr
where gtr.opponent_rating < 500
group by team_id) as q

Answer 1

您可以通过条件聚合获取这些平均值并将结果连接到表中:

select 
  gtr.team_id,
  gtr.match_performance_rating,
  g.avg_pm_opp_over_500,
  g.avg_pm_opp_less_500
from game_team_rating gtr inner join (
  select 
    team_id,
    avg(case when opponent_rating > 500 then match_performance_rating end) avg_pm_opp_over_500,
    avg(case when opponent_rating <= 500 then match_performance_rating end) avg_pm_opp_less_500
  from game_team_rating
  group by team_id  
) g on g.team_id = gtr.team_id

请参阅demo .
结果:

| team_id | match_performance_rating | avg_pm_opp_over_500 | avg_pm_opp_less_500 |
| ------- | ------------------------ | ------------------- | ------------------- |
| 1       | 500                      | 450                 |                     |
| 1       | 400                      | 450                 |                     |
| 2       | 600                      |                     | 500                 |
| 3       | 500                      | 500                 |                     |
| 2       | 400                      |                     | 500                 |