我有一个表(MySQL),每n秒捕获样本。该表有许多列,但对此重要的是两个:时间戳(类型TIMESTAMP)和计数(类型INT)。
我想做的是,在一个时间范围内获得计数列的总和和平均值。例如,我每2秒记录一次样本,但我想要所有样本的10秒或30秒窗口中所有样本的计数列的总和。
以下是数据示例:
+---------------------+-----------------+
| time_stamp | count |
+---------------------+-----------------+
| 2010-06-15 23:35:28 | 1 |
| 2010-06-15 23:35:30 | 1 |
| 2010-06-15 23:35:30 | 1 |
| 2010-06-15 23:35:30 | 942 |
| 2010-06-15 23:35:30 | 180 |
| 2010-06-15 23:35:30 | 4 |
| 2010-06-15 23:35:30 | 52 |
| 2010-06-15 23:35:30 | 12 |
| 2010-06-15 23:35:30 | 1 |
| 2010-06-15 23:35:30 | 1 |
| 2010-06-15 23:35:33 | 1468 |
| 2010-06-15 23:35:33 | 247 |
| 2010-06-15 23:35:33 | 1 |
| 2010-06-15 23:35:33 | 81 |
| 2010-06-15 23:35:33 | 16 |
| 2010-06-15 23:35:35 | 1828 |
| 2010-06-15 23:35:35 | 214 |
| 2010-06-15 23:35:35 | 75 |
| 2010-06-15 23:35:35 | 8 |
| 2010-06-15 23:35:37 | 1799 |
| 2010-06-15 23:35:37 | 24 |
| 2010-06-15 23:35:37 | 11 |
| 2010-06-15 23:35:37 | 2 |
| 2010-06-15 23:35:40 | 575 |
| 2010-06-15 23:35:40 | 1 |
| 2010-06-17 10:39:35 | 2 |
| 2010-06-17 10:39:35 | 2 |
| 2010-06-17 10:39:35 | 1 |
| 2010-06-17 10:39:35 | 2 |
| 2010-06-17 10:39:35 | 1 |
| 2010-06-17 10:39:40 | 35 |
| 2010-06-17 10:39:40 | 19 |
| 2010-06-17 10:39:40 | 37 |
| 2010-06-17 10:39:42 | 64 |
| 2010-06-17 10:39:42 | 3 |
| 2010-06-17 10:39:42 | 31 |
| 2010-06-17 10:39:42 | 7 |
| 2010-06-17 10:39:42 | 246 |
+---------------------+-----------------+
我想要的输出(基于上面的数据)应该看起来像这样:
+---------------------+-----------------+
| 2010-06-15 23:35:00 | 1 | # This is the sum for the 00 - 30 seconds range
| 2010-06-15 23:35:30 | 7544 | # This is the sum for the 30 - 60 seconds range
| 2010-06-17 10:39:35 | 450 | # This is the sum for the 30 - 60 seconds range
+---------------------+-----------------+
我使用GROUP BY来收集这些数字的第二个,或分钟,但我似乎无法找出语法获得子分钟或范围的秒GROUP BY命令正常工作。
我大多要使用这个查询从这个表虹吸数据到另一个表。
谢谢!