gpt4 book ai didi

Mysql累计和,带重置条件,按时间段分组

转载 作者:行者123 更新时间:2023-11-30 22:10:44 27 4
gpt4 key购买 nike

我有一个表,每行包含名为 id(键)、日期、符号、值的字段。

Sign = 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 ;

我在 SUM 函数内部使用就地赋值 (@cum:=value) 在分组时相应地更改 @cum,并将它乘以 0 不干扰实际总和,这将对 sign*value 求和正常情况下,当找到重置条件时,将减去@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:=@cum+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累计和,带重置条件,按时间段分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40190817/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com