在MySQL中计算累计总数

Question

我有这个MySQL查询：

SELECT DAYOFYEAR(`date`)  AS d, COUNT(*) 
FROM  `orders` 
WHERE  `hasPaid` > 0
GROUP  BY d
ORDER  BY d

它返回类似于这样的内容：

d  | COUNT(*) |
20 |  5       |
21 |  7       |
22 | 12       |
23 |  4       |

我真正想要的是在最后添加一列来显示累计总数：

d  | COUNT(*) | ??? |
20 |  5       |   5 |
21 |  7       |  12 |
22 | 12       |  24 |
23 |  4       |  28 |

这可行吗？

- nickf

---

可能是在MySQL中创建累积总和列的重复问题。 - Ztyx

@Ztyx，你所链接的问题是在一年之后提出的。因此，情况应该相反。 - Olaf Dietsche

Answer 1

MySQL警告：在表达式中设置用户变量已被弃用，并将在将来的版本中移除。

也许对您来说，有一个更简单的解决方案，可以避免数据库执行大量查询。这个解决方案只执行一次查询，然后在单次遍历中对结果进行一些数学运算。

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(`date`) AS d,
       COUNT(*) AS c
    FROM  `orders`
    WHERE  `hasPaid` > 0
    GROUP  BY d
    ORDER  BY d) AS q1

这将为您提供一个额外的RT（累计运行）列。不要忘记在顶部设置语句来首先初始化累计运行变量，否则您将只得到一个NULL值的列。

- Autosoft

---

这个工作得非常出色！查看此操作的 EXPLAIN 显示其比先前接受的答案更有效率。 - nickf

关键是使用子查询。这使得在涉及多个表或聚合的复杂查询中变得可靠。 - Álvaro González

对于那些想要使用PHP的基本MySQL函数来完成这样操作的人，请确保先单独运行第一行（但仍在第二行之前）。 - user778005

Ariel，请提供一个可行的例子作为回复，而不是留下一个飞过式的评论。很想看看你的建议是如何工作的！ - Andrew Hedges

值得一提的是，@rentot变量可以在FROM子句中的SELECT语句中直接设置，如下所示：... AS q1, (SELECT @runtot:=0) AS n。这可能会让PHP开发人员的工作更加轻松，因为现在只需要一个语句查询即可。 - peterm

难道不是两次遍历：每个SELECT语句一次吗？由于使用了GROUP by DAYOFYEAR(date)，临时/中间表中的行数是有限制的。 - PaulH

Answer 2

SELECT 
   DAYOFYEAR(O.`date`)  AS d, 
   COUNT(*),
   (select count(*) from `orders` 
       where  DAYOFYEAR(`date`) <= d and   `hasPaid` > 0)
FROM  
  `orders` as O
WHERE  
  O.`hasPaid` > 0
GROUP  BY d
ORDER  BY d

这将需要一些语法调整（我没有MySQL来测试它），但它向您展示了这个想法。子查询只需回到并添加您已经包含在外部查询中的所有内容，它必须为每一行执行这个操作。

请参阅此问题以了解如何使用联接完成相同的操作。

针对与不断增长的数据导致性能降低的担忧：由于一年最多有366天，并且我假设您不会针对多年运行此查询，子查询将被评估最多366次。如果日期和hasPaid标志具有适当的索引，则可以正常运行。

- cdonner

---

请注意，在大型、中等大小和一些小型数据库上，这个操作将非常缓慢，因为它需要执行与结果行数相同数量的额外查询。 - Sergej Andrejev

同意。我+1了这个答案，因为它很聪明，当我们需要时，我们都使用过这样的解决方案，但我们也都知道这是有代价的。这取决于您需要运行计数的位置。对于业务逻辑？那么可能在数据库中执行此操作。对于视图？在代码中执行。 - Jarret Hardie

Answer 3

从MySQL 8开始，您将使用窗口函数来进行此类查询：

SELECT dayofyear(`date`) AS d, count(*), sum(count(*)) OVER (ORDER BY dayofyear(`date`))
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d

在上面的查询中，聚合函数 count(*) 嵌套在窗口函数 sum(..) OVER (..) 内部，这是因为在 SQL 中有着逻辑操作顺序的存在。如果您感到困惑，可以轻松地使用派生表或WITH 子句来更好地构造您的查询结构：

WITH daily (d, c) AS (
  SELECT dayofyear(`date`) AS d, count(*)
  FROM `orders`
  WHERE `hasPaid` > 0
  GROUP BY d
)
SELECT d, c, sum(c) OVER (ORDER BY d)
ORDER BY d

- Lukas Eder

Answer 4

可以使用MySQL中的临时表来计算运行余额。以下查询应该有效：

CREATE TEMPORARY table orders_temp1 (SELECT id, DAYOFYEAR(`date`)  AS d, COUNT(*) as total FROM  `orders` WHERE  `hasPaid` > 0 GROUP BY d ORDER  BY d);
CREATE TEMPORARY table orders_temp2 (SELECT * FROM orders_temp1);
SELECT d, total, (SELECT SUM(t2.total) FROM orders_temp2 t2 WHERE t2.id<=t1.id) as running_total FROM orders_temp1 t1;

一个临时表用于组织查询。请注意，临时表仅存在于连接到MySQL服务器的持续时间内。

上述查询使用子查询，该子查询返回临时表中所有行的余额，包括当前行在内。将余额分配给实际表中的当前行。

- Nadir Latif

---

该查询使用存储在内存中的临时表。它们比基于磁盘的表快得多。但是，如果您的表有大量数据或查询将由多个用户同时执行，则使用临时表会使查询变慢。请参见：https://dev59.com/BmMk5IYBdhLWcg3w5R-k - Nadir Latif

Answer 5

我认为这是不可能的，每一行结果都应该是独立的。使用编程语言来获取这些值。

- Sergej Andrejev

---

考虑到关系数学的本质，以及您正在使用group by，即使mysql有一些技巧可以实现这一点，按照Sergej的建议，在编程语言中完成会更简单明了。 - Jarret Hardie

我不同意。从重用和维护的角度来看，在数据库和应用层之间分离处理任务是有问题的。如果你想在不同的地方使用这些数据，比如在报告和屏幕上，你就需要复制运行总计逻辑。 - cdonner

+1 你说得对：在编程逻辑中这会更容易和更好 - 我试图看看是否有一些神奇的超棒函数可以做到这一点。 - nickf

一个运行总计列不是视图逻辑，而是嵌入在表中的数据。是的，SQL是关系集合逻辑，但几乎总是有序的。暗示这是不可能的是不正确的。暗示它是不好的形式需要讨论。 - Brendan

这在现代数据库管理系统中实际上非常容易（使用窗口函数），远非“不可能”。 - user330315