Mysql的累计总和，与复位条件，按期限组

问题描述：

我有一个表，每行持有名为id（键），日期，符号，值的字段。Mysql的累计总和，与复位条件，按期限组

符号= 0是重置条件。它将用于设置初始累积值

Sign = +1是一个添加条件。它会将价值累计到累计总额。

Sign = -1是一个减法，正如你猜测的那样，它会减少累计总数。

顺序很重要，所以它们必须用0,1,1顺序进行评估。

比方说，我想要获得价值的累计总和。

SELECT my_date, my_sign, my_value, @cum AS cum_before, 
(@cum := IF(my_sign !=0, @cum + my_sign * my_value, my_value)) AS cum_after 
FROM my_table, (SELECT @cum :=0) as t WHERE my_date LIKE '2016-05-%' 
ORDER BY my_date, my_sign + (my_sign =0) *2 DESC;

将正确显示：

my_date  my_sign my_value cum_before cum_after 
2016-05-02  0 10000.00   0 10000.00 
2016-05-02  1 1860.00  10000 11860.00 
2016-05-02  -1 1860.00  11860 10000.00 
2016-05-03  1 1780.00  10000 11780.00 
2016-05-06  1 4625.00  11780 16405.00 
2016-05-09  1 14200.00  16405 30605.00

现在我想通过周（或月）基时，它，并且具有cum_before组中处理的行之前，设定为初始值（顺便应该是前一组的cum_after），cum_after作为处理组中行后的累积值。事情变得复杂，因为之前的状态变量@cum似乎用组集的第一个值初始化。

我正在创建一个临时表来保持正确的顺序，因为GROUP似乎不遵守任何ORDER BY子句（我猜它正在取出行，因为它们出现在数据库中）。

CREATE TEMPORARY TABLE _t_ SELECT id FROM my_table 
ORDER BY my_date, my_sign + (my_sign =0) *2 DESC ;

我使用分配到位（@cum：=值），SUM函数内，分组时相应地改变@cum，和由0乘以不与真实的总和干扰，这将总结在正常情况下标记*值，当找到复位条件时，将减去@cum和增加值字段。

SELECT min(my_date) AS MinDate, max(my_date) AS MaxDate, 
@cum AS cum_before, SUM( 
    0 * (@cum := IF(my_sign !=0, my_sign * my_value, my_value)) + 
    IF(my_sign !=0, my_sign * my_value, - @cum + my_value) 
) AS cum_after 
FROM my_table as F, _t_, (SELECT @cum :=0) AS t 
WHERE _t_.id = F.id AND my_date LIKE '2016-05-%' 
GROUP BY date_format(my_date, "%y%U");

将为下列：

MinDate  MaxDate  cum_before cum_after 
2016-05-02 2016-05-06  10000 16405.00 
2016-05-09 2016-05-09  14200 14200.00

这是不对的，因为我所期望获得的是：

MinDate  MaxDate  cum_before cum_after 
2016-05-02 2016-05-06   0 16405.00 
2016-05-09 2016-05-09  16405 30605.00

基本上它似乎分配@cum第一行中的设置，而不是保留以前的变量值。

如何才能获得正确的分组？

答

适应一个previous answer通过Haleemur Ali 看来它不能在单一步骤中进行，如累积变量每组之后复位。因此，一个可能的解决方案是：

SELECT MinDate, MaxDate, @cum as cum_before, (@cum:[email protected]+tmp_cum) as cum_after 
FROM (SELECT min(my_date) AS MinDate, max(my_date) AS MaxDate, 
SUM( 
    0 * (@tmp := IF(my_sign !=0, my_sign * my_value, my_value)) + 
    IF(my_sign !=0, my_sign * my_value, - @tmp + my_value) 
) AS tmp_cum 
FROM my_table as F, _t_, (SELECT @tmp :=0) AS t 
WHERE _t_.id = F.id AND my_date LIKE '2016-05-%' 
GROUP BY date_format(my_date, "%y%U")) as SUBQ, (SELECT @cum:=0) as tmp_var;

不知道性能，或者这是否可以被优化。

Mysql的累计总和，与复位条件，按期限组

相关推荐