Consulta sobre medias móviles

[ismapolo]

Estimados amigos:

Tengo datos de temperatura de varios meses de un conjunto de estaciones meteorológicas.

La estructura de la base de datos es la siguiente:

| ID | LUGAR | FECHA | TMAX|

donde: FECHA es la fecha del día
TMAX: es el dato de la temperatura
LUGAR: es el lugar donde se encuentra la estación

Mi idea es agrupar las medias por día de ese conjunto de datos y luego aplicar una media móvil de 5 días para ver la tendencia de la temperatura durante esos meses.

Pues bien, agrupar por días es algo que mi ordenador hace muy rápido (0.03 seg) pero cuando tiene que hallar las medias móviles, entonces ya se eterniza y le cuesta nada menos que 35 segundos. No quiero imaginarme lo que le debe costar hacerlo online.

La consulta que utilizo para agrupar los registros por días y hallar la media móvil es la siguiente:

SELECT a.FECHA, format(sum(b.TMAX)/count(b.TMAX),2) 5dayMovAvg
FROM TABLA a
JOIN TABLA b on datediff(a.FECHA,b.FECHA) BETWEEN 0 AND 4
WHERE a.FECHA BETWEEN '2010-12-15' AND '2011-03-31'
GROUP BY a.FECHA;


Y la verdad que no entiendo esta lentitud, ya que para calcular la media móvil de un día, sólo tiene que sumar los datos de las temperaturas de los 5 días anteriores y dividirlo entre 5.

¿Hay alguna forma más eficiente de hacer esto?

Saludos a todos y gracias!

[leonardo_josue]

Hola ismapolo:

No entiendo por qué no utilizas la función AVG para el cálculo del porcentaje, creo que sería más conveniente:

Código MySQL:

SELECT a.FECHA, format(AVG(b.TMAX),2) 5dayMovAvg 
FROM TABLA a 
JOIN TABLA b on datediff(a.FECHA,b.FECHA) BETWEEN 0 AND 4 
WHERE a.FECHA BETWEEN '2010-12-15' AND '2011-03-31'
GROUP BY a.FECHA;
 

Por otro lado, no me queda claro qué es lo que quieres hacer, creo que sería conveniente que nos pusieras algunos datos de ejemplo con lo que esperas de salida, para tratar de buscar alguna otra alternativa, pues al estar el JOIN tal como lo tienes creo que te estaría generando un producto cartesiano, pues no defines ninguna relación entre tu alias A y B, lo que indudablemente afecta en el tiempo de respuesta.

Saludos
Leo.

[ismapolo]

Gracias por responder Leo:

Tomo nota sobre utilizar AVG.

Lo que busco es un resultado como el siguiente:

FECHA 5dayMovAvg
2010-12-15 7.10
2010-12-16 8.20
2010-12-17 7.52

...

donde 7.10 es la media móvil de los días 11, 12, 13, 14 y 15 de dic. de 2010 (es decir, es el resultado de sumar el dato agrupado de esos días y dividirlo entre 5)

8.20 es el resultado de sumar el dato agrupado de los días 12, 13, 14, 15 y 16 de dic. de 2010 y dividirlo entre 5.

7.52 es el resultado de sumar el dato agrupado de los días 13, 14, 15, 16 y 17 de dic. de 2010 y dividirlos entre 5.

Así con todos los valores agrupados de la serie temporal.

La consulta que he puesto más arriba fue lo único que encontré en internet, es decir, no es mía.

Saludos!

[jurena]

A ver si he entendido bien. Estoy de acuerdo con leonardo_josue en utilizar AVG para sacar las medias, pero lo que no me convence es la consulta. Me explico: Si lo que quieres es averiguar las medias por día, y luego sumarlas y dividir por 5 creo que vas a tener que realizar 5 búsquedas, una por cada día, hacer las medias de cada día, luego sumar esas medias y finalmente dividir por 5. También puedes hacerlo con consultas UNION ALL en una tirada.

[ismapolo]

Gracias jurena. En ese caso, ¿cómo quedaría la consulta?

Saludos.

[jurena]

yo lo haría con subconsulta y consulta con union. Un ejemplo básico
SELECT (SUM(t1.mediadia)/5) mediamovil, t1.fecha5 fechareferencia FROM (SELECT AVG(temp) mediadia, fecha5 FROM tutabla where fecha = 'fechadia5'
UNION ALL
SELECT AVG(temp) mediadia, fecha4 FROM tutabla where fecha = 'fechadia4'
UNION ALL
SELECT AVG(temp) mediadia, fecha3 FROM tutabla where fecha = 'fechadia3'
UNION ALL
SELECT AVG(temp) mediadia, fecha2 FROM tutabla where fecha = 'fechadia2'
UNION ALL
SELECT AVG(temp) mediadia, fecha1 FROM tutabla where fecha = 'fechadia1')t1

No he probado nada. Naturalmente, puedes usar para esas fechas el date_sub y date_add sobre la fecha de referencia, que debería ir en el primer select del union all

De todas formas, quizás te sería más cómodo hacerlo con programación.

[leonardo_josue]

Hola de nuevo IsmaPolo...

Si entendí correctamente, creo que no hay necesidad de hacer 5 subconsultas como comenta el compañero jurena, a reserva de que pruebes y nos comentes, pero primero veamos si estoy entendiendo bien... En mi post pasado de pedía que nos pusieras algunos datos de ejemplo, pero solo pusiste la salida (y me es imposible saber a partir de qué datos obtuviste el 7.10 y los demás datos) así es que voy a poner algunos datos de ejemplo por mi cuenta. Voy a suponer que tienes la siguiente información en tu tabla:

Código MySQL:

mysql> SELECT * FROM tabla;
+------+---------+------------+------+
| ID   | LUGAR   | FECHA      | TMAX |
+------+---------+------------+------+
|    1 | lugar 1 | 2011-10-01 |   10 |
|    2 | lugar 1 | 2011-10-02 |   20 |
|    3 | lugar 1 | 2011-10-03 |   30 |
|    4 | lugar 1 | 2011-10-04 |   40 |
|    5 | lugar 1 | 2011-10-05 |   50 |
|    6 | lugar 1 | 2011-10-06 |   60 |
|    7 | lugar 1 | 2011-10-07 |   70 |
|    8 | lugar 1 | 2011-10-08 |   80 |
|    9 | lugar 1 | 2011-10-09 |   90 |
|   10 | lugar 1 | 2011-10-10 |  100 |
+------+---------+------------+------+
10 rows in set (0.00 sec)
 

si entendí bien, los promedios se deberían calcular así:

Código:

 
2011-10-01 = 10 / 1 = 10
(Pues no hay ninguna temperatura con fecha anterior)
 2011-10-02 = 10 + 20 / 2 = 15
(Pues existe una temperatura con fecha anterior)
 2011-10-03 = 10 + 20 + 30 / 3 = 20
(Pues existe dos temperatura con fecha anterior)
 2011-10-04 = 10 + 20 + 30 + 40 / 4 = 25
(existe 4 temperatura con fecha anterior)
 2011-10-05 = 10 + 20 + 30 + 40 + 50 / 5 = 30
(existe 5 temp. con fecha anterior)
 2011-10-06 = 20 + 30 + 40 + 50 + 60 / 5 = 40
(aunque existen 6 temp con fecha anterior, solo se toman las últimas 5, 
pues con respecto a la del primer día ya han transcurrido más de 6 días)
...

Si estoy en lo correcto y esto es lo que quieres obtener podrías hacerlo así:

Código MySQL:

mysql> SELECT fecha, (
    -> SELECT AVG(tmax)
    -> FROM tabla T2 WHERE T2.fecha <= T1.fecha AND 
    -> DATEDIFF(T1.fecha, T2.fecha) < 5) 5dayMovAvg
    -> FROM tabla T1;
+------------+------------+
| fecha      | 5dayMovAvg |
+------------+------------+
| 2011-10-01 |    10.0000 |
| 2011-10-02 |    15.0000 |
| 2011-10-03 |    20.0000 |
| 2011-10-04 |    25.0000 |
| 2011-10-05 |    30.0000 |
| 2011-10-06 |    40.0000 |
| 2011-10-07 |    50.0000 |
| 2011-10-08 |    60.0000 |
| 2011-10-09 |    70.0000 |
| 2011-10-10 |    80.0000 |
+------------+------------+
10 rows in set (0.02 sec)
 

Dale un vistazo para ver si es lo que requieres, y prueba también la solución de jurena.

Saludos
Leo.

[jurena]

Creo que lleva razón leonardo_josue y el resultado es el mismo si se hace su consulta, que es mucho más eficiente.

la suma de las medias de los lugares, aplicada luego la media a esas medias es igual a la media de todas las temperaturas de esos días, por lo que te aconsejo que uses la de leonardo.

Saludos

[ismapolo]

Esa es la idea Leo, aunque yo lo quería agrupado por días. Ten en cuenta que un mismo día puede tener varios valores correspondientes a varias estaciones meteorológicas

El problema es que, cuando la serie de datos es grande (como en mi caso) la consulta es lentísima y en ocasiones no termina de hacerse.

Para que te hagas una idea, la consulta que propones ha tardado 700 segundos en realizarse.

No sé si hacer la media móvil sobre 5 elementos o sobre más. En una serie temporal de este tipo, con muchos altibajos en los valores de la variable, cuantos más elementos tomemos (por ejemplo 15 días en lugar de 5) la gráfica resultante se suaviza en gran medida y esto favorece compararlo con otras series. Es lo que estaba buscando, suavizarla aunque no demasiado.

Gracias!

[jurena]

¿Y por qué no controlas que no haga medias de todos los datos, sino sólo de los de tu consulta?

Imagina tu serie, primero te traes sólo cinco registros antes del comienzo (fecha de tu serie) y 5 después. Luego y sólo sobre ellos haces la subconsulta y agrupas para hacer las medias. Incluso con programación será mejor que una consulta que saque las medias de todos los días que tienes recogidos.

[ismapolo]

Iniciado por jurena

¿Y por qué no controlas que no haga medias de todos los datos, sino sólo de los de tu consulta?

Imagina tu serie, primero te traes sólo cinco registros antes del comienzo (fecha de tu serie) y 5 después. Luego y sólo sobre ellos haces la subconsulta y agrupas para hacer las medias. Incluso con programación será mejor que una consulta que saque las medias de todos los días que tienes recogidos.

No lo sé jurena, estoy hecho un lío.

Saludos.

[leonardo_josue]

Hola de nuevo ismapolo...

Creo que no estamos yendo a la raíz del problema... en primer lugar no recuerdo que nos hayas comentado si estás manejando Indices en tu tabla y cuales son estos indices... Te recuerdo que los índices sirven justamente para ayudar en las consultas, también sería conveniente que hicieras un EXPLAIN a tu consulta, para ver el plan de ejecución.

Segundo, los datos que puse yo son completamente inventados, PUES SIGUES SIN PONERNOS TUS DATOS. En ningún lugar habías mencionado que un día podía tener más de un dato... en cuanto a la agrupación, pues simplemente deberías de agregarla:

Código MySQL:

SELECT fecha, (
SELECT AVG(tmax)
FROM tabla T2 WHERE T2.fecha <= T1.fecha AND 
DATEDIFF(T1.fecha, T2.fecha) < 5) 5dayMovAvg
FROM tabla T1 GROUP BY fecha;
 

Y si no quieres analizar toda tu tabla AGREGA UNA CONDICIÓN WHERE DONDE SÓLO MANEJES LOS REGISTROS QUE QUIERES ANALIZAR, ya sea filtrando por rangos de fechas o por cualquier otro criterio.

Saludos
Leo.

[ismapolo]

Gracias Leo, tanto el agrupar por fechas como la selección de las mismas ya las había adaptado en tu consulta.

Si tomo los datos de 2006-2007, hay cerca de 12.000 registros de temperaturas. La base de datos tiene más de 100.000 registros en total. Hay 86 estaciones aportando datos, por eso dije que en un día puede haber varios registros, uno para cada estación.

El campo índice es lo llamo ID_REG y es int (10), auto_increment.

Respecto a los campos de la base de datos, los incluí en un mensaje mío anterior. Los datos reales creo que tampoco son importantes y para una simulación bien pueden valer unos inventados.

Saludos.

[bigwhite]

Mi idea es agrupar las medias por día de ese conjunto de datos y luego aplicar una media móvil de 5 días para ver la tendencia de la temperatura durante esos meses.

Offtopic, pero yo tambien usaria el cruce de dos medias moviles: una lenta y una rapida........

[bigwhite]

PD: usas indices sobre las FECHAS ? los BETWEEN son de por si LENTOS...