Tengo una tabla P que se parece a esto:

ID | Status | Env
1  |   1    | Linux
1  |   1    | Windows
1  |   3    | Mac
2  |   1    | Linux
2  |   1    | Windows
2  |   1    | Mac
3  |   3    | Linux
3  |   0    | Windows
3  |   3    | Mac

Aquí, 1 significa el éxito de la prueba, mientras que cualquier otro número significa algún tipo de falla. Quiero agregar estos datos de tal manera que por cada prueba fallida tenga una lista separada por comas de entornos fallidos en cada fila. Y si no hay fallas, debería haber NULL en las nuevas columnas. La salida se vería algo así como

ID | Status | Env     | Failure_list
1  |   1    | Linux   | Mac
1  |   1    | Windows | Mac
1  |   3    | Mac     | Mac
2  |   1    | Linux   | Null
2  |   1    | Windows | Null
2  |   1    | Mac     | Null
3  |   3    | Linux   | Linux, Windows, Mac
3  |   0    | Windows | Linux, Windows, Mac
3  |   3    | Mac     | Linux, Windows, Mac

Estoy usando la función de copo de nieve LISTAGG () en una consulta como

SELECT ID, STATUS, LISTAGG(ENV, ', ') 
FROM P
GROUP BY ID, STATUS

Esta es la salida que obtengo:

ID | Status | Env
1  |   1    | Linux, Windows
1  |   3    | Mac
2  |   1    | Linux, Windows, Mac
3  |   0    | Windows
3  |   3    | Linux, Mac

¿Cómo puedo cambiar esta consulta para obtener el resultado que estoy buscando?

2
s.m 27 ago. 2020 a las 03:22

1 respuesta

La mejor respuesta

Puede resolver esto con una subconsulta correlacionada:

select
    t.*,
    (
        select listagg(t1.env)
        from mytable t1
        where t1.id = t.id and t1.status <> 1
    ) failure_list
from mytable t

O mejor aún, usando listagg() como una función de ventana, que Snowflake admite:

select 
    t.*,
    listagg(case when status <> 1 then env end) over(partition by id) failure_list
from mytable t
3
GMB 27 ago. 2020 a las 00:24