mysql - 基于行计算滚动总和

Question

我正在尝试根据日期时间行计算当前行与前 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

我们可以按日期时间合并重量和体积的前4个值，然后+ 将它们与各自的当前值进行比较

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;