12 respuestas en este tema

[AngelF]

Hola ¿qué tal?.

Tengo un problemilla que quisiera me dierais vuestra ilustrada opinión.

Tengo una base de datos en Firebird con los registros de humedad y temperatura de varios sensores. Una consulta típica seria por ejemplo saber la temperatura media, mínima y máxima para cada dia, de cada sensor, en un mes, lo que me devolvería una tabla como la siguiente:

Día Sensor Media         Máxima         Mínima
========================================
1 S1 75,00 85,00 10,00
1 S2 65,13 80,25 8,35
1 S3 45,12 65,00 25,23
2 S1 35,00 40,00 30,25
2 S2 45,00 55,25 10,58
2 S3 65,25 80,00 10,00
....
....
....
31 S1 75,00 85,00 10,00
31 S2 65,13 80,25 8,35
31 S3 45,12 65,00 25,23
=========================================

En este ejemplo he tomado sólo 3 sensores, pero pueden ser hasta 60. Los números son totalmente inventados.

El caso es que quiero obtener para cada sensor, de los resultados obtenidos en la tabla, la media de las medias, la media de las máximas y la media de las mínimas, es decir, una tabla tal que asi:

Sensor Media_Media Media_Max Media_Min
=========================================
S1 85,00 95,25 47,58
S2 65,48 85,47 40,58
S3 75,89 80,87 50,89
=========================================

En este caso perdemos la variable día, porque cada dato es el promedio de todos los datos en un mes.

¿Cómo lo hago? Creo que habría que mandar los datos de la consulta 1 a una tabla temporal y sobre esta tabla realizar la consulta que me devuelva la tabla 2 ¿no? Pero no sé cómo hacerlo, ¿qué componente utilizo para crear la tabla temporal y sobre ella lanzar la segunda consulta?

Gracias y un saludo al foro.

[Wilson]

Coloca la estructura de la tabla y con mucho gusto te ayudaremos.

Saludos

[TiammatMX]

A riesgo de equivocarme (siendo SQL ANSI 92 es casi seguro que la tiene) hay en SQL una función que te devuelve la media de una serie de números. Disculparás que no te ponga la sintaxis, pero ando con una mala memoria que no tienes idea... 

[AngelF]

Gracias por vuestras respuestas, pero creo que no me he explicado bien.

Sé como hacer el promedio de una serie con SQL. Lo que quiero es tomar los resultados que ya me devuelve la consulta 1 (la primera tabla de mi post inicial) pasarlos a una tabla temporal que tenga los campos SENSOR (char), MEDIA, MAXIMO Y MINIMO (todos estos campos del tipo numeric(12,2)) y sobre esta tabla temporal hacer la siguiente consulta SQL:

SELECT
    SENSOR,
    AVG(MEDIA) AS Media_Media,
    AVG(MAXIMO) AS Media_Max,
    AVG(MINIMO) AS Media_Min
FROM
    TABLA_TEMPO
GROUP BY SENSOR
ORDER BY SENSOR

para que me devuelva el resultado esperado:


Sensor Media_Media Media_Max Media_Min
--------------------------------------------------------------------
S1 85,00 95,25 47,58
S2 65,48 85,47 40,58
S3 75,89 80,87 50,89
--------------------------------------------------------------------

Lo que no sé cómo hacer es pasar los datos de la consulta 1 a la tabla temporal y luego hacer la consulta SQL de medias en la tabla temporal.

[Wilson]

Como no me diste la estructura de la tabla original, voy a suponerla:

delphi

CREATE TABLE MEDICIONES (
    DIA          DATE,
    SENSOR       INTEGER,
    TEMPERATURA  NUMERIC(12,2)

Con esta consulta deberías obtener lo que buscas sin necesidad de tablas temporales.

delphi

SELECT SENSOR_FINAL,
    AVG(MEDIA) AS MEDIA_MEDIA,
    AVG(MAXIMO) AS MEDIA_MAX,
    AVG(MINIMO) AS MEDIA_MIN
    FROM
    (SELECT DIA, SENSOR AS SENSOR_FINAL, AVG(TEMPERATURA) AS MEDIA, MAX(TEMPERATURA) AS MAXIMO,
     MIN(TEMPERATURA) AS MINIMO FROM MEDICIONES WHERE DIA BETWEEN '01.01.2014' AND
    '21.01.204' GROUP BY SENSOR, DIA)
    GROUP BY SENSOR_FINAL

[AngelF]

Muchas gracias Wilson por tu código, es muy interesante y desde luego una forma que desconocía para lanzar una consulta anidada.
Disculpa que no te pase la estructura de la base, pero es que no soy el propietario y no sé si puedo hacerlo.
En cualquier caso, el ejemplo que pones es perfectamente válido.

Sin embargo, Wilson, tú código que es perfectamente válido ¿no sería un poco lento, puesto que vuelves a lanzar la consulta 1 y anidada sobre ella, haces la consulta de las medias?
Volviendo al tema de la tabla temporal, ¿no habría una manera más rápida quizá, de tomar el resultado de la consulta 1 (que ya está lanzada y resuelta y que por lo tanto no perdemos tiempo en volver a lanzarla), pasarlo a una tabla temporal y sobre esta tabla temporal realizar la consulta de las medias?
Pienso que sería más rápido puesto que sólo realizas la consulta de las medias. ¿Se podría hacer como lo planteo?

[Sergio]

A no ser que la consulta primera es super super compleja (que no lo parece) y tarde 10 minutos en ejecutarse, no tiene mucho sentido usar tablas temporales, si fuese así, podrías calcular esas medias y maximos a mano desde delphi sin usar SQLs, aunque si tarda tanto la SQL primera, mirate a ver si la SQL no está bien diseñada (el from debe ser por la tabla que luego uses al filtrar, por ejemplo) o te faltan índices.

Pero si hablamos de que tarda cosa de 1 segundo, entonces ni te lo pienses, usa a FireBird para que te haga ese trabajo sucio, debería ser muy rápido, más que con temporales.

En mi empresa siempre usamos SQL para estas cosas, y si resulta lento, toca currarse las SQL y los índices hasta que sea rápido, porque si usando "trucos sucios" consigues más velocidad que FireBird, algo has hecho mal y arreglarlo te mejora todo el rendimiento.

[Rolphy Reyes]

Saludos.

Tal como explica Sergio si no demora mucho en realizar la consulta puedes hacerlo como Wilson te indico.

Ahora bien, si aun insiste con la idea de las tablas temporales revisa estos enlaces:
Info1
Info2
Info3

[AngelF]

Ok, me habéis convencido, voy a probar con el código de Wilson.

Mi idea era evitar lanzar nuevamente la consulta porque sí que resulta lenta, pero no porque no esté optimizada (que también puede ser), sino porque la BD es enorme, tiene muchos índices, y permite hacer bastantes cosas como promedio por hora, por día, por día de la semana, por sensor, etc.

Lo pruebo y comento.

¡Muchas gracias!

[genriquez]

Hola, yo tengo la misma situación en una base de datos mía, que genera casi un millón de registros mensuales, ya tengo información de 2 años,  probé en su momento con tablas temporales, sin embargo la velocidad de ejecución era muy similar a utilizar tablas anidadas.

Depende mucho de la organización de las consultas, los índices y demás opciones de optimización de la base de datos, pero al menos en mi caso el tiempo es similar y la sencillez que otorga las consultas anidadas, me dieron la pauta para usarlas.

Saludos.

[AngelF]

Gracias Genriquez por aportar tu experiencia.

En efecto, he hecho algunas pruebas preliminares y el lanzar la consulta anidada no es tan lento como suponía. En realidad, me ha sorprendido lo rápido que es.
No vale la pena en absoluto complicarse la vida con tablas temporales ni zarandajas.

Gracias a la ayuda de todos, parece que el problema está resuelto.

¡Muchas gracias!

[Sergio]

Para estas SQLs lentas y complejas, de verdad que ayuda ejecurlas desde FlamRobin o similar y ver el "plan" que te genera, solo con localizar alguna palabra "natural" y crear un índice para que desaparezca hace que mejore todo.

Y si no hay más "naturales" y aún os tarda mucho, os aseguro que reordenando cosas se consiguen maravillas, el optimizador de plan de Firebird es mejorable en muchas ocasiones (aunque la V2.5 mejoró muchisimo en esto), y consultas de 5 segundos que siempre pensamos que no se podrían mejorar, pasaban a 0.1s tras un poco de reordenación.

Lo más espectacular suele ser qué tabla usas como "from", debe ser la más usada en los filtros, y bueno, cada caso puede variar, pero si te empeñas, se pueden bajar del segundo casi casi el 100% de las consultas. Hay varios trucos, como detectar si algo existe con un exists(select first 1 'A' from mitabla) y cosas así que aunque parecen iguales a otras maneras de hacerlo, afectan mucho a la generación del plan y a la velocidad final.

Respecto del tamaño del fichero de base de datos, tenemos clientes con 90 GB de fichero y prácicamente no se nota en nada, excepto las copias de seguridad que tardan horas, claro. Eso no hace que una consulta sea lenta, a no ser que la consulta involucre leerse muchos miles de registros, y eso también te lo dice FlameRobin, por cierto.

[AngelF]

Muchas gracias, Sergio, por la valiosa información que aportas. Estoy aprendiendo un montón con este hilo.

Monté el código de Wilson en la versión definitiva y va como un tiro, incluso más rápido que la primera vez que se ejecuta la consulta. Supongo que es lógico ya que cuando lanzas dos veces una misma consulta, o parecida, la segunda vez es algo más rápido.

En cualquier caso, los índices deben de estar bien afinados porque tarda entre 0,5 y 1,5 segundos sobre una BD enorme, de 4GB con 40 millones de registros.
Me tomo nota del FlameRobin para echarle un ojo.

Un saludo.