查找或计算锦标赛的统计数据

Question

这个问题在过去的几周里一直困扰着我，但到目前为止我找到的解决方案似乎还不足以缓解我的疑虑。

问题陈述：建立一个表格(查询(，显示以下列(球队名称、比赛次数、胜利、失败、平局和分数(每支球队的分数是在知道每场胜利得3分，平局得1分的情况下计算的，而失败一点也不影响。

表架构：

表">团队"：

列名	类型
id	int
名称	varchar(50(

我规范了matches表的布局以简化事情，但我认为该表的结构已经提供给了您。请告诉我你是否可以控制matches表格的结构，我可以向你展示如何使用lead()来确定比赛双方的输赢/平局状态。

如果您将点值表示为一个表(或者CTE，在这种情况下是通用表表达式(，则可以简化聚合：

with norm_matches as (
select id as match_id, team_1 as team_id, team_1_goals as goals, 
case
when team_1_goals > team_2_goals then 'W'
when team_1_goals = team_2_goals then 'D'
when team_1_goals < team_2_goals then 'L'
end as outcome
from matches
union all
select id as match_id, team_2 as team_id, team_2_goals as goals, 
case
when team_1_goals > team_2_goals then 'L'
when team_1_goals = team_2_goals then 'D'
when team_1_goals < team_2_goals then 'W'
end as outcome
from matches
), points (outcome, value) as (
values ('W', 3), ('D', 1), ('L', 0)
)
select t.name, 
count(1) as matches,
count(1) filter (where m.outcome = 'W') as victories,
count(1) filter (where m.outcome = 'L') as defeats,
count(1) filter (where m.outcome = 'D') as draws,
sum(p.value) as score
from teams t
join norm_matches m on m.team_id = t.id
join points p on p.outcome = m.outcome
group by t.name
order by t.name
;

db<gt；小提琴这里

一个选项是

• 统一">匹配"；字段转换为">团队"；以及">目标"；两次(每队一次(，同时记录胜利(如果A队赢了B队，则为1，平局为0，输掉比赛为-1(
• 在"0"one_answers"0"之间施加CCD_ 4团队"；表和刚刚更新的">匹配"；表
• 在CASE语句的组合中使用所有需要的聚合，如下例所示(COALESCE将把任何null值转换为零(

WITH cte_matches AS (
SELECT id, 
team_1                                          AS team, 
team_1_goals                                    AS goals, 
CASE WHEN team_1_goals > team_2_goals THEN 1
WHEN team_1_goals < team_2_goals THEN -1
ELSE 0 END                                 AS has_won
FROM matches
UNION ALL
SELECT id, 
team_2                                          AS team, 
team_2_goals                                    AS goals, 
CASE WHEN team_2_goals > team_1_goals THEN 1
WHEN team_2_goals < team_1_goals THEN -1
ELSE 0 END                                 AS has_won
FROM matches
)
SELECT t.name, 
COUNT(t.id)      AS matches,
COALESCE(SUM(CASE WHEN has_won =  1 THEN 1 END), 0) AS victories,
COALESCE(SUM(CASE WHEN has_won = -1 THEN 1 END), 0) AS defeats,
COALESCE(SUM(CASE WHEN has_won =  0 THEN 1 END), 0) AS draws,
COALESCE(SUM(CASE WHEN has_won =  1 THEN 3
WHEN has_won =  0 THEN 1 END), 0) AS score
FROM      teams       t
LEFT JOIN cte_matches m
ON t.id = m.team
GROUP BY t.name

请在此处查看演示。