基于行计算累计和

Question

我正在尝试根据DateTime行计算当前行和前4行之间的两列“权重”和“体积”的滚动总和。这些是大量的日志，有时我们会收到多个负载。因此，基本上，每次新的负载进入时，它都会根据自身和前4行计算一个新的滚动总和。我使用的是MySQL 5.7。请记住，每一行都不是新日期。数据应该是这样的……

         DateTime       | Weight | Volume | Roll_Weight | Roll_Volume
    2019-01-01 08:00:00 | 54000  | 72.2   |   54000     |   72.2
    2019-01-01 09:30:00 | 29000  | 38.0   |   83000     |  110.2
    2019-01-05 13:00:00 | 38900  | 54.8   |  118900     |  165.0 
    2019-01-06 07:00:00 | 44000  | 56.2   |  162900     |  221.2
    2019-01-06 12:30:00 | 49000  | 18.0   |  211900     |  239.2
    2019-01-07 09:00:00 | 27900  | 84.5   |  185800     |  251.5
    2019-01-10 08:00:00 | 94000  | 72.6   |  250800     |  286.1
    2019-01-10 13:30:00 | 65000  | 39.7   |  286800     |  271.0
    2019-01-10 15:00:00 | 38900  | 50.5   |  274800     |  265.3

Select DateTime, Sum(Weight), Sum(Volume) over (Partition by DateTime ORDER BY DateTime asc 4 preceding)
From t1

我知道，因为我使用的是MySQL 5.7，所以我没有能力使用OVER，但这似乎是我需要的。解决这个问题的最好方法是什么？我找到的所有资源的每一行都有不同的日期，所以它们只在日期上使用自连接查询，但我不能这样做，因为我是按行滚动的。

Answer 1

我们可以通过DateTime对Weight和的前4个值进行COALESCE，然后使用各自的当前值对它们进行+

SELECT `DateTime`, Weight, Volume,
   Weight + 
   COALESCE((SELECT Weight 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `Date` DESC LIMIT 0, 1), 0) +
   COALESCE((SELECT Weight 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `Date` DESC LIMIT 1, 1), 0)+
   COALESCE((SELECT Weight 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `DateTime` DESC LIMIT 2, 1), 0)+
   COALESCE((SELECT Weight 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `DateTime` DESC LIMIT 3, 1), 0) AS RollWeight,

   Volume + 
   COALESCE((SELECT Volume 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `Date` DESC LIMIT 0, 1), 0) +
   COALESCE((SELECT Volume 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `Date` DESC LIMIT 1, 1), 0)+
   COALESCE((SELECT Volume 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `DateTime` DESC LIMIT 2, 1), 0)+
   COALESCE((SELECT Volume 
             FROM t1 AS t2
             WHERE t2.`DateTime` < t3.`DateTime`
             ORDER BY `DateTime` DESC LIMIT 3, 1), 0) AS RollVolume

FROM t1 AS t3;