mysql - 建立基于SUM的每日记录

Question

我在构建聚合函数时遇到问题。这是我的问题:

我有一张这样的 table

 id action day         isSum difference
 1  ping   2012-01-01  1     500         (this is the sum of the differences from last year)
 2  ping   2012-01-01  0     -2
 3  ping   2012-01-02  0      1
 4  ping   2012-01-03  0     -4
 5  ping   2012-01-04  0     -2
 6  ping   2012-01-05  0      3
 7  ping   2012-01-06  0      2
 8  ping   2012-01-01  1      0         (this is the sum of the differences from last year, now for pong)
 9  pong   2012-01-01  0     -5
 10  pong   2012-01-02  0      2
 11  pong   2012-01-03  0     -2
 12  pong   2012-01-04  0     -8
 13  pong   2012-01-05  0      3
 14  pong   2012-01-06  0      4

我现在需要为每一天选择操作、日期和自 01-01 以来的汇总差异，以便我的结果如下所示

action day        total      
ping   2012-01-01 498
ping   2012-01-02 499
ping   2012-01-03 495
ping   2012-01-04 493
ping   2012-01-05 496
ping   2012-01-06 498
pong   2012-01-01 - 5
pong   2012-01-02 - 3
pong   2012-01-03 - 5
pong   2012-01-04 -13
pong   2012-01-05 -10
pong   2012-01-06 - 6

我该怎么做？

有很多数据集(~100 万)，因此查询需要非常便宜。我不知道如何根据操作列使用 sum 来获取每日记录的每日总和。

Answer 1

您需要使用 SUBQUERY得到总场。

select action, day, 
 (select sum(difference) from x 
   where action = t2.action and day <= t2.day 
   group by action) as total
from x t2 group by action ,day

查看SQLFIDDLE