mysql - 按周/月间隔的日期范围分组

我正在使用MySQL，并且具有下表:

| clicks | int  |
|   period  | date |

我希望能够生成这样的报告，其中在过去4周内完成了各个周期:

|   period    | clicks |
|  1/7 - 7/5  |  1000  |
| 25/6 - 31/7 |  ....  |
| 18/6 - 24/6 |  ....  |
| 12/6 - 18/6 |  ....  |

或最近3个月内:

| period | clicks |
|  July  |  ....  |
|  June  |  ....  |
| April  |  ....  |

有什么想法可以进行选择查询以生成等效的日期范围和点击次数吗？

最佳答案

SELECT
 WEEKOFYEAR(`date`) AS period,
 SUM(clicks) AS clicks
FROM `tablename`
WHERE `date` >= CURDATE() - INTERVAL 4 WEEK
GROUP BY period

SELECT
 MONTH(`date`) AS period,
 SUM(clicks) AS clicks
FROM `tablename`
WHERE `date` >= CURDATE() - INTERVAL 3 MONTH
GROUP BY period

关于mysql - 按周/月间隔的日期范围分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3012895/