GROUP BY o COUNT Like Field Values ​​- ¿UNPIVOT?

Tengo una tabla con campos de testing, Ejemplo

id | test1 | test2 | test3 | test4 | test5 +----------+----------+----------+----------+----------+----------+ 12345 | P | P | F | I | P 

Entonces, para cada logging quiero saber cuántos Pass, Failed o Incomplete (P, F o I)

¿Hay alguna manera de agrupar valor?

Seudo:

 SELECT ('P' IN (fields)) AS pass WHERE id = 12345 

Tengo alnetworkingedor de 40 campos de testing que necesito agrupar de alguna manera y realmente no quiero escribir esta consulta súper fea y larga. Sí, sé que debería volver a escribir la tabla en dos o tres tablas separadas, pero este es otro problema.

Resultados previstos:

 passed | failed | incomplete +----------+----------+----------+ 3 | 1 | 1 

Sugerencias?

Nota: estoy ejecutando PostgreSQL 7.4 y sí, estamos actualizando

Puede que haya encontrado una solución:

 SELECT id ,l - length(replace(t, 'P', '')) AS nr_p ,l - length(replace(t, 'F', '')) AS nr_f ,l - length(replace(t, 'I', '')) AS nr_i FROM (SELECT id, test::text AS t, length(test::text) AS l FROM test) t 

El truco funciona así:

  • Transforme el tipo de fila en su representación de text.
  • Mida la longitud del carácter.
  • Reemplace el personaje que desea contar y mida el cambio en la longitud.
  • Calcule la longitud de la fila original en la subselección para un uso repetido.

Esto requiere que P, F, I estén presentes en ninguna otra parte de la fila. Use una selección secundaria para excluir cualquier otra columna que pueda interferir.

Probado en 8.4 – 9.1. Nadie usa PostgreSQL 7.4 más hoy en día, tendrás que ponerte a testing. Solo uso funciones básicas, pero no estoy seguro de si es factible enviar el tipo de fila al text en 7.4. Si eso no funciona, tendrás que concatenar todas las columnas de testing una vez a mano:

 SELECT id ,length(t) - length(replace(t, 'P', '')) AS nr_p ,length(t) - length(replace(t, 'F', '')) AS nr_f ,length(t) - length(replace(t, 'I', '')) AS nr_i FROM (SELECT id, test1||test2||test3||test4 AS t FROM test) t 

Esto requiere que todas las columnas NOT NULL sean NOT NULL .

Básicamente, debe desvincular sus datos por testing:

 id | test | result +----------+----------+----------+ 12345 | test1 | P 12345 | test2 | P 12345 | test3 | F 12345 | test4 | I 12345 | test5 | P 

– para que luego pueda agruparlo por resultado de la testing.

Desafortunadamente, PostgreSQL no tiene incorporada la funcionalidad de pivote / falta de actividad, por lo que la forma más sencilla de hacerlo sería algo así como:

 select id, 'test1' test, test1 result from mytable union all select id, 'test2' test, test2 result from mytable union all select id, 'test3' test, test3 result from mytable union all select id, 'test4' test, test4 result from mytable union all select id, 'test5' test, test5 result from mytable union all 

Hay otras maneras de abordar esto, pero con 40 columnas de datos, esto se pondrá realmente feo.

EDIT: un enfoque alternativo –

 select r.result, sum(char_length(replace(replace(test1||test2||test3||test4||test5,excl1,''),excl2,''))) from mytable m, (select 'P' result, 'F' excl1, 'I' excl2 union all select 'F' result, 'P' excl1, 'I' excl2 union all select 'I' result, 'F' excl1, 'P' excl2) r group by r.result 

Puede usar una tabla auxiliar sobre la marcha para convertir columnas en filas, luego podrá aplicar funciones agregadas, algo como esto:

 SELECT SUM(fields = 'P') AS passed, SUM(fields = 'F') AS failed, SUM(fields = 'I') AS incomplete FROM ( SELECT t.id, CASE x.idx WHEN 1 THEN t.test1 WHEN 2 THEN t.test2 WHEN 3 THEN t.test3 WHEN 4 THEN t.test4 WHEN 5 THEN t.test5 END AS fields FROM atable t CROSS JOIN ( SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ) x WHERE t.id = 12345 ) s 

Editar : acabo de ver el comentario sobre 7.4, no creo que esto funcione con esa versión antigua (unnest () llegó mucho más tarde). Si alguien piensa que esto no vale la pena, lo eliminaré.

Tomando la idea de Erwin de utilizar la "representación de filas" como base para la solución un poco más allá y automáticamente "normalizar" la tabla sobre la marcha:

 select id, sum(case when flag = 'F' then 1 else null end) as failed, sum(case when flag = 'P' then 1 else null end) as passed, sum(case when flag = 'I' then 1 else null end) as incomplete from ( select id, unnest(string_to_array(trim(trailing ')' from substr(all_column_values,strpos(all_column_values, ',') + 1)), ',')) flag from ( SELECT id, not_normalized::text AS all_column_values FROM not_normalized ) t1 ) t2 group by id 

El corazón de la solución es el truco de Erwin para hacer un único valor de la fila completa utilizando el cast not_normalized::text . Las funciones de cadena se aplican a la tira del valor de identificación inicial y los corchetes que lo rodean.

El resultado de eso se transforma en una matriz y esa matriz se transforma en un set de resultados utilizando la function unnest ().

Para comprender esa parte, simplemente ejecute las selects internas paso a paso.

Luego, el resultado se agrupa y se countn los valores correspondientes.