CODEWITHSUNDEEP
pythonsqlpostgresqlpython-2.7pandas
SpanishBoy
SpanishBoy

Reputation: 2225

Calculate previous results in PostgreSQL

I am trying to complete an interesting research, and add new features to existing dataset

ID      DATE           LEAGUE         HOME        AWAY    H_GOALS A_GOALS PREV_H_GOALS PREV_A_GOALS
9911 "2005-01-01" "Bundesliga 1" "Wolfsburg"  "Schalke 04"    2      1          NULL      NULL
9822 "2005-01-01" "Jupiler League" "Beveren" "Lokeren"        2      1          NULL      NULL
9823 "2005-01-01" "Jupiler League" "Waregem" "Westerlo"       2      3          NULL      NULL
9824 "2005-01-10" "Jupiler League" "Westerlo" "Beveren"       4      1           3         2
9932 "2005-01-10" "Bundesliga 1" "Bayern Munich" "Wolfsburg"  2      0          NULL       2
9933 "2005-01-10" "Bundesliga 1" "Ein Frankfurt" "Schalke 04" 0      1          NULL       1

Now, I want to add additional columns like

My attempt for case #1 is not correct:

SELECT ROW_NUMBER() OVER (ORDER BY start_time DESC) AS rowno,
       LEAD(m.match_score) OVER (ORDER BY start_time DESC) as MATCH_GOALS, -- #1

FROM match as m
WHERE (m.home = m.home OR m.away = m.away) #fail
ORDER BY start_time DESC

Can you advise appropriate window functions (PostgreSQL), or fixed example?

Upvotes: 0

Views: 50

Answers (1)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23088

I have a solution tested in SQL Server, but I see that PostgreSQL has support for all the goodies in SQL Server (CTEs, Window functions etc.), so this should be a good start.

Your data structure is quite unfriendly for direct window function appliance, so a CTE obtains it in a way easier to deal with:

ID, Date, League, Team, Goals

This allows to get previous information, since team is present in a single column.

Also, first CTE computes if the team's result in that particular Match.

Second CTE actually gets information about previous match, using LAG window function.

;with PivCTE AS (
    SELECT ID, Date, League, Home AS Team, HGoals AS Goals, 
        (CASE WHEN HGoals > AGoals THEN 1 WHEN HGoals = AGoals THEN 2 ELSE 3 END) AS Result,
        SUM(HGoals) OVER (PARTITION BY Home ORDER BY (SELECT 1)) AS AllGoals
    FROM Match 
    UNION
    SELECT ID, Date, League, Away AS Team, AGoals AS Goals,
        (CASE WHEN HGoals < AGoals THEN 1 WHEN HGoals = AGoals THEN 2 ELSE 3 END) AS Result,
        SUM(AGoals) OVER (PARTITION BY Away ORDER BY (SELECT 1)) AS AllGoals 
    FROM Match
),
PrevData AS (
    SELECT ID, Date, League, Team, 
        LAG(Goals, 1, NULL) OVER (PARTITION BY Team ORDER BY Date) AS PrevGoals,
        LAG(Result, 1, NULL) OVER (PARTITION BY Team ORDER BY Date) AS PrevResult,
        AllGoals
    FROM PivCTE
)
select M.*, 
    PH.PrevGoals AS HomePrevGoals, PA.PrevGoals AS AwayPrevGoals, 
    PH.PrevResult AS HomePrevWin, PA.PrevResult AS AwayPrevWin,
    PH.AllGoals AS HomeAllGoals, PA.AllGoals AS AwayAllGoals
FROM Match M
    JOIN PrevData PH ON PH.ID = M.ID AND PH.Team = M.Home
    JOIN PrevData PA ON PA.ID = M.ID AND PA.Team = M.Away
ORDER BY M.Date DESC

Setup data:

create table Match (
    ID INT NOT NULL,
    Date DATE NOT NULL,
    League NVARCHAR(100) NOT NULL,
    Home NVARCHAR(100) NOT NULL,
    Away NVARCHAR(100) NOT NULL,
    HGoals INT NOT NULL,
    AGoals INT NOT NULL
)

insert into Match values 

(9911, '2005-01-01', 'Bundesliga 1', 'Wolfsburg',  'Schalke 04',    2,      1),
(9822, '2005-01-01', 'Jupiler League', 'Beveren', 'Lokeren',        2,      1),
(9823, '2005-01-01', 'Jupiler League', 'Waregem', 'Westerlo',       2,      3),
(9824, '2005-01-10', 'Jupiler League', 'Westerlo', 'Beveren',       4,      1),
(9932, '2005-01-10', 'Bundesliga 1', 'Bayern Munich', 'Wolfsburg',  2,      0),
(9933, '2005-01-10', 'Bundesliga 1', 'Ein Frankfurt', 'Schalke 04', 0 ,     1)

Upvotes: 1

Related Questions