[Solved] SQL moving aggregate SUM without partial results

EverSQL Database Performance Knowledge Base

SQL moving aggregate SUM without partial results

Database type:

Assume I have this schema (tested on postgresql) where the 'Scorelines' relation contains results of sport matches. (kickoff is a TIMESTAMP but replaced by INT for readability)

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

CREATE TABLE Scorelines (
   team TEXT, 
   kickoff INT,
   scored INT,
   conceded INT
);

Now I want to produce another column 'three_matches_scored' that contains the sum of the points scored over the 3 preceding game (determined by kickoff) of the same team. I have this:

SELECT team, kickoff, scored, conceded, SUM(scored) OVER three_matches AS three_matches_scored 
FROM Scorelines
WINDOW three_matches AS
      (PARTITION BY team ORDER BY kickoff
       ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
ORDER BY kickoff;

This works beautifully so far, except that I get values starting from the second game. Example:

| TEAM | KICKOFF | SCORED | CONCEDED | THREE_MATCHES_SCORED |
|------|---------|--------|----------|----------------------|
|    A |       1 |      1 |        0 |               (null) |
|    B |       2 |      1 |        1 |               (null) |
|    A |       3 |      1 |        1 |                    1 |
|    A |       4 |      3 |        0 |                    2 |
|    B |       4 |      1 |        4 |                    1 |
|    A |       6 |      0 |        2 |                    5 |
|    B |       6 |      4 |        2 |                    2 |
|    B |       8 |      1 |        2 |                    6 |
|    B |      10 |      1 |        1 |                    6 |
|    A |      11 |      2 |        1 |                    4 |

I want the column 'three_matches_scored' to be (null) for the first 3 games because there are no 3 results to sum up. How can I achieve this?

I'd prefer simple understandable solutions, performance is not critical for this particular case.

My only idea right now, is to define a stored function SUM3, that results in (null) with less than 3 values to add up. But I never defined a function in SQL and can't seem to figure it out.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX scorelines_idx_kickoff ON "Scorelines" ("kickoff");
The optimized query:
SELECT
        Scorelines.team,
        Scorelines.kickoff,
        Scorelines.scored,
        Scorelines.conceded,
        SUM(Scorelines.scored) OVER three_matches AS three_matches_scored 
    FROM
        Scorelines WINDOW three_matches AS (PARTITION 
    BY
        team 
    ORDER BY
        kickoff ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) 
    ORDER BY
        Scorelines.kickoff

Related Articles



* original question posted on StackOverflow here.