example - listagg sql server

¿Cómo excluir valores nulos en array_agg como en string_agg usando postgres?

Question

Si uso array_agg para recopilar nombres, obtengo mis nombres separados por comas, pero en caso de que haya un valor null , ese nulo también se toma como un nombre en el agregado. Por ejemplo :

SELECT g.id,
       array_agg(CASE WHEN g.canonical = ''Y'' THEN g.users ELSE NULL END) canonical_users,
       array_agg(CASE WHEN g.canonical = ''N'' THEN g.users ELSE NULL END) non_canonical_users
FROM groups g
GROUP BY g.id;

vuelve ,Larry,Phil lugar de solo Larry,Phil (en mi 9.1.2, muestra NULL,Larry,Phil ). como en this violín

En cambio, si uso string_agg() , solo me muestra los nombres (sin comas o nulos vacíos) como here

El problema es que tengo Postgres 8.4 instalado en el servidor y string_agg() no funciona allí. ¿Hay alguna manera de hacer que array_agg funcione de manera similar a string_agg ()?

Answer 1

Al resolver la cuestión general de eliminar nulos de los agregados de arreglos, hay dos formas principales de atacar el problema: haciendo array_agg (unnest (array_agg (x)) o creando un agregado personalizado.

La primera es de la forma mostrada above :

SELECT 
    array_agg(u) 
FROM (
    SELECT 
        unnest(
            array_agg(v)
        ) as u 
    FROM 
        x
    ) un
WHERE 
    u IS NOT NULL;

El segundo:

/*
With reference to
http://ejrh.wordpress.com/2011/09/27/denormalisation-aggregate-function-for-postgresql/
*/
CREATE OR REPLACE FUNCTION fn_array_agg_notnull (
    a anyarray
    , b anyelement
) RETURNS ANYARRAY
AS $$
BEGIN

    IF b IS NOT NULL THEN
        a := array_append(a, b);
    END IF;

    RETURN a;

END;
$$ IMMUTABLE LANGUAGE ''plpgsql'';

CREATE AGGREGATE array_agg_notnull(ANYELEMENT) (
    SFUNC = fn_array_agg_notnull,
    STYPE = ANYARRAY,
    INITCOND = ''{}''
);

Llamar al segundo es (naturalmente) un poco más bonito que el primero:

seleccione array_agg_notnull (v) de x;

Answer 2

Como se ha sugerido en los comentarios, puede escribir una función para reemplazar los nulos en una matriz, sin embargo, como también se señaló en el hilo enlazado en los comentarios, este tipo anula la eficiencia de la función agregada si tiene que crear un agregado , dividirlo y luego agregarlo de nuevo.

Creo que mantener nulos en la matriz es solo una característica (quizás no deseada) de Array_Agg. Podrías usar subconsultas para evitar esto:

SELECT  COALESCE(y.ID, n.ID) ID,
        y.Users,
        n.Users
FROM    (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
            FROM    Groups g
            WHERE   g.Canonical = ''Y''
            GROUP BY g.ID
        ) y
        FULL JOIN 
        (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
            FROM    Groups g
            WHERE   g.Canonical = ''N''
            GROUP BY g.ID
        ) n
            ON n.ID = y.ID

FIDDLE SQL

Answer 3

Con postgresql-9.3 se puede hacer esto;

SELECT g.id,
   array_remove(array_agg(CASE WHEN g.canonical = ''Y'' THEN g.users ELSE NULL END), NULL) canonical_users,
   array_remove(array_agg(CASE WHEN g.canonical = ''N'' THEN g.users ELSE NULL END), NULL) non_canonical_users
FROM groups g 
GROUP BY g.id;

Actualización : con postgresql-9.4;

SELECT g.id,
   array_agg(g.users) FILTER (WHERE g.canonical = ''Y'') canonical_users,
   array_agg(g.users) FILTER (WHERE g.canonical = ''N'') non_canonical_users
FROM groups g 
GROUP BY g.id;

Answer 4

Estoy agregando esto a pesar de que este hilo es bastante antiguo, pero me encontré con este truco que funciona bastante bien en arreglos pequeños. Se ejecuta en Postgres 8.4+ sin bibliotecas o funciones adicionales.

string_to_array(array_to_string(array_agg(my_column)))::int[]

El método array_to_string() realidad se deshace de los nulos.

Answer 5

Una pregunta más importante es por qué tirar todos los combos de usuario / grupo a la vez. Garantizado su interfaz de usuario no puede manejar todos esos datos. Agregar paginación a datos de gran tamaño también es una mala idea. Haga que sus usuarios filtren el conjunto antes de que vean los datos. Asegúrese de que su conjunto de opciones de JOIN esté en la lista para que puedan filtrar el rendimiento si así lo desean. A veces, 2 consultas hacen a los usuarios más felices si son rápidos.

Answer 6

SQL Fiddle

select
    id,
    (select array_agg(a) from unnest(canonical_users) a where a is not null) canonical_users,
    (select array_agg(a) from unnest(non_canonical_users) a where a is not null) non_canonical_users
from (
    SELECT g.id,
           array_agg(CASE WHEN g.canonical = ''Y'' THEN g.users ELSE NULL END) canonical_users,
           array_agg(CASE WHEN g.canonical = ''N'' THEN g.users ELSE NULL END) non_canonical_users
    FROM groups g
    GROUP BY g.id
) s

O, más simple y puede ser más barato, utilizando array_to_string que elimina nulos:

SELECT
    g.id,
    array_to_string(
        array_agg(CASE WHEN g.canonical = ''Y'' THEN g.users ELSE NULL END)
        , '',''
    ) canonical_users,
    array_to_string(
        array_agg(CASE WHEN g.canonical = ''N'' THEN g.users ELSE NULL END)
        , '',''
    ) non_canonical_users
FROM groups g
GROUP BY g.id

SQL Fiddle