SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)
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.
I tried Hammerite's solution in my project, but it didn't work well where there were missing samples from the series. Here's an example of the query that is supposed to select timestamp (ts), user name and average measure from metric_table and group the results by 27-minute time intervals:
select
min(ts),
user_name,
sum(measure) / 27
from metric_table
where
ts between date_sub('2015-03-17 00:00:00', INTERVAL 2160 MINUTE) and '2015-03-17 00:00:00'
group by unix_timestamp(ts) div 1620, user_name
order by ts, user_name
;
Note: 27 minutes (in select) = 1620 seconds (in group by), 2160 minutes = 3 days (that's the time range)
When I ran this query against a time series where samples were irregularly recorded (in other words: for any given time stamp there was no guarantee to find measure values for all user names) the results were not stamped according to the interval (were not placed every 27 minutes). I suspect that was due to min(ts) returning a time stamp in some groups that was greater than the expected floor(ts0 + i*interval). I modified the former query to this one:
select
from_unixtime(unix_timestamp(ts) - unix_timestamp(ts) mod 1620) as ts1,
user_name,
sum(measure) / 27
from metric_table
where
ts between date_sub('2015-03-17 00:00:00', INTERVAL 2160 MINUTE) and '2015-03-17 00:00:00'
group by ts1, user_name
order by ts1, user_name
;
and it works fine even when the samples are missing. I think that is because once the time math is moved to select it guarantees that ts1 will align with the time steps.
Another solution.
To average over any interval you like you can convert your dt to timestamp and group by modulo by your interval (7 seconds in the example).
select FROM_UNIXTIME(
UNIX_TIMESTAMP(dt_record) - UNIX_TIMESTAMP(dt_record) mod 7
) as dt, avg(1das4hrz) from `meteor-m2_msgi`
where dt_record>='2016-11-13 05:00:00'
and dt_record < '2016-11-13 05:02:00'
group by FROM_UNIXTIME(
UNIX_TIMESTAMP(dt_record) - UNIX_TIMESTAMP(dt_record) mod 7);
To show how it works, I prepare a request, showing calculations.
select dt_record, minute(dt_record) as mm, SECOND(dt_record) as ss,
UNIX_TIMESTAMP(dt_record) as uxt, UNIX_TIMESTAMP(dt_record) mod 7 as ux7,
FROM_UNIXTIME(
UNIX_TIMESTAMP(dt_record) - UNIX_TIMESTAMP(dt_record) mod 7) as dtsub,
column from `yourtable` where dt_record>='2016-11-13 05:00:00'
and dt_record < '2016-11-13 05:02:00';
+---------------------+--------------------+
| dt | avg(column) |
+---------------------+--------------------+
| 2016-11-13 04:59:43 | 25434.85714285714 |
| 2016-11-13 05:00:42 | 5700.728813559322 |
| 2016-11-13 05:01:41 | 950.1016949152543 |
| 2016-11-13 05:02:40 | 4671.220338983051 |
| 2016-11-13 05:03:39 | 25468.728813559323 |
| 2016-11-13 05:04:38 | 43883.52542372881 |
| 2016-11-13 05:05:37 | 24589.338983050846 |
+---------------------+--------------------+
+---------------------+-----+-----+------------+------+---------------------+----------+
| dt_record | mm | ss | uxt | ux7 | dtsub | column |
+---------------------+------+-----+------------+------+---------------------+----------+
| 2016-11-13 05:00:00 | 0 | 0 | 1479002400 | 1 | 2016-11-13 04:59:59 | 36137 |
| 2016-11-13 05:00:01 | 0 | 1 | 1479002401 | 2 | 2016-11-13 04:59:59 | 36137 |
| 2016-11-13 05:00:02 | 0 | 2 | 1479002402 | 3 | 2016-11-13 04:59:59 | 36137 |
| 2016-11-13 05:00:03 | 0 | 3 | 1479002403 | 4 | 2016-11-13 04:59:59 | 34911 |
| 2016-11-13 05:00:04 | 0 | 4 | 1479002404 | 5 | 2016-11-13 04:59:59 | 34911 |
| 2016-11-13 05:00:05 | 0 | 5 | 1479002405 | 6 | 2016-11-13 04:59:59 | 34911 |
| 2016-11-13 05:00:06 | 0 | 6 | 1479002406 | 0 | 2016-11-13 05:00:06 | 33726 |
| 2016-11-13 05:00:07 | 0 | 7 | 1479002407 | 1 | 2016-11-13 05:00:06 | 32581 |
| 2016-11-13 05:00:08 | 0 | 8 | 1479002408 | 2 | 2016-11-13 05:00:06 | 32581 |
| 2016-11-13 05:00:09 | 0 | 9 | 1479002409 | 3 | 2016-11-13 05:00:06 | 31475 |
+---------------------+-----+-----+------------+------+---------------------+----------+
Can anyone suggest something faster?