mysql - 使用 group by 获取累计和

Question

我必须从表中计算累积总和。我从论坛检查了以下问题。 Create a Cumulative Sum Column in MySQL

Php Script to Calculate Cumulative Totals for Accounts

但我无法为此创建更正的查询。

我试过下面的查询。

SET @CumulativeSum := 0;

SELECT ItemId,
       sum(Quantity) Quantity,
       @CumulativeSum = @CumulativeSum + sum(Quantity) AS CumulativeSum,
       weekofyear(UploadedIn) WeekNo
  FROM testtbl
GROUP BY ItemId, weekofyear(UploadedIn)
ORDER BY ItemId, weekofyear(UploadedIn);

下面是示例数据

CREATE TABLE `testtbl` (
  `ItemId` int(11) DEFAULT NULL,
  `Quantity` int(11) DEFAULT NULL,
  `UploadedIn` datetime DEFAULT NULL
) ;


INSERT INTO  testtbl (1, 50, '01-01-2009 00:00:00'), 
(1, 25, '01-01-2009 00:00:00'), 
(1, 25, '01-01-2009 00:00:00'), 
(1, -100, '04-12-2009 16:34:33'), 
(1, 10, '19-09-2018 16:34:33'), 
(1, 10, '19-09-2018 16:40:47'), 
(1, -19, '19-09-2018 16:41:48'), 
(3, 1, '20-03-2013 16:49:47'), 
(3, 1, '20-03-2013 16:49:47'), 
(3, 1, '20-03-2013 16:49:47'), 
(3, -3, '21-03-2013 09:05:00'), 
(3, 3, '21-03-2013 09:05:01'), 
(3, -3, '05-04-2013 10:27:00'), 
(3, 1, '25-04-2013 16:07:18'), 
(3, -1, '25-04-2013 16:07:34'), 
(3, 5, '29-04-2013 15:33:19'), 
(3, 25, '29-04-2013 15:40:18'), 
(3, 100, '29-04-2013 15:40:18'), 
(3, 10, '29-04-2013 15:40:18'), 
(3, 10, '29-04-2013 15:40:18'), 
(3, 20, '29-04-2013 15:40:18'), 
(3, 100, '29-04-2013 15:40:18'), 
(3, 10, '07-05-2013 07:21:49'), 
(3, 10, '07-05-2013 07:21:49'), 
(3, 5, '07-05-2013 07:21:49'), 
(3, 2, '07-05-2013 07:25:49'), 
(3, 6, '07-05-2013 07:32:05'), 
(3, 22, '07-05-2013 07:32:05'), 
(3, 10, '07-05-2013 07:32:05'), 
(3, 5, '07-05-2013 07:32:05'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 5, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 12, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 100, '07-05-2013 08:19:23'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 5, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 12, '07-05-2013 08:19:23'), 
(3, -682, '07-05-2013 09:33:02'), 
(3, 4, '08-05-2013 07:08:14'), 
(3, -4, '08-05-2013 09:41:29'), 
(3, 1, '08-05-2013 15:38:13'), 
(3, -1, '08-05-2013 15:54:35'), 
(3, 1, '30-05-2013 08:20:02'), 
(3, 2, '14-06-2013 15:23:02')

预期结果

ItemId  Quantity    CumulativeSum   WeekNo
1       100             100             1
1       1               101             38
1       -100            1               49
3       3               3               12
3       -3              0               14
3       0               0               17
3       270             270             18
3       -270            0               19
3       1               1               22
3       2               3               24

Answer 1

在早期版本的 MySQL 中，您可以使用相关子查询来处理此问题:

SELECT
    ItemId,
    WEEKOFYEAR(UploadedIn) WeekNo,
    SUM(Quantity) Quantity,
    (SELECT SUM(t2.Quantity) FROM testtbl t2
     WHERE WEEKOFYEAR(t2.UploadedIn) <= WEEKOFYEAR(t1.UploadedIn) AND
     t1.ItemId = t2.ItemId) AS CumulativeSum
FROM testtbl t1
GROUP BY
    ItemId,
    WEEKOFYEAR(UploadedIn)
ORDER BY
    ItemId,
    WEEKOFYEAR(UploadedIn);

Demo

在 MySQL 8+ 中，我们可以使用 SUM 作为解析函数:

SELECT
    ItemId,
    WEEKOFYEAR(UploadedIn) WeekNo,
    SUM(Quantity) Quantity,
    SUM(SUM(Quantity)) OVER (PARTITION BY ItemId
                             ORDER BY WEEKOFYEAR(UploadedIn)) AS CumulativeSum
FROM testtbl
GROUP BY
    ItemId,
    WEEKOFYEAR(UploadedIn)
ORDER BY
    ItemId,
    WEEKOFYEAR(UploadedIn);