example - mysql group by having

SELECT/GROUP BY-segmentos de tiempo(10 segundos, 30 segundos, etc.)

Question

Tengo una tabla (MySQL) que captura muestras cada n segundos. La tabla tiene muchas columnas, pero todo lo que importa para esto es dos: una marca de tiempo (del tipo TIMESTAMP) y una cuenta (del tipo INT).

Lo que me gustaría hacer es obtener sumas y promedios de la columna de recuento en un rango de veces. Por ejemplo, tengo muestras cada 2 segundos registradas, pero me gustaría la suma de la columna de conteo para todas las muestras en una ventana de 10 segundos o 30 segundos para todas las muestras.

Aquí hay un ejemplo de los datos:

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

El resultado que me gustaría (basado en los datos anteriores) debería verse así:

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

He usado GROUP BY para recopilar estos números por segundo, o por minuto, pero parece que no puedo entender la sintaxis para hacer que el sub-minuto o rango de segundos de comandos GROUP BY funcione correctamente.

Principalmente voy a utilizar esta consulta para extraer datos de esta tabla a otra tabla.

¡Gracias!

Answer 1

Muy extraño pero usando la solución aquí:

Promedio de datos por cada 5 minutos en los tiempos determinados

Podemos sugerir algo como:

select convert(
              (min(dt_record) div 50)*50 - 20*((convert(min(dt_record), 
               datetime) div 50) mod 2), datetime)  as dt, 
       avg(1das4hrz) 
from `meteor-m2_msgi`
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;


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 es porque la mitad del minuto NORMAL tiene 30 segundos, mientras que el ''FORMATO DE FECHA INTEGER'' supone que nosotros lo dividimos por 50

Answer 2

Otra solución.

Para promediar sobre cualquier intervalo que desee, puede convertir su dt en timestamp y agruparlo por módulo según su intervalo (7 segundos en el ejemplo).

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

Para mostrar cómo funciona, preparo una solicitud que muestra los cálculos.

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

¿Alguien puede sugerir algo más rápido?

Answer 3

Probé la solución de Hammerite en mi proyecto, pero no funcionó bien cuando faltaban muestras de la serie. Aquí hay un ejemplo de la consulta que se supone que selecciona la marca de tiempo (ts), el nombre de usuario y la medida promedio de metric_table y agrupa los resultados en intervalos de tiempo de 27 minutos:

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
;

Nota: 27 minutos (en selección) = 1620 segundos (en grupo por), 2160 minutos = 3 días (ese es el intervalo de tiempo)

Cuando ejecuté esta consulta en una serie temporal donde las muestras se registraron irregularmente (en otras palabras: para un sello de tiempo dado no había garantía de encontrar valores de medición para todos los nombres de usuario) los resultados no se marcaron de acuerdo con el intervalo (no se colocaron cada 27 minutos). Sospecho que se debió a que min (ts) devolvió una marca de tiempo en algunos grupos que era mayor que el piso esperado (intervalo ts0 + i *). Modifiqué la consulta anterior a esta:

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
;

y funciona bien incluso cuando faltan las muestras. Creo que es porque una vez que el tiempo matemático se mueve para seleccionar, garantiza que ts1 se alineará con los pasos de tiempo.

Answer 4

GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 30

o diga por alguna razón que quería agruparlos en intervalos de 20 segundos sería DIV 20 etc. Para cambiar los límites entre los valores de GROUP BY , podría usar

GROUP BY (UNIX_TIMESTAMP(time_stamp) + r) DIV 30

donde r es un entero literal no negativo menos de 30. Entonces

GROUP BY (UNIX_TIMESTAMP(time_stamp) + 5) DIV 30

debe darle sumas entre hh: mm: 05 y hh: mm: 35 y entre hh: mm: 35 y hh: mm + 1: 05.