如何获得每个月*的注册用户总数？

Suppose I have a table like this:

假设我有一个这样的表:

users:

id (int) | date_registered (int)
1          1394746963
...        ...

And I want to make a pretty chart showing the number of registered users for each month (not the number of users registering per month, but the total number of registered users at that point in time).

我想制作一个漂亮的图表,显示每个月的注册用户数(不是每月注册的用户数,而是该时间点注册用户的总数)。

So, for example, the data returned might look like this:

因此,例如,返回的数据可能如下所示:

date        | registeredUsers
2013-10-00    15
2013-11-00    55
2013-12-00    96
2014-01-00    150
2014-02-00    225

I know how to get the number of users registered at a certain point in time, that's easy:

我知道如何获得在某个时间点注册的用户数量,这很容易:

SELECT COUNT(*) FROM users WHERE date_registered < :unixTimeStamp

But how do I write a query that will return the number of registered users at multiple points in time (namely, at the end or beginning of each month)?

但是,如何编写一个查询,以便在多个时间点(即每月结束或开始时)返回注册用户数?

1 个解决方案

#1

You may want to group by date.

您可能希望按日期分组。

SELECT date, COUNT(id) FROM users GROUP BY MONTH(date_registered).