Windows function help in mysql?

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x