MySQL的运行总数为COUNT [英] MySQL Running Total with COUNT

问题描述

我知道set @running_sum=0; @running_sum:=@running_sum + ...方法，但是，对于我来说，它似乎不起作用.

I'm aware of the set @running_sum=0; @running_sum:=@running_sum + ... method, however, it does not seem to be working in my case.

我的查询:

SELECT DISTINCT(date), COUNT(*) AS count 
   FROM table1
   WHERE date > '2011-09-29' AND applicationid = '123'
   GROUP BY date ORDER BY date

结果为我提供了唯一的日期，以及应用程序123的出现次数.

The result gives me unique dates, with the count of occurrences of application 123.

我想保持count的总运行量，以查看累积的增长.

I want to keep a running total of the count, to see the accumulated growth.

现在我正在用PHP进行此操作，但我想将其全部切换到MySQL.

Right now I'm doing this in PHP, but I want to switch it all to MySQL.

使用本文第一行中的方法只是复制计数，而不是累加计数.

Using the method from the first line of this post simply duplicates the count, instead of accumulating it.

我想念什么?

P.S.集合很小，只有大约100个条目.

P.S. The set is very small, only about 100 entries.

您对ypercube:

you're right ypercube:

这是带有running_sum的版本:

Here's the version with running_sum:

SET @running_sum=0;
SELECT date, @running_sum:=@running_sum + COUNT(*) AS total FROM table1
   WHERE date > '2011-09-29' AND applicationid = '123'
   GROUP BY date ORDER BY date

count列最终与我刚刚打印的COUNT(*)相同

count column ends up being the same as if I just printed COUNT(*)

推荐答案

更新后的答案

OP要求使用单查询方法，这样就不必将用户变量与使用变量来计算运行总计分开设置:

Updated Answer

The OP asked for a single-query approach, so as not to have to SET a user variable separately from using the variable to compute the running total:

SELECT d.date,
       @running_sum:=@running_sum + d.count AS running
  FROM (  SELECT date, COUNT(*) AS `count`
            FROM table1
           WHERE date > '2011-09-29' AND applicationid = '123'
        GROUP BY date
        ORDER BY date ) d
  JOIN (SELECT @running_sum := 0 AS dummy) dummy;

用户变量的内联初始化"对于模拟其他分析功能也很有用.确实，我从像这样的答案中学到了这项技术.

"Inline initialization" of user variables is useful for simulating other analytic functions, too. Indeed I learned this technique from answers like this one.

您需要引入一个封闭查询，以将COUNT(*)条记录中的@running_sum制成表格:

You need to introduce an enclosing query to tabulate the @running_sum over your COUNT(*)ed records:

SET @running_sum=0;
SELECT d.date,
       @running_sum:=@running_sum + d.count AS running
  FROM (  SELECT date, COUNT(*) AS `count`
            FROM table1
           WHERE date > '2011-09-29' AND applicationid = '123'
        GROUP BY date
        ORDER BY date ) d;

另请参见此答案.

这篇关于MySQL的运行总数为COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！