SELECT/GROUP BY - сегменты времени (10 секунд, 30 секунд и т.д.)

Ответ 1

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.

Ответ 2

Я пробовал решение Hammerite в своем проекте, но он не работал хорошо, где отсутствовали образцы из серии. Здесь приведен пример запроса, который должен выбрать временную метку (ts), имя пользователя и среднюю меру из metric_table и группировать результаты с помощью 27-минутных интервалов времени:

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
;

Примечание: 27 минут (при выборе) = 1620 секунд (в группе), 2160 минут = 3 дня (что временной диапазон)

Когда я запускал этот запрос по временному ряду, где образцы были неправильно записаны (другими словами: для любой заданной отметки времени не было гарантии найти значения значений для всех имен пользователей), результаты не были отпечатаны в соответствии с интервалом ( не размещались каждые 27 минут). Я подозреваю, что это произошло из-за того, что min (ts) вернул отметку времени в некоторых группах, которая была больше ожидаемого уровня (ts0 + я * interval). Я изменил прежний запрос на этот:

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
;

и он отлично работает, даже если образцы отсутствуют. Я думаю, это потому, что, как только математика времени перемещается для выбора, она гарантирует, что ts1 будет выровняться с шагами времени.

Ответ 3

Другое решение.

Чтобы усреднить за любой интервал, который вам нравится, вы можете преобразовать свой dt в timestamp и group по модулю по вашему интервалу (7 секунд в примере).

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);

Чтобы показать, как это работает, я готовлю запрос, показывая вычисления.

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 |
+---------------------+-----+-----+------------+------+---------------------+----------+

Может ли кто-нибудь предложить что-то быстрее?

Ответ 4

Очень странно, но используя здесь решение:

Среднее количество данных за каждые 5 минут в заданное время

Мы можем предложить что-то вроде:

select convert(
(min(dt_record) div 50)*50 - 20*((convert(min(dt_record), datetime) div 50) mod 2),

datetime) как dt,   avg (1das4hrz) из meteor-m2_msgi, где dt_record >= '2016-11-13 05:00:00' и dt_record < '2016-11-14 00:00:00' группа конвертированием (dt_record, datetime) div 50;

select (
convert(
min(dt_record), datetime) div 50)*50 - 20*(
(convert(min(dt_record), datetime) div 50) mod 2
) as dt,
avg(column) from `your_table`
where dt_record>='2016-11-13 05:00:00'
and dt_record < '2016-11-14 00:00:00'
group by convert(dt_record, datetime) div 50;

50 состоит в том, что 1/2 минуты NORMAL имеет 30 секунд, в то время как "FORMAT DATE FORMAT" предполагает разделение на 50