需要自我连接和排名的SQL [英] SQL requiring self join and ranking

问题描述

我有一张足球比赛表:

    CREATE TABLE matches(
    season NUMBER(4),
    matchDate DATE,
    homeTeam VARCHAR2(25),
    awayTeam VARCHAR2(25),
    homeGoals NUMBER(2),
    awayGoals NUMBER(2),
    totalGoals NUMBER(3));

对于每一行，我希望更新totalGoals列. totalGoals是通过将homeTeam在主场比赛的最近5场比赛中得分(homeGoals + awayGoals)的得分与awayTeam进行过的最近5场比赛得分所获得的目标相加得出的.

For each row, I wish to update the totalGoals column. totalGoals is calculated by adding the goals scored (homeGoals + awayGoals) in the 5 most recent matches where the homeTeam played at home to the goals scored in the 5 most recent matches where the awayTeam played away.

仅使用同一赛季的比赛就可以计算出进球数.它不包括当前行中得分的目标.如果任一支球队都没有参加本赛季所需的比赛次数，则totalGoals保持为空.

It calculates goals scored from using only matches from the same season. It does NOT include goals scored in the current row. If either team has not played the required number of matches in the season, totalGoals remains NULL.

我可以使用PL/SQL进行更新，但是有没有办法仅使用SQL来做到这一点?

I can update this using PL/SQL, but is there a way to do this using only SQL?

推荐答案

如果我了解您想要的内容，则可以使用

If I've understood what you want, you can do this with analytic functions and windowing clauses.

select season, matchdate, hometeam, awayteam, homegoals, awaygoals,
    case when home_cnt >= 5 and away_cnt >= 5 then
        home_tot + away_tot
    else null end as totalgoals
from (
    select season, matchdate, hometeam, awayteam, homegoals, awaygoals,
        count(*) over (partition by season, hometeam
            order by matchdate
            rows between 5 preceding and 1 preceding) as home_cnt,
        sum(homegoals + awaygoals) over (partition by season, hometeam
            order by matchdate
            rows between 5 preceding and 1 preceding) as home_tot,
        count(*) over (partition by season, awayteam
            order by matchdate
            rows between 5 preceding and 1 preceding) as away_cnt,
        sum(homegoals + awaygoals) over (partition by season, awayteam
            order by matchdate
            rows between 5 preceding and 1 preceding) as away_tot
    from matches
)
order by season, matchdate, hometeam, awayteam;

内部选择使用count和sum的分析版本以及window子句rows between ...计算每个季节中每个主队/客队的比赛次数和进球总数.将两者都限制为前五个(不包括当前行)，我认为这是您想要的.然后，外部选择项将当前行中两支球队的相关总数加在一起，但同时检查两个计数，如果其中任一<则将总数留空. 5.请注意，它只击过matches表一次.

The inner select calculates the number of matches and the total number of goals across them, for each home/away team in each season, using the analytic version of count and sum, and the window clause rows between ... limits both to the previous five, excluding the current row, which I think is what you want. The outer select then adds the relevant totals together for the two teams in the current row, but checks both counts and leaves the total null if either is < 5. Note that it only hits the matches table once.

在订购之前紧接附加过滤器:

With an additional filter immediately before the order-by:

where season = 2012 and homeTeam = 'Norwich' and awayteam = 'Aston Villa'

...您得到:

    SEASON MATCHDATE HOMETEAM                  AWAYTEAM                   HOMEGOALS  AWAYGOALS TOTALGOALS
---------- --------- ------------------------- ------------------------- ---------- ---------- ----------
      2012 13-MAY-12 Norwich                   Aston Villa                        2          0         30

您可以使用它来更新匹配行的表，尽管通常我会根据需要进行计算，以避免可能在视图中出现潜在的数据完整性错误.

You could use this to update the table for the matching row, though generally I'd calculate it as needed to avoid potential data integrity errors, possibly in a view.

这篇关于需要自我连接和排名的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！