python - Calculate previous results in PostgreSQL -

i trying complete interesting research, , add new features 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, want add additional columns

• match_goals - goals kicked in previous match team
• prev_result - result of previous game [1 - win, 2- draw, 3 - lose]
• all_goals_in_season - total goals kicked team in season

my attempt case #1 not correct:

select row_number() on (order start_time desc) rowno,        lead(m.match_score) on (order start_time desc) match_goals, -- #1  match m (m.home = m.home or m.away = m.away) #fail order start_time desc 

can advise appropriate window functions (postgresql), or fixed example?

i have solution tested in sql server, see postgresql has support goodies in sql server (ctes, window functions etc.), should start.

your data structure quite unfriendly direct window function appliance, cte obtains in way easier deal with:

id, date, league, team, goals 

this allows previous information, since team present in single column.

also, first cte computes if team's result in particular match.

second cte gets information previous match, using lag window function.

;with pivcte (     select id, date, league, home team, hgoals goals,          (case when hgoals > agoals 1 when hgoals = agoals 2 else 3 end) result,         sum(hgoals) on (partition home order (select 1)) allgoals     match      union     select id, date, league, away team, agoals goals,         (case when hgoals < agoals 1 when hgoals = agoals 2 else 3 end) result,         sum(agoals) on (partition away order (select 1)) allgoals      match ), prevdata (     select id, date, league, team,          lag(goals, 1, null) on (partition team order date) prevgoals,         lag(result, 1, null) on (partition team order date) prevresult,         allgoals     pivcte ) select m.*,      ph.prevgoals homeprevgoals, pa.prevgoals awayprevgoals,      ph.prevresult homeprevwin, pa.prevresult awayprevwin,     ph.allgoals homeallgoals, pa.allgoals awayallgoals match m     join prevdata ph on ph.id = m.id , ph.team = m.home     join prevdata pa on pa.id = m.id , pa.team = m.away order 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 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)