SELECT / GROUP BY - 时间段（10秒，30秒等） [英] SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)
问题描述
我有一个表格（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 | ＃这是00 - 30秒范围
|的总和2010-06-15 23:35:30 | 7544 | ＃这是30 - 60秒范围
|的总和2010-06-17 10:39:35 | 450 | ＃这是30 - 60秒范围内的总和
+ --------------------- + ----------- ------ +
我使用GROUP BY来收集这些数字，或者按分钟，但是我可以似乎没有找到语法来获取GROUP BY命令的子分钟或范围正常工作。
我主要是使用这个查询来将数据从这个表中抽取到另一个表中。
谢谢！
GROUP BY UNIX_TIMESTAMP（time_stamp）DIV 30
或者说出于某种原因，第二个时间间隔为 DIV 20 等。要更改 GROUP BY 值之间的界限，您可以使用
GROUP BY（UNIX_TIMESTAMP（time_stamp）+ r）DIV 30
其中 r 是一个小于30的字面非负整数。所以
GROUP BY UNIX_TIMESTAMP（time_stamp）+ 5 ）DIV 30
应该给你hh：mm：05和hh：mm：35之间以及hh：mm：35和hh之间的总和：mm + 1：05。
I have a table (MySQL) that captures samples every n seconds. The table has many columns, but all that matters for this is two: a time stamp (of type TIMESTAMP) and a count (of type INT).
What I would like to do, is get sums and averages of the count column over a range of times. For instance, I have samples every 2 seconds recorded, but I would like the sum of the count column for all the samples in a 10 second or 30 second window for all samples.
Here's an example of the data:
+---------------------+-----------------+ | 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 | +---------------------+-----------------+
The output I would like (based on the data above) should look like this:
+---------------------+-----------------+ | 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 +---------------------+-----------------+
I have used GROUP BY to gather these numbers by the second, or by the minute, but I can't seem to figure out the syntax to get the sub-minute or range of seconds GROUP BY commands to work correctly.
I am mostly going to be using this query to syphon data from this table to another table.
Thanks!
GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 30
or say for some reason you wanted to group them in 20-second intervals it would be DIV 20 etc. To change the boundaries between GROUP BY values you could use
GROUP BY (UNIX_TIMESTAMP(time_stamp) + r) DIV 30
where r is a literal nonnegative integer less than 30. So
GROUP BY (UNIX_TIMESTAMP(time_stamp) + 5) DIV 30
should give you sums between hh:mm:05 and hh:mm:35 and between hh:mm:35 and hh:mm+1:05.
这篇关于SELECT / GROUP BY - 时间段（10秒，30秒等）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
