database - SQL moving aggregate SUM without partial results -

database - SQL moving aggregate SUM without partial results -

assume have schema (tested on postgresql) 'scorelines' relation contains results of sport matches. (kickoff timestamp replaced int readability)

sqlfiddle here: http://sqlfiddle.com/#!12/52475/3

create table scorelines ( team text, kickoff int, scored int, conceded int );

now want produce column 'three_matches_scored' contains sum of points scored on 3 preceding game (determined kickoff) of same team. have this:

select team, kickoff, scored, conceded, sum(scored) on three_matches three_matches_scored scorelines window three_matches (partition team order kickoff rows between 3 preceding , 1 preceding) order kickoff;

this works beautifully far, except values starting sec game. example:

| team | kickoff | scored | conceded | three_matches_scored | |------|---------|--------|----------|----------------------| | | 1 | 1 | 0 | (null) | | b | 2 | 1 | 1 | (null) | | | 3 | 1 | 1 | 1 | | | 4 | 3 | 0 | 2 | | b | 4 | 1 | 4 | 1 | | | 6 | 0 | 2 | 5 | | b | 6 | 4 | 2 | 2 | | b | 8 | 1 | 2 | 6 | | b | 10 | 1 | 1 | 6 | | | 11 | 2 | 1 | 4 |

i want column 'three_matches_scored' (null) first 3 games because there no 3 results sum up. how can accomplish this?

i'd prefer simple understandable solutions, performance not critical particular case.

my thought right now, define stored function sum3, results in (null) less 3 values add together up. never defined function in sql , can't seem figure out.

you can utilize case statement null rows there less 3 games:

select team, kickoff, scored, conceded, case when count(scored) on three_matches = 3 sum(scored) on three_matches else null end three_matches_scored scorelines window three_matches (partition team order kickoff rows between 3 preceding , 1 preceding) order kickoff;

output:

team | kickoff | scored | conceded | three_matches_scored ------+---------+--------+----------+---------------------- | 1 | 1 | 0 | b | 2 | 1 | 1 | | 3 | 1 | 1 | | 4 | 3 | 0 | b | 4 | 1 | 4 | | 6 | 0 | 2 | 5 b | 6 | 4 | 2 | b | 8 | 1 | 2 | 6 b | 10 | 1 | 1 | 6 | 11 | 2 | 1 | 4 (10 rows)

sql database postgresql