mysql - MySQL在一段时间内对行进行计数

可以说我有一个用户表，看起来像

userID int(4) unsigned not null auto_increment,
name varchar(50) not null,
date_start int(10) unsigned not null,
date_stop int(10) unsigned not null

我想统计每月活跃的所有用户（介于date_start和date_stop之间，这些用户是unix_timestamps（））。

所以结果应该是这样的：

2012/01    55
2012/02    58
2012/03    51

我会说这样的话，但显然我缺少了一些东西：

SELECT
    DATE_FORMAT(???, '%Y/%m'),
    COUNT(userID)
FROM users
WHERE
    ??? BETWEEN date_start AND date_stop
GROUP BY DATE_FORMAT(???, '%Y%m');

...只是为了澄清，对我有用的是：

SELECT m.yearMonth, COUNT(u.userID)
FROM users u
LEFT JOIN months m ON m.yearMonth BETWEEN DATE_FORMAT(FROM_UNIXTIME(u.date_start), '%Y%m') AND DATE_FORMAT(FROM_UNIXTIME(u.date_stop), '%Y%m')
GROUP BY m.yearMonth;

使用具有以yyyymm形式存储的“所有”年/月的表。

最佳答案

首先，您应该有一个包含yyyyMM格式的年，月详细信息的表。然后，您需要将该表与users表联接，其中date_start的yyyyMM部分小于月份表中的值，而date_end大于或等于月份表中的值。

这将计算一个活跃用户超过一个月（每月一次）的用户，我认为这是您所期望的。

CREATE TABLE months(yearMonth INT);
INSERT INTO months VALUES(201201);
INSERT INTO months VALUES(201202);
INSERT INTO months VALUES(201203);
....

SELECT m.yearMonth, COUNT(*)
FROM
    months m, users u
WHERE
    m.yearMonth >= CONVERT(INT,
                        CONVERT(VARCHAR(4), DATEPART(yy, date_start) +
                        CONVERT(VARCHAR(2), DATEPART(mm, date_start)
                      )
AND m.yearMonth <= CONVERT(INT,
                        CONVERT(VARCHAR(4), DATEPART(yy, date_end) +
                        CONVERT(VARCHAR(2), DATEPART(mm, date_end)
                      )

注意：这是SQL Server，但我想应该可以获取CONVERT / DATEPART函数的MySQL副本。