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

unpivot oracle

Question

Tengo una tabla con campos de prueba, Ejemplo

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

Entonces, para cada registro 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 alrededor de 40 campos de prueba 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

Answer 1

Básicamente, debe desvincular sus datos por prueba:

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 prueba.

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

Answer 2

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 texto.
• 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 prueba. Solo uso funciones básicas, pero no estoy seguro de si es factible enviar el tipo de fila al texto en 7.4. Si eso no funciona, tendrás que concatenar todas las columnas de prueba 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 .

Answer 3

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

Answer 4

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 conjunto de resultados utilizando la función unnest ().

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

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