在 mysql 中有没有一种方法可以按月分组，但可以自定义开始日期。

假设我想按月计算登录次数，但条件是该月从用户注册时开始。

例如，用户 A 于 1 月 30 日注册，用户 B 于 1 月 15 日注册

我应该按如下方式对登录进行分组:

* User A: January 30th - February 28th, March 1st - March 30th, March 31 - April 30 and so on and so forth 
* User B: January 15th - February 14th, February 15th - March 14th and so on and so forth

我想我需要使用像 DATE_ADD('2013-01-30', INTERVAL 1 MONTH); 这样的东西，但我似乎找不到进行分组的方法。

更新

@GarethD:你是对的，打错了

一般来说，月份应该从下个月的同一天或下个月的最后一天开始，以防万一第一天不可能，所以如果您在第 31 天注册，则月份将从第 30 天开始对于没有 31 天的月份和二月的最后一天为 28 或 29

示例:

鉴于此

id 1 registered on 2012-12-16
id 2 registered on 2013-01-29

和下表

+----+------------+
| id |    date    |
+----+------------+
|  1 | 2013-01-15 |
|  1 | 2013-01-16 |
|  1 | 2013-01-17 |
|  1 | 2013-01-17 |
|  2 | 2013-03-20 |
|  2 | 2013-03-21 |
|  2 | 2013-03-28 |
|  2 | 2013-03-29 |
|  2 | 2013-03-30 |
+----+------------+

结果应该是

+----+----------------------------+-------+
| id |           range            | count |
+----+----------------------------+-------+
| 1  | 2012-12-16, 2013-01-15     |     1 |
| 1  | 2013-01-16, 2013-02-15     |     3 |
| 2  | 2013-02-2[8|9], 2013-03-28 |     3 |
| 2  | 2013-03-29, 2013-04-28     |     2 |
+----+----------------------------+-------+

我希望现在的意图更清楚了。

最佳答案

对于以下内容，我假设您已经有一个数字表，如果您没有数字表，那么我建议您制作一个，但如果您不想，则可以 create a number list on the fly

您可以通过将您的用户 ID 和注册日期与您的号码表交叉连接来获取所有边界的列表:

SELECT  u.ID, 
        DATE_ADD(RegisteredDate, INTERVAL n.Number MONTH) PeriodStart,
        DATE_ADD(RegisteredDate, INTERVAL n.Number + 1 MONTH) PeriodEnd
FROM    User u
        CROSS JOIN Numbers n;

这给出了一个像这样的表格:

ID  PERIODSTART     PERIODEND
1   2012-12-16      2012-12-16
2   2013-01-29      2013-01-29
1   2013-01-16      2013-01-16
2   2013-02-28      2013-02-28

Example on SQL Fiddle

然后您需要将它加入您的主表，并进行计数:

SELECT  u.ID,
        u.PeriodStart,
        DATE_ADD(PeriodEnd, INTERVAL -1 DAY) PeriodEnd,
        COUNT(*) AS `COUNT`
FROM    (   SELECT  u.ID, 
                    DATE_ADD(RegisteredDate, INTERVAL n.Number MONTH) PeriodStart,
                    DATE_ADD(RegisteredDate, INTERVAL n.Number + 1 MONTH) PeriodEnd
            FROM    User u
                    CROSS JOIN Numbers n
        ) u
        INNER JOIN T
            ON T.ID = u.ID
            AND T.Date >= u.PeriodStart
            AND T.Date < PeriodEnd
GROUP BY u.ID, u.PeriodStart, u.PeriodEnd;

给出最终结果:

ID  PERIODSTART     PERIODEND   COUNT
1   2012-12-16      2013-01-15  1
1   2013-01-16      2013-02-15  3
2   2013-02-28      2013-03-28  3
2   2013-03-29      2013-04-28  2

Full Example on SQL-Fiddle

显然，您可以将期间的开始日期和结束日期连接起来以形成“范围”字符串，但这可能最好在您的应用程序层中处理。

编辑

这可以在没有可能表现更好的子查询的情况下实现:

SELECT  u.ID,
        DATE_ADD(u.RegisteredDate, INTERVAL n.Number MONTH) PeriodStart,
        DATE_ADD(DATE_ADD(u.RegisteredDate, INTERVAL n.Number + 1 MONTH), INTERVAL -1 DAY) PeriodEnd,
        COUNT(*) AS `COUNT`
 FROM   User u
        CROSS JOIN Numbers n 
        INNER JOIN T
            ON T.ID = u.ID
            AND T.Date >= DATE_ADD(u.RegisteredDate, INTERVAL n.Number MONTH)
            AND T.Date < DATE_ADD(u.RegisteredDate, INTERVAL n.Number + 1 MONTH)
GROUP BY u.ID, u.RegisteredDate, n.Number;

Example with no subquery on SQL-Fiddle

编辑 2

这将为您提供所有用户的所有时间段，直到当前时间段(即今天在日期范围内)

SELECT  u.ID,
        DATE_ADD(u.RegisteredDate, INTERVAL n.Number MONTH) PeriodStart,
        DATE_ADD(DATE_ADD(u.RegisteredDate, INTERVAL n.Number + 1 MONTH), INTERVAL -1 DAY) PeriodEnd,
        COUNT(T.ID) AS `COUNT`
 FROM   User u
        CROSS JOIN Numbers n 
        LEFT JOIN T
            ON T.ID = u.ID
            AND T.Date >= DATE_ADD(u.RegisteredDate, INTERVAL n.Number MONTH)
            AND T.Date < DATE_ADD(u.RegisteredDate, INTERVAL n.Number + 1 MONTH)
WHERE   DATE_ADD(u.RegisteredDate, INTERVAL n.Number + 1 MONTH) <= CURRENT_TIMESTAMP
GROUP BY u.ID, u.RegisteredDate, n.Number;

Example on SQL Fiddle

关于mysql按月分组，自定义开始日期，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18620466/