mysql - funciones - having sql

Consulta SQL con avg y grupo por

Question

Tengo algunos problemas para escribir una consulta SQL para MySQL. Tengo una tabla con la siguiente estructura:

mysql> select id, pass, val from data_r1 limit 10;
+------------+--------------+----------------+
| id         | pass         | val            |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 |  44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
| DA02882103 | 5.0000000000 |  44.7265000000 |
| DA02959106 | 1.0000000000 | 186.1470000000 |
| DA02959106 | 2.0000000000 | 148.2660000000 |
| DA02959106 | 3.0000000000 | 111.9050000000 |
| DA02959106 | 4.0000000000 |  76.1485000000 |
| DA02959106 | 5.0000000000 |  44.4007000000 |
| DA02959106 | 4.0000000000 |  76.6485000000 |

Quiero crear una consulta que extraiga la siguiente información de la tabla:

id, AVG of ''val'' for ''pass'' = 1, AVG of ''val'' for ''pass'' = 2, etc

El resultado de la consulta debería verse así:

+------------+---------+---------+---------+---------+---------+---------+---------+
| id         | val_1   | val_2   | val_3   | val_4   | val_5   | val_6   | val_7   |
+------------+---------+---------+---------+---------+---------+---------+---------+
| DA02959106 | 186.147 | 148.266 | 111.905 | 76.3985 | 44.4007 | 0       | 0       |
+------------+---------+---------+---------+---------+---------+---------+---------+

con más filas para cada ''id'' único, por supuesto.

Ya intenté algunas consultas como

SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;

Esto devuelve el resultado correcto, pero tengo que expandirlo con los resultados de los otros valores posibles de ''pase'' (hasta 7)

Traté de usar un SELECT anidado dentro de AVG pero esto no funcionó porque no descubrí cómo limitarlo correctamente al ''id'' actual.

Luego creé Views para representar el resultado de cada consulta para ''pass'' = 1, ''pass'' = 2, etc. Pero para la mayoría de los ids, el valor más alto para ''pass'' es 5. Cuando uso las consultas JOIN para obtener el resultado final de las vistas recibí un conjunto de resultados vacío, porque algunas de las Vistas están vacías / no tienen valores para un ''id'' específico.

¿Algunas ideas?

Answer 1

Si entiendo lo que necesita, intente esto:

SELECT id, pass, AVG(val) AS val_1 
FROM data_r1 
GROUP BY id, pass;

O bien, si solo quiere una fila para cada identificación, esto:

SELECT d1.id,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 1) as val_1,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 2) as val_2,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 3) as val_3,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 4) as val_4,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 5) as val_5,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 6) as val_6,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 7) as val_7
from data_r1 d1
GROUP BY d1.id