计算每个团队的输赢率

Question

我有以下两个表：

sport_a_statistics：

id      team_id     game_id     points
1       1           1           7   
2       2           1           8
3       3           2           6   
4       1           2           9

sport_b_statistics：

id      team_id     game_id     points
1       1           3           2   
2       2           3           1
3       3           4           3   
4       1           4           10

我想计算每个团队的输赢率。这包括确保从两个运动桌上捕捉胜利和失败，因为我的锦标赛涉及 2 项运动。所以我正在寻找的输出如下：

team_id    wins    loss    ratio
1          3       1       3.0
2          1       1       1.0
3          0       2       0.0

我无法弄清楚如何在一个查询中执行此操作。

Answer 1

假设你没有联系，你可以使用窗口函数和union all：

select team_id,
sum(points = max_points) as num_wins,
sum(points < max_points) as num_losses,
sum(points = max_points) / nullif(sum(points < max_points), 0) as ratio
from ((select a.*, max(points) over (partition by game_id) as max_points
from sport_a a
) union all
(select b.*, max(points) over (partition by game_id) as max_points
from sport_b b
) 
) ab
group by team_id

---

做了一个小编辑^