在 MySQL 中计算运行总数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/664700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate a running total in MySQL
提问by nickf
I have this MySQL query:
我有这个 MySQL 查询：
SELECT DAYOFYEAR(`date`) AS d, COUNT(*)
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d
Which returns something like this:
它返回如下内容：
d | COUNT(*) |
20 | 5 |
21 | 7 |
22 | 12 |
23 | 4 |
What I'd really like is another column on the end to show the running total:
我真正想要的是最后的另一列来显示运行总数：
d | COUNT(*) | ??? |
20 | 5 | 5 |
21 | 7 | 12 |
22 | 12 | 24 |
23 | 4 | 28 |
Is this possible?
这可能吗？
回答by Autosoft
Perhaps a simpler solution for you and prevents the database having to do a ton of queries. This executes just one query then does a little math on the results in a single pass.
也许对您来说是一个更简单的解决方案，并可以防止数据库进行大量查询。这仅执行一个查询，然后在一次传递中对结果进行一些数学运算。
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
This will give you an additional RT (running total) column. Don't miss the SET statement at the top to initialize the running total variable first or you will just get a column of NULL values.
这将为您提供一个额外的 RT（运行总计）列。不要错过顶部的 SET 语句以首先初始化运行总计变量，否则您只会得到一列 NULL 值。
回答by cdonner
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
This will require some syntactical tuning (I don't have MySQL to test it), but it shows you the idea. THe subquery just has to go back and add up everything fresh that you already included in the outer query, and it has to do that for every row.
这将需要一些语法调整（我没有 MySQL 来测试它），但它向您展示了这个想法。子查询只需要返回并添加您已经包含在外部查询中的所有新内容，并且它必须对每一行都这样做。
Take a look at this questionfor how to use joins to accomplish the same.
看看这个问题，了解如何使用连接来完成同样的任务。
To address concerns about performance degradation with growing data: Since there are max. 366 days in a year, and I assume that you are not running this query against multiple years, the subquery will get evaluated up to 366 times. With proper indices on the date and the hasPaid flag, you'll be ok.
为了解决随着数据增长而导致性能下降的担忧：因为有最大。一年 366 天，我假设您没有针对多年运行此查询，子查询将被评估多达 366 次。有了正确的日期索引和 hasPaid 标志，你就可以了。
回答by Lukas Eder
Starting with MySQL 8, you will be using window functionsfor this kind of query:
从 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
In the above query, the aggregate function count(*)is nested inside of the window function sum(..) OVER (..), which is possible because of the logical order of operations in SQL. If that's too confusing, you can easily resort to using a derived table or a WITHclauseto better structure your query:
在上面的查询中，聚合函数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
回答by Nadir Latif
It is possible to calculate a running balance using a temporary table in MySQL. The following query should work:
可以使用 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;
A temporary table is used for organizing the query. Note that a temporary table only exists for the duration of the connection to the MySQL server
临时表用于组织查询。注意临时表只存在于连接 MySQL 服务器的持续时间内
The above query uses a sub query, which returns balance of all rows in the temporary table upto and including the current row. The balance is assigned to the current row in the actual table
上面的查询使用了一个子查询，它返回临时表中直到并包括当前行的所有行的余额。余额分配给实际表中的当前行
回答by Sergej Andrejev
I would say that this is impossible every resulting row should be independent. Use programming language for getting these values
我会说这是不可能的，每个结果行都应该是独立的。使用编程语言获取这些值
回答by Sam
Unless you have no other option but doing this in sql, I'd sum the results in the programming language that is making the query. A nesting like this will became very slow as the table grows.
除非您别无选择，只能在 sql 中执行此操作，否则我会使用进行查询的编程语言对结果求和。随着表的增长，这样的嵌套将变得非常缓慢。
回答by TFD
You can hack this using the Cross Join statement or some slef joins but it will get slow with any large data sets so probably best done in a post query processor; either cursor of in client code
您可以使用 Cross Join 语句或一些 slef join 来破解它，但它会因任何大型数据集而变慢，因此最好在后查询处理器中完成；客户端代码中的任一游标
回答by Sam Saffron
This is one of the only places where cursors are faster than a set based queries, if performance is critical I would either
这是游标比基于集合的查询更快的唯一地方之一，如果性能至关重要，我要么
- Do this outside of MySql or
- Use MySql 5 Cursors
- 在 MySql 之外执行此操作或
- 使用 MySql 5 游标
