在mysql中检索运行总记录随时间的增长 [英] Retrieve running-total record growth over time in mysql
问题描述
我有一个Drupal网站,有一个表跟踪用户。我想做的是图形成员随时间的增长。所以我想按下mysql返回这样的:
I have a Drupal site which has a table that keeps track of users. What I want to do is graph membership growth over time. So I want to massage mysql into returning something like this:
date | # of users (total who have registered up to the given date)
1/1/2014 | 0
1/2/2014 | 2
1/3/2014 | 10
其中#个用户是已注册帐户的用户总数到指定日期(运行总数),而不是在该特定日期注册的用户数(这很容易检索)。
表中的 {users}
行有一个 uid
列,名称
列和创建的
(timestamp)列。
Each row of my {users}
table has a uid
column, a name
column, and a created
(timestamp) column.
因此,来自我的 {users}
表的示例记录将是:
So a sample record from my {users}
table would be:
name: John Smith
uid: 526
created: 1365844220
推荐答案
我最终使用了一个包含变量的解决方案,基于Stack Overflow回答发布这里。此解决方案似乎比提供的其他答案更加灵活和有效。
I ended up using a solution that incorporates variables, based on a Stack Overflow answer posted here. This solution appears to be a bit more flexible and efficient than other answers provided.
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;
请注意,按组,日期格式和范围要求只是我特定项目的具体细节。 此解决方案的更通用形式(根据原始问题)是:
Note that the group by, date formatting, and range requirements are simply specifics of my particular project. A more generic form of this solution (as per the original question) would be:
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;
这篇关于在mysql中检索运行总记录随时间的增长的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!