在mysql中获取运行总记录随时间的增长

Question

我有一个Drupal站点，其中有一个跟踪用户的表。我想做的是绘制会员人数随时间增长的图表。我想让mysql返回如下内容:

date | # of users (total who have registered up to the given date)
1/1/2014 | 0
1/2/2014 | 2
1/3/2014 | 10

其中'# of users'是截至给定日期(running-total)已注册帐户的用户总数，而不是在特定日期注册的用户数量(检索起来很简单)。

我的{users}表的每一行都有一个uid列、一个name列和一个created(时间戳)列。

因此，{users}表中的一个示例记录将是:

name: John Smith
uid: 526
created: 1365844220

Answer 1

尝试:

select u.created, count(*)
from (select distinct date(created) created from `users`) u
join `users` u2 on u.created >= date(u2.created)
group by u.created

SQLFiddle。

Answer 2

我最终使用了一个结合变量的解决方案，基于Stack Overflow的答案张贴在这里。这个解决方案似乎比提供的其他答案更灵活、更有效。

  SELECT u.date,
  @running_total := @running_total + u.count AS count
  FROM (
    SELECT COUNT(*) AS count, DATE_FORMAT(FROM_UNIXTIME(created), '%b %d %Y') AS date
    FROM {users}
    WHERE created >= :start_time AND created <= :end_time
    GROUP BY YEAR(FROM_UNIXTIME(created)), MONTH(FROM_UNIXTIME(created)), DAY(FROM_UNIXTIME(created))
  ) u
  JOIN (
    SELECT @running_total := u2.starting_total
    FROM (
      SELECT COUNT(*) as starting_total
      FROM {users}
      WHERE created < :start_time
    ) u2
  ) initialize;

请注意，分组时间、日期格式和范围需求只是我的特定项目的细节。这个解决方案的一个更通用的形式(根据原始问题)是:

  SELECT u.date,
  @running_total := @running_total + u.count AS count
  FROM (
    SELECT COUNT(*) AS count, DATE(FROM_UNIXTIME(created)) AS date
    FROM {users}
    GROUP BY date
  ) u
  JOIN (
    SELECT @running_total := 0
  ) initialize;

Answer 3

不了解表结构，所以根据需要调整查询

SELECT DATE(created), COUNT(*) AS Users FROM users GROUP BY DATE(created)

当您只想显示已注册用户的日期时，添加

HAVING COUNT(*) > 0

在查询结束时