如何理解统计玩家累计游戏次数的SQL自内连接查询的工作原理？

前提说明 #

我们的需求是统计每个玩家截止到每一个活动日期的累计游戏游玩次数，也就是结果表的games_played_so_far字段。
原表Activity结构和示例数据如下：

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

对应查询语句如下：

select a1.player_id, a1.event_date, sum(a2.games_played) as games_played_so_far
from activity as a1
inner join activity as a2
on a1.event_date >= a2.event_date
and a1.player_id = a2.player_id
group by  a1.player_id, a1.event_date

逐段拆解运行逻辑 #

1. 自连接的本质：把一张表当两张用 #

SQL里给activity表分别起了别名a1和a2，相当于我们把完全相同的两份原表放在一起做关联，可以简单理解为a1是「当前统计的基准行」，a2是「用来累加的历史行」。

2. 连接条件的作用 #

连接的两个限制条件是并行生效的：

• a1.player_id = a2.player_id：只把同一个玩家的行关联起来，不同玩家的数据不会互相干扰
• a1.event_date >= a2.event_date：对于a1里的每一行，只关联a2里同一个玩家日期小于等于当前a1行日期的所有行

3. 具体数据演示关联过程 #

我们以玩家ID=1的三行数据为例，关联后得到的中间结果如下：

4. 分组求和的作用 #

接下来执行group by a1.player_id, a1.event_date，就是把a1玩家ID和日期相同的行合并为一组，对每组的a2.games_played做求和：

• 第一组（a1.player_id=1，a1.event_date=2016-03-01）：sum(5) = 5
• 第二组（a1.player_id=1，a1.event_date=2016-05-02）：sum(5+6) = 11
• 第三组（a1.player_id=1，a1.event_date=2017-06-25）：sum(5+6+1) = 12
  玩家ID=3的计算逻辑完全相同，最终就能得到期望的输出结果。

补充说明 #

这种写法是窗口函数普及之前，计算累计求和的经典实现方式，现在更简洁的等价写法是用开窗函数：

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

两种写法的运行逻辑本质是一致的。

内容的提问来源于stack exchange，提问作者user_12