[534] Game Play Analysis III

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

 

Write an SQL query to report for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date. Check the example for clarity.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

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                   |
+-----------+------------+---------------------+
Explanation: 
For the player with id 1, 5 + 6 = 11 games played by 2016-05-02, and 5 + 6 + 1 = 12 games played by 2017-06-25.
For the player with id 3, 0 + 5 = 5 games played by 2018-07-03.
Note that for each player we only care about the days when the player logged in.

 

보면 날짜별로 game_played 값이 누적되어 합해지는 것을 확인할 수 있다.

이에 해당 문제는 누적합을 구하는 문제로 이해할 수 있었습니다.

 

MySQL 구버전에서는 Window Function을 지원하지 않지만, 가장 간단하게 풀 수 있는 방법이라 생각해서 

누적합을 구할 수 있는 sum(partition by x order by y) 함수식을 이용하여 문제를 풀었습니다.

 

해당 문제는 player_id별로 event_date에 따른 games_played의 누적합을 구해야 했기 때문에,

sum(games_palyed) over (partition by palyer_id order by event_date)를 통해 games_palyed_so_far을 구했습니다

# 기존 오류 코드 
select player_id
        , event_date
        , sum(games_played) over (order by event_date) as games_played_so_far --player_id별로 partition을 안넣음 
from activity 
group by player_id, event_date

# window function
select player_id 
	,event_date
    ,sum(games_played) over (partition by player_id order by event_date) as games_played_so_far
from activity 
group by 1,2

 

만약 window function을 사용 할 수 없는 상황이라면?