Windows function help in mysql?

Issue

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

Solution

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.

Answered By – Tim Biegeleisen

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published