Windows function help in mysql?-CodePudding

Input: 
Activity table:
 ----------- ----------- ------------ -------------- 
| player_id | device_id | event_date | games_played |
 ----------- ----------- ------------ -------------- 
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 1         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
 ----------- ----------- ------------ -------------- 
Output: 
 ----------- ------------ --------------------- 
| player_id | event_date | games_played_so_far |
 ----------- ------------ --------------------- 
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
 ----------- ------------ ---------------------

I am trying to do cumulative sum for games played by that player in date asc.

select player_id, event_date, sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity a
order by player_id

I am not able to understand how the window function with sum is returning the required output.

For example, over(partition by player_id order by event_date) , doesn't this return player id= 1 in one group sorted by date and then giving sum should return the total date right? But here it's giving the cumulative sum. Please provide some clarity

CodePudding user response：

The expression

sum(games_played) over (partition by player_id order by event_date)

defines a window as being the group of records from the earliest event_date until the current record, the group of records being ordered by event_date. So, it generates a cumulative sum of the number of games played. The PARTITION clause means that each set of player records will have a separate cumulative sum.

CodePudding user response：

If I got it r

Let's see the last part of sum window-function

over(partition by player_id order by event_date)

the "partition by" part means "start calculating from 0 again"
the "order by" means "here is what should be added from line to line

If the "order by" part is missing, database has no glue how to calculate sum from step to step so it will just calculate sum for the whole group (partition by clause) and show it for each line of that group

see dbfiddle here