按日期范围按周/月间隔组合 [英] Group by date range on weeks/months interval

问题描述

我正在使用MySQL，我有以下表格：

I'm using MySQL and I have the following table:

| clicks | int  |
|   period  | date |

我希望能够生成这样的报告，在过去4周内完成期间：

I want to be able to generate reports like this, where periods are done in the last 4 weeks:

|   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  |  ....  |

任何想法如何使可以生成等效日期范围和点击次数的选择查询计数？

Any ideas how to make select queries that can generate the equivalent date range and clicks count?

推荐答案

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

这篇关于按日期范围按周/月间隔组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！