MySQL 按周/月间隔按日期范围分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3012895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group by date range on weeks/months interval
提问by khelll
I'm using MySQL and I have the following table:
我正在使用 MySQL，我有下表：
| clicks | int |
| period | date |
I want to be able to generate reports like this, where periods are done in the last 4 weeks:
我希望能够生成这样的报告，其中的期间是在过去 4 周内完成的：
| period | clicks |
| 1/7 - 7/5 | 1000 |
| 25/6 - 31/7 | .... |
| 18/6 - 24/6 | .... |
| 12/6 - 18/6 | .... |
or in the last 3 months:
或在过去 3 个月内：
| period | clicks |
| July | .... |
| June | .... |
| April | .... |
Any ideas how to make select queries that can generate the equivalent date range and clicks count?
任何想法如何进行可以生成等效日期范围和点击计数的选择查询？
回答by simendsjo
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
回答by Keeper
For the last 3 months you can use:
在过去 3 个月内，您可以使用：
SELECT MONTH(PERIOD), SUM(CLICKS)
FROM TABLE
WHERE PERIOD >= NOW() - INTERVAL 3 MONTH
GROUP BY MONTH(PERIOD)
or for the last 4 weeks:
或过去 4 周：
SELECT WEEK(PERIOD), SUM(CLICKS)
FROM TABLE
WHERE PERIOD >= NOW() - INTERVAL 4 WEEK
GROUP BY WEEK(PERIOD)
Code not tested.
代码未测试。
