（超详细一文看懂）MySQL累计求和问题及窗口函数order by的原理

数据表Activity:

每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0 ）。
编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。

查询结果Result:

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

其实就是累加问题。

1、使用表连接的方法

• step1：自连接

select *
from activity a1
left join activity a2 on a1.player_id=a2.player_id;

结果：

图中是我们想要达到的结果，id=1的用户，2016-03-01，要得到5；2016-5-2，要得到5和6；2017-6-25，要得到5、6、1……，所以要加上筛选条件：a1.event_date>=a2.event_date，即：

select *
from activity1 a1
left join activity1 a2 on a1.player_id=a2.player_id
where a1.event_date>=a2.event_date;

• step2：分组聚合
  观察上表，要以左表（a1）的player_id和event_date为分组，计算右表（a2）的games_played的和，就是分组计算，不过分组依据和计算目标别弄错就行。

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

这样就得到了结果。

2、使用窗口函数

在这之前，除了使用排名函数rank(),dense_rank(),row_number()，其他时候没太关注过窗口函数的运行逻辑，这次借着这个机会好好试了试，其实逻辑也很简单。

窗口函数格式：聚合函数 over (partition by column_name order by column_name)
也就是说重要的参数有两个：

• partition by：执行分组功能
• order by：表面是排序功能，实际是累计功能！！！
  再强调一遍：order by 表面是排序功能，实际是累计功能！

order by经常被用来和partition by连用，计算名次，可实际是累计作用的，仔细想想可以发现，计算组内名次不就是在累计吗，每个人的名次都是综合他之前所有人的名次基础上得来的。

所以当一些聚合函数（sum、avg、min、max等）和窗口函数连用的时候，order by 就是起累计作用的。

还是从例子来看吧：
还是以上面的那个表为例：
加上order by：

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

很简单就能得到累加结果，这说明加上order by之后，就在组内（player_id）按照日期进行累加。

不加order by：

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

发现只是在组内进行求和，没有涉及按照日期累加，日期此时就是摆设。

3、其他例子

除了求累加和，当然也可以使用其他聚合函数，这里也举举例子，加深印象。

max()：截止目前的最大值。

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

avg()：截止目前的平均值。

select player_id, event_date,
avg(games_played) over (partition by player_id order by event_date) as games_played_so_far
from activity1 order by player_id,event_date; 

其他的不再赘述。
文中若有错误，欢迎您留言指出。

总结一句话：在窗口函数里面，order by表面是排序，实际是求累计！