在MySQL中计算运行总数

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?

这是可能的吗?

6 个解决方案

#1

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语句来初始化正在运行的total变量，否则您将得到一列空值。

#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

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 question for 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.

用不断增长的数据处理对性能退化的关注:因为有max。一年有366天，我假设您没有对这个查询运行多年，子查询的计算次数将达到366次。有了合适的日期索引和hasPaid标志，您就可以了。

#3

I would say that this is impossible every resulting row should be independent. Use programming language for getting these values

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

#4

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中执行此操作，否则我将用生成查询的编程语言对结果进行求和。这样的嵌套会随着表的增长而变得非常缓慢。

#5

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

您可以使用交叉连接语句或一些slef连接来破解它，但是对于任何大型数据集，它都将变得很慢，所以最好在post查询处理器中完成;在客户端代码中游标。

#6

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
是在MySql之外还是?
Use MySql 5 Cursors
使用MySql 5游标

#1