Mysql统计一段时间内的行数

Question

假设我有一个看起来像这样的用户表

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 形式存储“所有”年份/月份的表。

Answer 1

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

这将统计活跃时间超过一个月的用户，每月一次，我认为这正是您所期望的。

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 对应项。