每个月每个唯一id的累计总和

s5a0g9ez 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(365)

我试图计算从所有客户的累计利润，我从这里找到一个非常好的参考。但是，我的表包含多个客户，这些客户应该有自己的累计利润。下面是我已有的“客户”表。

| Year | Month | id  | profit | cumulative |
| 2017 |   1   | 123 |  1000  |            |
| 2017 |   2   | 123 |  -200  |            |
| 2017 |   3   | 123 |  500   |            |
| 2017 |   1   | 456 |  500   |            |
| 2017 |   2   | 456 |  100   |            |
| 2017 |   3   | 456 |  200   |            |

如果我使用这样的sql代码：

SET @csum := 0;
UPDATE client
SET cumulative = (@csum := @csum + profit);

我得到的结果是这样的：

| Year | Month | id  | profit | cumulative |
| 2017 |   1   | 123 |  1000  |    1000    |
| 2017 |   2   | 123 |  -200  |    800     |
| 2017 |   3   | 123 |  500   |    1300    |
| 2017 |   1   | 456 |  500   |    1800    |
| 2017 |   2   | 456 |  100   |    1900    |
| 2017 |   3   | 456 |  200   |    2100    |

我期望得到的是：

| Year | Month | id  | profit | cumulative |
| 2017 |   1   | 123 |  1000  |    1000    |
| 2017 |   2   | 123 |  -200  |    800     |
| 2017 |   3   | 123 |  500   |    1300    |
| 2017 |   1   | 456 |  500   |    500     |
| 2017 |   2   | 456 |  100   |    600     |
| 2017 |   3   | 456 |  200   |    800     |

我试着把它分为年，月，和身份证，但它不起作用。基本上，我想要每个月每个客户的累计金额。你知道怎么解决这个问题吗？提前谢谢。

sql mysql sum cumulative-sum

来源：https://stackoverflow.com/questions/48337711/cumulative-sum-for-each-unique-id-in-every-month

3条答案

按热度按时间

a0x5cqrl1#

我会避免使用局部变量，因为结果有时可能与预期的不同，而且基于集合的方法可以通过dbms更好地进行优化。改用子查询或自联接：

SELECT c1.*,
       (SELECT SUM(c2.profit)
        FROM client c2
        WHERE (c2.year < c1.year or 
              (c2.year = c1.year and c2.month <= c1.month)) and 
              c2.id = c1.id
       ) AS cumulative_sum
FROM TABLE client c1

因此在 update 它可以像这样

UPDATE client
JOIN
(
   SELECT c1.id, c1.year, c1.month,
       (SELECT SUM(c2.profit)
        FROM client c2
        WHERE (c2.year < c1.year or 
              (c2.year = c1.year and c2.month <= c1.month)) and 
              c2.id = c1.id
       ) AS cumulative_sum
   FROM client c1
) t ON client.id = t.id and
     client.year = t.year and
     client.month = t.month
SET cumulative = t.cumulative_sum

sqlfiddle演示（感谢@johnwoo提供数据）

赞(0）回复(0）举报 2021-06-21

jhiyze9q2#

局部变量只能在查询中与order by一起正常工作。

SET @csum := 0, @id:=NULL;
UPDATE client
   SET cumulative = (@csum := if(id=@id,@csum,0) + profit), id=(@id:=id)
ORDER BY id, year, month;

sqlfiddle.com上的示例
或者更简短地说： ... SET cumulative = (@csum := if(id=@id, @csum, 0*(@id:=id) ) + profit) . 这个comare stored id与当前id相同，如果id相同，则返回stored sum，如果id不同，则返回0（并存储新id）。

赞(0）回复(0）举报 2021-06-21

dpiehjr43#

您可以使用变量来实现这一点，但是您需要非常小心。使用变量时，您希望所有操作都在一条语句中--因为mysql不保证语句的求值顺序：

SET @csum := 0;
SET @id := -1;

UPDATE client c
    SET cumulative = (CASE WHEN @id = id
                           THEN @csum := @csum + profit
                           WHEN @id := id
                           THEN @csum := profit
                           ELSE @csum := profit
                      END)
    ORDER BY id, year, month;

赞(0）回复(0）举报 2021-06-21

我来回答

每个月每个唯一id的累计总和

3条答案

相关问题

热门标签

最新问答