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