SQL query - possibly requires analytic functions

Question

I asked a similar question yesterday that was answered correctly. SQL requiring self join and ranking Although this question is similar, I'm unable to adapt the answer to fit.

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));

INSERT statements can be found here: https://gist.github.com/2941229

I wish to calculate a value for totalGoals as follows. It is the total number of goals scored (homeGoals+awayGoals) in the 5 most recent matches played by the current home team added to the number of goals scored in the 5 most recent matches played by the current away team BEFORE the current match. It must NOT include the goals from the current match.

The difference with yesterday's question was that it only counted previous matches where the home team played at home and the away team played away. With this question, it doesn't matter if the home team previously played at home or away. They just had to be one of the teams in that previous match.

As before, only matches from the current season should be used, and if either team has not played 5 matches in the season, then totalGoals should remain NULL.

I can do this using PL/SQL, but I'd much prefer a SQL query. Yesterday's solution was about a thousand times faster than my PL/SQL.

Thanks Tams

How do _you_ think it might work? You may find you get a better quality of answer and that people are more willing to help you if you are able to demonstrate that you have [tried something for yourself](http://mattgemmell.com/2008/12/08/what-have-you-tried/). — Ben, Jun 17 '12 at 09:24
@Ben - Until yesterday I had never heard of analytic functions. I was suggesting that it would need some form of ranking and self join. I was completely wrong. I often find it's better not to lead a programmer down a particular route, as it may lead them down a wrong path that they wouldn't have gone down if I'd said nothing. I've been able to adapt yesterday's solution for a variety of other queries, but because of the way it is written, as requested, it it difficult for me to see how I could adapt it for this purpose. — Tams, Jun 17 '12 at 09:36
Yes _everyone_ is helping you without monetary reward. If someone asks for such; click the `` link and explain what's happening to a moderator. If you have the time, I'd be grateful if you read the links I've posted. — Ben, Jun 17 '12 at 10:27
Not everyone, Ben. Some prefer to heckle from the sidelines when they can't help. — Tams, Jun 17 '12 at 19:41

Alex Poole · Accepted Answer · 2012-06-17T15:53:26.087

Essentially you need to to be able introduce an 'or' of some kind to the home/away team calculations, but you can't do that (as far as I know) inside the analytic functions. It looks like you need to create an intermediate table (which exists only during the query, so more a view I guess) that has an additional calculated column with either team name. You can use unpivot for that, but you lose the original columns, which you may still need. So you can fall back to a pre-11g psuedo-unpivot, something like:

select season, matchdate, hometeam, awayteam, homegoals, awaygoals,
    case when rn = 1 then hometeam else awayteam end as anyteam
from matches
cross join (select level as rn from dual connect by level <= 2)

That will give you two rows for every one in your original table. You should be able to use that as a(nother) subquery in place of matches in yesterday's answer, with some tweaking of the analytics clauses, and something to remove duplicates.

I think you've done it again, Alex. I just need a view that lists each row twice with a TEAM rather than a hometeam or awayteam. Then I can use your solution from yesterday. Thanks again! — Tams, Jun 17 '12 at 10:13

SQL query - possibly requires analytic functions

1 Answers1