MySQL按月汇总，运行总计 [英] MySQL aggregate by month with running total

问题描述

可能重复:
计算MySQL中的运行总数

Possible Duplicate:
Calculate a running total in MySQL

我正在监视一个应用程序自2011年以来每月使用MySQL和PHP创建的用户数量.作为查询的一部分，我还希望包括运行总计.

I'm monitoring the number of users created since 2011 in an application by month for a chart using MySQL and PHP. As a part of the query I would also like to include a running total.

SELECT
  DATE_FORMAT(created,'%Y%m%d') as 'Date',
  COUNT(item_id) as 'NewUsers'
FROM AP_user
WHERE YEAR(created) > 2011
  AND user_groups = '63655'
  AND user_active = 1
  AND userID NOT IN $excludedUsers
GROUP BY MONTH(created) ASC

我可以按月返回用户数"，但是如何在此查询中包括运行总计呢?

I'm able to return the "users by month" but how do I include a running total as a part of this query?

推荐答案

不幸的是，MySQL不提供分析功能，就像Oracle和SQL Server一样.

Unfortunately, MySQL doesn't provide analytic functions, like Oracle and SQL Server do.

获得运行总计"的一种方法是利用用户变量，如下所示:

One way to get a "running total" is to make use of a user variable, something like this:

  SELECT t.Date
       , t.NewUsers
       , @rt := @rt + t.NewUsers AS `Running Total`
    FROM (SELECT @rt := 0) i
    JOIN (
           SELECT DATE_FORMAT(created,'%Y%m%d') AS `Date`
                , COUNT(item_id) as `NewUsers`
             FROM AP_user
            WHERE YEAR(created) > 2011
              AND user_groups = '63655'
              AND user_active = 1
              AND userID NOT IN $excludedUsers
            GROUP BY DATE_FORMAT(created,'%Y-%m')
            ORDER BY DATE_FORMAT(created,'%Y-%m') ASC
         ) t

注意:在这种情况下，不能保证上面使用的存储变量的行为.但是，如果我们对查询谨慎，我们可以在SELECT语句中获得可预测的，可重复的结果.内存变量的行为可能在将来的版本中更改，从而使此方法行不通.

NOTE: The behavior of memory variables like used above is not guaranteed in this context. But if we are careful with the query, we can get predictable, repeatable results in SELECT statements. The behavior of memory variables may change in a future release, rendering this approach unworkable.

注意:我基本上将查询括在括号内，并给它一个别名作为内联视图(MySQL称之为派生表").我对您的查询进行了一些更改，您的GROUP BY可以将2012年1月和2013年1月组合在一起，我对此进行了更改.我还添加了ORDER BY子句.

NOTE: I basically wrapped your query in parentheses, and gave it an alias as an inline view (what MySQL calls a "derived table"). I made a few changes to your query, your GROUP BY has potential to group January 2012 together with January from 2013, I changed that. I also added an ORDER BY clause.

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