mysql - 根据 where 条件将聚合分组值与每行的值组合起来

Question

我想将按值分组与各行的值进行比较。

与我2天前回答的问题相比Create 2 columns for 2 different group by functions in the same table ，我现在希望能够仅计算低于特定行的日期值的日期值的平均评分。

想象一下下表(称为 game_team_ rating)

team_id  match_performance_rating    opponent_rating         date
    1            500                      700                2019-05-01
    1            400                      625                2019-05-02
    2            600                      400                2019-05-02
    3            500                      525                2019-05-03
    2            400                      200                2019-05-03

最终结果现在应该是这样的:

 team_id   date           match_pr       avg_over_500    avg_less_500
    1      2019-05-01      500             Null               Null
    1      2019-05-02      400             500                Null
    2      2019-05-02      600             Null               Null
    3      2019-05-03      500             Null               Null
    2      2019-05-03      400             Null               400

因此 avg_over_500 和 avg_less_500 列只会查看之前比赛的表现。

我的想法是尝试这样的代码:

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 and gtr.date > date  then match_performance_rating end) avg_pm_opp_over_500,
    avg(case when opponent_rating <= 500 and gtr.date > date  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

但是，这显然不起作用，因为分组值的日期和每行的日期之间没有分隔。

Answer 1

在 MySQL 8+ 中，您将使用窗口函数(在 MySQL 8+ 中可用):

select gtr.*
       avg(case when gtr.opponent_rating > 500 then match_performance_rating end) over
           (partition by gtr.team_id order by gtr.date rows between unbounded preceding and 1 preceding) as pm_over_500,
       avg(case when gtr.opponent_rating < 500 then match_performance_rating end) over
           (partition by gtr.team_id order by gtr.date rows between unbounded preceding and 1 preceding) as pm_under_500
from game_team_rating gtr;