在MySQL中计算累积列

Question

我试图创建累积列，但无法创建它，请看一下我尝试计算它的方式。

我的表格

create table mytable (
    TotalQuantity Decimal(7)
); 
insert into mytable (TotalQuantity) values
(0.0),
(0.0),
(1.0),
(0.0),
(2.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(1.0),
(1.0),
(0.0),
(1.0);

根据上述数据集，我想要计算不同的计数和累计总和。

SELECT TotalQuantity AS DistinctTotalQuantity,
       COUNT(TotalQuantity) AS COUNTVALUE, 
       @running_total := @running_total + COUNT(TotalQuantity) AS cumulative_sum
FROM mytable 
JOIN (SELECT @running_total := 0) r
GROUP BY TotalQuantity

- Juned Ansari

---

尝试查看https://dev59.com/yWcs5IYBdhLWcg3wp1z3，该问题与编程有关。 - davidchoo12

你正在合并两种方法。要么在子查询中执行分组操作和变量添加，在外部查询中执行，要么切换到仅使用其中一种方法。 - Strawberry

@davidchoo12 我怀疑原帖中的 MySQL 版本并不是最新的。 - Strawberry

此外，表格需要主键。 - Strawberry

Answer 1

也许符合标准的 ANSI 方式来进行累加求和是通过相关的子查询。在这里，我使用了一个视图，其中包含您的基本查询：

CREATE VIEW test AS
SELECT
    TotalQuantity AS DistinctTotalQuantity,
    COUNT(TotalQuantity) AS COUNTVALUE, 
FROM mytable 
WHERE StoreId = 210 AND ProdName = 'Tusq'
GROUP BY TotalQuantity

然后通过以下方式计算累计总数：

SELECT
    t1.DistinctTotalQuantity,
    t1.COUNTVALUE,
    (SELECT SUM(t2.COUNTVALUE) FROM test t2
     WHERE t2.DistinctTotalQuantity <= t1.DistinctTotalQuantity) AS cumulative_sum
FROM test t1;

使用会话变量，我们可以尝试：

SET @total = 0;
SELECT
    DistinctTotalQuantity,
    COUNTVALUE,
    (@total := @total + COUNTVALUE) AS cumulative_sum
FROM test;

- Tim Biegeleisen

---

看起来查询太昂贵了，我们不能使用变量来完成相同的操作吗？ - Juned Ansari

@JunedAnsari 如果你想要计算滚动总和，可以使用会话变量。但是这里的性能问题之一是需要在一个查询中进行聚合，然后再进行滚动求和。这不是最高效的情况。 - Tim Biegeleisen

Answer 2

以下查询将完美运行，不需要在您的查询/方法中进行太多更改。虽然我不确定性能如何：

SELECT TotalQuantity AS DistinctTotalQuantity,
       COUNT(TotalQuantity) AS COUNTVALUE, 
       (select @running_total := @running_total + count(TotalQuantity) from mytable m 
     where m.TotalQuantity = m1.TotalQuantity) cumulative_sum
    FROM (select *from mytable order by TotalQuantity) m1
    JOIN (SELECT @running_total := 0) r
    GROUP BY m1.TotalQuantity;

如果您想使用浮点数，请按以下方式声明列：

create table `mytable` (
    `TotalQuantity` Decimal (7,1)  //Here 1 is scale. It means 1 digit is allowed after decimal point.
)

这是我更新后的Sqlfiddle链接。

希望能对您有所帮助！

- Harshil Doshi

---

嗨，Harshil，如果值改变了，它就会出错。http://sqlfiddle.com/#!9/95166f/1 - Juned Ansari

你想将1.5视为单独的TotalQuantity进行计算吗？ - Harshil Doshi

答案已被接受，因此我认为它是有效的，因此更新了我的答案。 - Harshil Doshi

Answer 3

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;


set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from mytable
order by id;

- Ravi Chauhan