1 题目描述

表：Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

(player_id, event_date)是此表的主键(具有唯一值的列)
这张表显示了某些游戏的玩家的活动情况
每一行是一个玩家的记录, 他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0 )

编写一个解决方案, 同时报告每组玩家和日期, 以及玩家到 目前为止 玩了多少游戏, 也就是说, 玩家在该日期之前所玩的游戏总数, 详细情况请查看示例

以 任意顺序 返回结果表

1.1 测试用例

示例 1：
输入：

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            |
+-----------+-----------+------------+--------------+

输出：

+-----------+------------+---------------------+
| 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                   |
+-----------+------------+---------------------+

解释：
对于 ID 为 1 的玩家, 2016-05-02 共玩了 5+6=11 个游戏, 2017-06-25 共玩了 5+6+1=12 个游戏
对于 ID 为 3 的玩家, 2018-07-03 共玩了 0+5=5 个游戏
请注意, 对于每个玩家, 我们只关心玩家的登录日期

2 解题思路

2.1 解法 1: group by + join

题目要求: 按照 玩家和日期 统计到目前为止玩了多少游戏, 马上想到 group by player_id, event_date
问题点: 如何统计当前日期及之前的游戏数量总和
将 Activity a 关联 Activity b, b 负责group by, 查询玩家 和 日期数据, a 负责统计 当前日期及之前游戏数量总和

b 表每一个 event_date 对应 a 表 event_date 日期及之前的数据记录

player_id = 1
event_date = 2016-03-01 时,  a 表存在 1 条记录
event_date = 2016-05-02 时,  a 表存在 2 条记录
event_date = 2017-06-25 时,  a 表存在 3 条记录
这样就能统计当前日期及之前游戏数量

最终 sql 实现

select b.player_id,  b.event_date,  sum(a.games_played) as games_played_so_far 
from Activity as a join Activity as b on a.player_id = b.player_id and a.event_date <= b.event_date
group by b.player_id,  b.event_date

2.2 解法 2: sum() over()

使用 sum() over() 统计数据
语法

SUM(<column>) OVER (PARTITION BY <partition_column> ORDER BY <order_column>)

• <column> 是需要计算总和的列名
• <partition_column> 是根据哪个列进行分区的列名
• <order_column> 是按照哪个列进行排序的列名

在 SUM() OVER() 函数中, <order_column> 指定了按照哪个列进行排序, 通常情况下, 这个列是时间列, 例如上例中的 event_date 列, 由于在 SQL 中, 时间戳是按照从过去到未来递增的顺序进行排序的, 因此可以通过将 <order_column> 列进行升序排序, 来实现统计当前时间及之前的结果

在实际使用中, 使用类似于 ORDER BY event_date ASC 的排序语句, 将 event_date 列按照时间戳的升序排列, 然后使用 SUM() OVER() 函数取得该行及以前行的游戏总数, 这意味着, 对于给定的 player_id, 它会遍历整个表, 并在每一行上使用 SUM() 函数, 将该行及之前行的游戏数量加起来

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