CODEWITHSUNDEEP
sqloracle-database
Tams
Tams

Reputation: 192

SQL requiring self join and ranking

I have a table of football results:

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

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.

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.

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

Upvotes: 1

Views: 1023

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

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;

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'

... you get:

    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.

Upvotes: 2

Henrique Ordine
Henrique Ordine

Reputation: 3337

I tried the following on MySQL, but it failed with "Error Code: 1093. You can't specify target table 'm' for update in FROM clause".

Maybe you can try it with Oracle. You'll have to replace the "limit 0,5" clause with Oracle's specific way of limiting the number of rows. I think they use "where rownum < 6".

update matches m
  set totalGoals = (select sum(homeGoals) + sum(awayGoals) 
                      from matches
                      where homeTeam = m.homeTeam
                        and season = m.season
                        and matchDate < m.matchDate
                      having count(matchDate) > 4
                      order by matchDate limit 0,5) +
                   (select sum(homeGoals) + sum(awayGoals) 
                      from matches
                      where awayTeam = m.awayTeamm
                        and season = m.season
                        and matchDate < m.matchDate
                      having count(matchDate) > 4
                      order by matchDate limit 0,5);

Upvotes: 0

Related Questions