SELECT/GROUP BY - 时间段(10 秒、30 秒等) [英] SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)
问题描述
我有一个表 (MySQL)，每 n 秒捕获一次样本.该表有许多列，但对它而言重要的是两列:时间戳(TIMESTAMP 类型)和计数(INT 类型).
我想做的是在一定范围内获得计数列的总和和平均值.例如，我每 2 秒记录一次样本，但我想要所有样本的 10 秒或 30 秒窗口中所有样本的计数列的总和.
以下是数据示例:
<前>+---------------------+-----+|时间戳 |计数 |+---------------------+-----+|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
或者说出于某种原因你想以 20 秒的间隔对它们进行分组，它会是 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屋！
