SELECT/GROUP BY - 时间段(10 秒、30 秒等)

SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)(SELECT/GROUP BY - 时间段(10 秒、30 秒等))

问题描述

我有一个表 (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 秒等)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！