CODEWITHSUNDEEP
postgresql
Luke Byrne
Luke Byrne

Reputation: 119

Cumulative sum of multiple window functions

I have a table with the structure:


id | date       | player_id | score
--------------------------------------
1  | 2019-01-01 | 1         | 1
2  | 2019-01-02 | 1         | 1
3  | 2019-01-03 | 1         | 0
4  | 2019-01-04 | 1         | 0
5  | 2019-01-05 | 1         | 1
6  | 2019-01-06 | 1         | 1
7  | 2019-01-07 | 1         | 0
8  | 2019-01-08 | 1         | 1
9  | 2019-01-09 | 1         | 0
10 | 2019-01-10 | 1         | 0
11 | 2019-01-11 | 1         | 1

I want to create two more columns, 'total_score', 'last_seven_days'.

total_score is a rolling sum of the player_id score

last_seven_days is the score for the last seven days including to and prior to the date

I have written the following SQL query:

SELECT id,
    date,
    player_id,
    score,
    sum(score) OVER all_scores AS all_score,
    sum(score) OVER last_seven AS last_seven_score
   FROM scores
  WINDOW all_scores AS (PARTITION BY player_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 
last_seven AS (PARTITION BY player_id ORDER BY id ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING);

and get the following output:


id | date       | player_id | score | all_score | last_seven_score   
------------------------------------------------------------------
1  | 2019-01-01 | 1         | 1     |           | 
2  | 2019-01-02 | 1         | 1     | 1         | 1
3  | 2019-01-03 | 1         | 0     | 2         | 2
4  | 2019-01-04 | 1         | 0     | 2         | 2
5  | 2019-01-05 | 1         | 1     | 2         | 2
6  | 2019-01-06 | 1         | 1     | 3         | 3
7  | 2019-01-07 | 1         | 0     | 4         | 4
8  | 2019-01-08 | 1         | 1     | 4         | 4
9  | 2019-01-09 | 1         | 0     | 5         | 4
10 | 2019-01-10 | 1         | 0     | 5         | 3
11 | 2019-01-11 | 1         | 1     | 5         | 3

I have realised that I need to change this

last_seven AS (PARTITION BY player_id ORDER BY id ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)

to instead of being 7, to use some sort of date format because just having the number 7 will introduce errors.

i.e. it would be nice to be able to do date - 2days or date - 6days

I also would like to add columns such as 3 months, 6 months, 12 months later down the track and so need it to be able to be dynamic.

DEMO

Upvotes: 1

Views: 201

Answers (2)

S-Man
S-Man

Reputation: 23756

demo:db<>fiddle

Solution for Postgres 11+:

Using RANGE interval as @LaurenzAlbe did

Solution for Postgres <11:

(just presenting the "days" part, the "all_scores" part is the same)

Joining the table against itself on the player_id and the relevant date range:

SELECT s1.*,
    (SELECT SUM(s2.score)
     FROM scores s2
     WHERE s2.player_id = s1.player_id
         AND s2."date" BETWEEN s1."date" - interval '7 days' AND s1."date" - interval '1 days')
FROM scores s1

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247625

You need to use a window by RANGE:

last_seven AS (PARTITION BY player_id
               ORDER BY date
               RANGE BETWEEN INTERVAL '7 days' PRECEDING
                         AND INTERVAL '1 day' PRECEDING)

This solution will work only from v11 on.

Upvotes: 1

Related Questions