这个问题过go 几周一直困扰着我,但到目前为止,我达成的解决方案似乎还不够好,不足以消除我的疑虑.

Problem Statement:构建一个表(查询),其中显示以下列(球队名称、比赛次数、胜利、失败、平局和得分).每支球队的得分都是在知道每次胜利得3分、平局得1分的情况下计算出来的,而失败则完全没有影响.

Table Schemas:

表"teams":

Column name Type
id int
name varchar(50)

表"matches":

Column name Type
id int
team_1 int
team_2 int
team_1_goals int
team_2_goals int

Sample Data:

表"teams":

id name
1 CEARA
2 FORTALEZA
3 GUARANY DE SOBRAL
4 FLORESTA

表"matches":

id team_1 team_2 team_1_goals team_2_goals
1 4 1 0 4
2 3 2 0 1
3 1 3 3 0
4 3 4 0 1
5 1 2 0 0
6 2 4 2 1

Expected Output:

name matches victories defeats draws score
CEARA 3 2 0 1 7
FORTALEZA 3 2 0 1 7
FLORESTA 3 1 2 0 3
GUARANY DE SOBRAL 3 0 3 0 0

What I've managed to do so far:

SELECT
    t.name, 
    count(m.team_1) filter(WHERE t.id = m.team_1)
        + count(m.team_2) filter(WHERE t.id = m.team_2) "matches",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals) "victories",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals < m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals > m.team_2_goals) "defeats",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "draws",
    ((count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals))* 3) +
        count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "score"
FROM
    teams t
JOIN matches m ON t.id IN (m.team_1, m.team_2)
GROUP BY t.name
ORDER BY "victories" DESC

其理论上输出正确答案.

我试着用像CASE WHEN或更大的JOIN这样的花哨的东西来制作,但没有取得好的效果.我想知道的是,就编写和服务器性能而言,是否有更好的方法来执行此查询.

感谢任何人的帮助!

推荐答案

为了简化操作,我标准化了matches表的布局,但我假设该表的 struct 已经给您了.请让我知道你是否控制了matches桌的 struct ,我可以告诉你如何使用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<>fiddle 100

Sql相关问答推荐

用于动态查询情况的存储过程常识模式

有没有办法在每次计算每一行的数据时更新2个值?

转换表中的数据

一个SQL查询将在需要的地方多次返回同一成员

将计算列设置为持久化的目的是什么?

按分类标准检索记录

SQL:查询作为子查询或CTE写入的最大和数失败

提取连续时间戳范围的SQL

如何在Hive SQL中分别按多列进行分组?

DB2 SQL查询结果多余数据

将时间戳四舍五入到最近 10 分钟的查询

如何查询自引用 comments 表以查找带有回复的 comments ,并按最新回复排序?

如何在 case 语句中使用聚合?

Teradata 多个进程的最大进程结束时间捕获

如何将 CONCATENATED 值与临时表中的值匹配

查找具有相同连接列数据的所有记录

将单行中的多个行值转换为列

在时态表和非时态表之间使用 EXCEPT 的 SQL 子查询给出表达式错误数

为什么 PostgreSQL 1 年间隔 <> 365 天间隔?

使用 SQL 查询从多个条件中删除重复行