Crear dos columnas dependiendo de los datos de una

[jcayon]

Hola:
Estoy haciendo una aplicación donde guardo la información de una serie de sensores en una base de datos y despues lo muestro en una pagina.

Los sensores pueden mandar varios tipos de información. Por ejemplo, tengo un sensor de temperatura que envia temperatura media, minima y maxima en un momento x, y guardo cada datos por separado, es decir, tengo la tabla de la siguiente forma:

-------------------------------------------------
| sensor | Atributo | Valor | FechaHora |
-------------------------------------------------
| 1 | Media | 15.5 | 2012-06-18 17:40:00 |
-------------------------------------------------
| 1 | Maxima | 17.5 | 2012-06-18 17:40:00 |
-------------------------------------------------
| 1 | Minima | 13.5 | 2012-06-18 17:40:00 |
-------------------------------------------------

y quiero hacer una consulta teniendo una columna con cada atributo y la fecha, del siguiente modo:

-------------------------------------------------
| sensor | Media | Maxima | Minima | FechaHora |
-------------------------------------------------
| 1 | 15.5 | 17.5 | 13.5 | 2012-06-18 17:40:00 |
-------------------------------------------------

Tengo una idea de como hacerlo por PHP pero supondría muchas peticiones a la base de datos y me preguntaba si sabeis alguna forma de hacerlo en sql y que no le tome demasiado tiempo a la base de datos.

Intente hacer una vista con cada atributo y despues juntar las vistas con INNER JOIN segun FechaHora, pero la consulta no termina diciendo que se a pasado el tiempo de consulta, y no se si es porque es una operación que no se puede hacer o porque simplemente le lleva demasiado tiempo.

Agradeceria cualquier sujerencia sobre como hacerlo.
Muchisimas gracias.

[quimfv]

Mira este post....http://www.forosdelweb.com/f86/agrup...recio-1000012/ no me gusta usarlo pero tu solucion pasa por group_concat

Yo habria hecho una tabla para cada tipo de sensor y un campo para cada tipo de dato....


Sensores
idSensor
idTipo
....

TiposSensores
idTipo
tipo
tabla
...

TblTemperaturas
idLectura
idSensor
FechaHora
Media
Maxima
Minima

Tbl..Otra...
idLectura
idSensor
FechaHora
DatoA
DatoB
...

...media, minima y maxima en un momento x...

media, minima y maxima no tienen sentido si no son sobre un periodo de tiempo??? Pero bueno tu sabras como lo medis....

[leonardo_josue]

Hola jcayon:

Existe otra forma con la que podrías obtener los resultados que quieres y sería con agrupaciones condicionales... Aquí la pregunta sería si sólo vas a tener una media, mínima y máxima por sensor y por fecha... Observa este ejemplo:

Código MySQL:

mysql> SELECT * FROM tabla;
+--------+----------+-------+---------------------+
| sensor | Atributo | Valor | FechaHora           |
+--------+----------+-------+---------------------+
|      1 | Media    |  15.5 | 2012-06-18 17:40:00 |
|      1 | Maxima   |  17.5 | 2012-06-18 17:40:00 |
|      1 | Minima   |  13.5 | 2012-06-18 17:40:00 |
+--------+----------+-------+---------------------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   sensor,
    ->   AVG(IF(atributo = 'Media', valor, NULL)) media,
    ->   AVG(IF(atributo = 'Maxima', valor, NULL)) maxima,
    ->   AVG(IF(atributo = 'Minima', valor, NULL)) minima,
    ->   fechaHora
    -> FROM tabla
    -> GROUP BY sensor, fechaHora;
+--------+----------+----------+----------+---------------------+
| sensor | media    | maxima   | minima   | fechaHora           |
+--------+----------+----------+----------+---------------------+
|      1 | 15.50000 | 17.50000 | 13.50000 | 2012-06-18 17:40:00 |
+--------+----------+----------+----------+---------------------+
1 row in set (0.00 sec)
 

Observa en primer lugar que estoy utilizando tres agrupaciones AVG condicionales, en cada IF estoy colocando cada uno de los posible valores que tienes (si tuvieras más atributos deberías considerarlos también).

Dale un vistazo para ver si te sirve.

Saludos
Leo.

[jcayon]

Hola:
Primero muchísimas gracias a los dos por responder:

Quim:
El problema es que tengo muchos tipos diferentes de sensores, o esa es la idea, quiero crear un sistema de recogida de datos que mande los datos a un servidor y que estos se puedan ver en nube, por lo que no puedo tener una tabla diferente por cada tipo de sensor ya que, en principio, ni se que sensores puede tener el sistema.

La cosa del tiempo x es porque la maquina de recogida de datos manda una trama con los tres valores, y yo los guardo en la tabla genérica. Pero después se da al usuario que lo vea la posibilidad de ver los tres valores que han llegado en ese tiempo.

Muchas gracias por el enlace, con la solución de agrupación del enlace me valdría perfectamente, pero he optado por la que plantea Leo con, además, la agrupación condicional.

Leo:
Muchísimas gracias, tu solución me ha funcionado a la perfección.
A ver si entiendo completamente la solución.
El AVG es por si tengo varias entradas con el atributo en una fecha y por un sensor concreto, para que me haga la media. Si no encuentra dato, me pone el valor NULL a través del IF.
Para que me lo junte según la fecha y el sensor se utiliza el GROUP BY.

¿Lo he entendido bien?

La idea es tener en la página una lista con los atributos seleccionables para el sensor y, según los seleccionados por el usuario, armar la tabla. Lo de los atributos media, maxima y minima es por que el ejemplo con el que estoy trabajando para implementarlo es así.

Al final esta mañana acabe haciendo en PHP una búsqueda rellenando un array de fechas y después volver ha hacer la búsqueda utilizando el array en el query como
WHERE fecha = $array. Me funcionaba si solo había un dato por sensor y si había dato para todo los atributos, pero esto funciona mucho mejor.
En tiempo sigue tardando un poquitito, pero es una búsqueda y con lo anterior eran dos, así que va bastante mejor.

Pues lo dicho, muchísimas gracias a los dos
Jonatan Cayón

[jcayon]

Hola:

Una pregunta más sobre este tema, se puede hacer agrupaciones con rangos de datos?
Me explico, una de las opciones que estamos pensando dar es normalizar las fechas, porque el sistema te puede mandar el dato, por ejemplo, a las 12:01, y el siguiente a las 12:07.
Pero para representarlo siempre es mejor que las fechas sean más redondas, entonces, si por ejemplo quieren normalizar las fechas cada 5 minutos pues entonces los datos anteriores seran a las 12:00 y a las 12:05 respectivamente.
O lo que es más rebuscado, si se normaliza a 10 minutos o mas, ambos datos serían a las 12:00 y habria que dar la media.
Es un problema que ya se me ha dado, tenemos un sensor de crecimiento que los datos los doy en una hoja excel y ya me han pedido en este medio normalizar las fechas (por cierto, odio las excel, )

Supongo que tocando el GROUP BY o con la selección condicional poniendo que la fecha este en el rango se pueda hacer, pero igual sabéis alguna forma concreta de hacerlo.

Muchas gracias.
Jonatan Cayón

[leonardo_josue]

Hola de nuevo jcayon:

Tal como lo describes, así es como funciona la consulta... En realidad el uso del agrupador AVG fue un tanto arbitrario, pero efectivamente, es para que te pusiera la media si es que tuvieras más de una entrada para cada sensor y atributo... si puedes asegurar que SÓLO TIENES UNA ENTRADA PARA CADA SENSOR, ATRIBUTO Y FECHA, en realidad podías utilizar SUM, MIN o MAX sin ningún problema:

Código MySQL:

mysql> SELECT
    ->   sensor,
    ->   MIN(IF(atributo = 'Media', valor, NULL)) media,
    ->   MAX(IF(atributo = 'Maxima', valor, NULL)) maxima,
    ->   SUM(IF(atributo = 'Minima', valor, NULL)) minima,
    ->   fechaHora
    -> FROM tabla
    -> GROUP BY sensor, fechaHora;
+--------+-------+--------+--------+---------------------+
| sensor | media | maxima | minima | fechaHora           |
+--------+-------+--------+--------+---------------------+
|      1 |  15.5 |   17.5 |   13.5 | 2012-06-18 17:40:00 |
+--------+-------+--------+--------+---------------------+
1 row in set (0.00 sec)
 

Sin embargo es necesario utilizar cualquiera de ellos (el que quieras), ya que de lo contrario sólo se evaluaría uno de los casos:

Código MySQL:

mysql> SELECT
    ->   sensor,
    ->   IF(atributo = 'Media', valor, NULL) media,
    ->   IF(atributo = 'Maxima', valor, NULL) maxima,
    ->   IF(atributo = 'Minima', valor, NULL) minima,
    ->   fechaHora
    -> FROM tabla
    -> GROUP BY sensor, fechaHora;
+--------+-------+--------+--------+---------------------+
| sensor | media | maxima | minima | fechaHora           |
+--------+-------+--------+--------+---------------------+
|      1 |  15.5 |   NULL |   NULL | 2012-06-18 17:40:00 |
+--------+-------+--------+--------+---------------------+
1 row in set (0.00 sec)
 

En cuanto al rendimiento, revisa que tengas índices creados en tu tabla para mejorar el tiempo de ejecución. La opción que planteabas utilizando INNER JOIN's debería mucho más tardada que la te propongo, pero igual podrías probarla para ver si tiene un mejor rendimiento... sería más o menos así:

Código MySQL:

mysql> SELECT
    ->   T1.sensor,
    ->   T1.valor media,
    ->   T2.valor maxima,
    ->   T3.valor minima,
    ->   T1.fechaHora
    -> FROM
    ->   (SELECT * FROM tabla WHERE atributo = 'Media') T1
    -> INNER JOIN
    ->   (SELECT * FROM tabla WHERE atributo = 'Maxima') T2
    ->   ON T1.sensor = T2.sensor AND T1.fechaHora = T2.fechaHora
    -> INNER JOIN
    ->   (SELECT * FROM tabla WHERE atributo = 'Minima') T3
    ->   ON T1.sensor = T3.sensor AND T1.fechaHora = T3.fechaHora;
+--------+-------+--------+--------+---------------------+
| sensor | media | maxima | minima | fechaHora           |
+--------+-------+--------+--------+---------------------+
|      1 |  15.5 |   17.5 |   13.5 | 2012-06-18 17:40:00 |
+--------+-------+--------+--------+---------------------+
1 row in set (0.00 sec)
 

Pero te repito, podría apostar mi desayuno a que tendrá peor desempeño que la se agrupaciones condicionales.

Saludos
Leo.

[leonardo_josue]

Con respecto a agrupar por rangos... hace tiempo tuve necesidad de hacer algo por el estilo... en ese entonces se requería agrupar la información por horas... para esto, simplemente utilizamos DATE_FORMAT para eliminar los minutos y segundos... es decir, hicimos algo como esto:

Código MySQL:

mysql> SELECT * FROM tabla;
+----------+-------+---------------------+
| atributo | Valor | FechaHora           |
+----------+-------+---------------------+
| Media    |  10.0 | 2012-06-18 17:40:00 |
| Media    |  20.0 | 2012-06-18 17:40:12 |
| Media    |  30.0 | 2012-06-18 17:45:34 |
| Media    |  40.0 | 2012-06-18 18:00:00 |
| Media    |  50.0 | 2012-06-18 18:59:59 |
+----------+-------+---------------------+
5 rows in set (0.00 sec)
 
mysql> SELECT
    ->   fechaHora,
    ->   DATE_FORMAT(fechaHora, '%Y-%m-%d %H:00:00')
    -> FROM tabla;
+---------------------+---------------------------------------------+
| fechaHora           | DATE_FORMAT(fechaHora, '%Y-%m-%d %H:00:00') |
+---------------------+---------------------------------------------+
| 2012-06-18 17:40:00 | 2012-06-18 17:00:00                         |
| 2012-06-18 17:40:12 | 2012-06-18 17:00:00                         |
| 2012-06-18 17:45:34 | 2012-06-18 17:00:00                         |
| 2012-06-18 18:00:00 | 2012-06-18 18:00:00                         |
| 2012-06-18 18:59:59 | 2012-06-18 18:00:00                         |
+---------------------+---------------------------------------------+
5 rows in set (0.00 sec)
 
mysql> SELECT
    ->   atributo,
    ->   DATE_FORMAT(fechaHora, '%Y-%m-%d %H:00:00'),
    ->   SUM(valor) suma
    -> FROM tabla
    -> GROUP BY atributo, DATE_FORMAT(fechaHora, '%Y-%m-%d %H:00:00');
+----------+---------------------------------------------+------+
| atributo | DATE_FORMAT(fechaHora, '%Y-%m-%d %H:00:00') | suma |
+----------+---------------------------------------------+------+
| Media    | 2012-06-18 17:00:00                         | 60.0 |
| Media    | 2012-06-18 18:00:00                         | 90.0 |
+----------+---------------------------------------------+------+
2 rows in set (0.00 sec)
 

Otra opción, sería crear una tabla de los intervalos que necesites... checa este post que contesté hace algunos días, se presenta algo como lo que quieres hacer.

http://www.forosdelweb.com/f86/probl...nsulta-999782/

Saludos
Leo.

[jcayon]

Hola:
Muchas gracias Leo por tus respuestas.
Si que tengo los indices creados en las tablas, cuando estuve mirando mysql vi que era una de las cosas que ayudaban a mejorar el rendimiento, aunque la fecha no la tengo como indice, no se si en este caso ayudaria.

Creo que con SELECT EXPLAIN analiza un poco el rendimiento del select, asi que probare con ambos y ya comento.
Si no con el tiempo que me tarda con los datos que tengo ya puedo ver cual de los query va tardando menos.

Por el lado de las fechas, tu solución de ponerla segun la hora esta guay, pero queria que pudiese ser un poco elegible, creo que en sensores es una buena idea darlo cada 5 min o 30 min.

Mirando un poco he llegado a la siguiente solución:

Código:

SELECT  
  CONCAT(DATE_FORMAT(fechaHora, '%Y-%m-%d'), ' ', SEC_TO_TIME(FLOOR(TIME_TO_SEC(fechahora)/(30*60))*30*60)) AS fechanormalizada,
  AVG( IF( atributo =  'Media', valor, NULL ) ) media, 
  AVG( IF( atributo =  'Maxima', valor, NULL ) ) maxima, 
  AVG( IF( atributo =  'Minima', valor, NULL ) ) minima
				
FROM  `datos recibidos` WHERE `idMaquina`='2' AND `idSensor`='1' 
				
GROUP BY idMaquina, idSensor, fechanormalizada

Como ves, saco el numero de segundos que tiene la fecha, divido entre el numero de minutos con el que quiero el rango, redondeo al entero menor, multiplico por el rango, y rearmo la fecha. Despues lo agrupo por este dato.

Tengo que comprobarlo un poco, pero así funciona. El select en el phpAdmin me tarda 0.0846 con 9636 datos agrupando hasta 275 (tengo uno cada 5 min aprox)

Funcionar funciona, pero me parece un select monstruoso, .

Bueno, muchas gracias
Jonatan Cayón

[leonardo_josue]

Hola de nuevo:

Considero que es pertinente que definas también tu campo fechaHora como índice, ya que esta está relacionada directamente con el criterio de agrupación... con respecto a los intervalos, creo que la mejor opción es la de crear una tabla de intervalos, como lo hice en el ejemplo que te puse de ejemplo... Aquí te ahorrarías el tiempo del cálculo y creo que el INNER JOIN sería más rápido que hacer el cálculo:

Código MySQL:

mysql> SELECT * FROM tabla;
+----------+-------+---------------------+
| atributo | Valor | FechaHora           |
+----------+-------+---------------------+
| Media    |  10.0 | 2012-06-18 17:40:00 |
| Media    |  20.0 | 2012-06-18 17:40:12 |
| Media    |  30.0 | 2012-06-18 17:45:34 |
| Media    |  40.0 | 2012-06-18 18:00:00 |
| Media    |  50.0 | 2012-06-18 18:59:59 |
+----------+-------+---------------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM intervalos;
+-------------+-----------+-----------+
| intervaloId | limiteInf | limiteSup |
+-------------+-----------+-----------+
|           1 | 17:40:00  | 17:44:59  |
|           2 | 17:45:00  | 17:49:00  |
|           3 | 18:00:00  | 18:59:59  |
+-------------+-----------+-----------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   T.atributo, SUM(T.valor), I.limiteInf, I.limiteSup
    -> FROM tabla T
    ->   INNER JOIN intervalos I
    ->     ON TIME(T.fechaHora) BETWEEN i.limiteInf AND i.limiteSup
    -> GROUP BY T.Atributo, DATE(fechaHora), I.limiteInf, I.limiteSup;
+----------+--------------+-----------+-----------+
| atributo | SUM(T.valor) | limiteInf | limiteSup |
+----------+--------------+-----------+-----------+
| Media    |         30.0 | 17:40:00  | 17:44:59  |
| Media    |         30.0 | 17:45:00  | 17:49:00  |
| Media    |         90.0 | 18:00:00  | 18:59:59  |
+----------+--------------+-----------+-----------+
3 rows in set (0.01 sec)
 

Haz también la prueba... independientemente que no sea la mejor opción, te servirá para practicar un poco y estoy seguro que en un futuro le podrás encontrar utilidad. Cuando pruebes, recuerda también definir indices en tu tabla de intervalos para los límites.

Saludos
Leo.