jak wykluczyć wartości null w array_agg, jak w string_agg, używając postgres?

Jeśli używam array_aggdo zbierania nazw, moje imiona są oddzielane przecinkami, ale w przypadku, gdy istnieje nullwartość, ta wartość null jest również traktowana jako nazwa w agregacji. Na przykład :

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;

zwraca ,Larry,Philzamiast just Larry,Phil(w moim 9.1.2 pokazuje NULL,Larry,Phil). jak na tych skrzypcach

Zamiast tego, jeśli używam string_agg(), pokazuje mi tylko nazwy (bez pustych przecinków i null), jak tutaj

Problem w tym, że mam Postgres 8.4zainstalowany na serwerze i string_agg()tam nie działa. Czy istnieje sposób, aby funkcja array_agg działała podobnie do string_agg ()?

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

Lub prostsze i może być tańsze, używając, array_to_stringktóre eliminuje wartości null:

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

Z postgresql-9.3 można to zrobić;

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;

Aktualizacja : z 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;

Przy rozwiązywaniu ogólnej kwestii usuwania wartości null z agregatów tablic istnieją dwa główne sposoby zaatakowania problemu: albo wykonanie array_agg (unnest (array_agg (x)), albo utworzenie niestandardowej agregacji.

Pierwsza ma postać pokazaną powyżej :

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

Drugi:

/*
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 = '{}'
);

Wywołanie drugiego jest (naturalnie) trochę ładniejsze niż pierwsze:

wybierz array_agg_notnull (v) z x;

Jeśli szukasz nowoczesnej odpowiedzi na ogólne pytanie, jak usunąć NULL z tablicy , jest to:

array_remove(your_array, NULL)

Byłem szczególnie ciekawy wydajności i chciałem porównać to z najlepszą możliwą alternatywą:

CREATE OR REPLACE FUNCTION strip_nulls(
    IN array_in ANYARRAY
)
RETURNS anyarray AS
'
SELECT
    array_agg(a)
FROM unnest(array_in) a
WHERE
    a IS NOT NULL
;
'
LANGUAGE sql
;

Wykonanie testu pgbench dowiodło (z dużą pewnością), że array_remove () jest nieco ponad dwa razy szybsza . Zrobiłem mój test na liczbach o podwójnej precyzji z różnymi rozmiarami tablic (10, 100 i 1000 elementów) i losowymi wartościami NULL pomiędzy.

Warto również zauważyć, że można tego użyć do usunięcia spacji (``! = NULL). Ale drugi parametr akceptujeanyelement , a ponieważ najprawdopodobniej wskazywałbyś puste miejsce za pomocą literału ciągu, upewnij się, że rzutujesz go na żądaną formę, zwykle nie tablicę.

Na przykład:

select array_remove(array['abc', ''], ''::text);

Jeśli spróbujesz:

select array_remove(array['abc', ''], '');

przyjmie, że „” to TEXT [] (tablica) i zgłosi ten błąd:

BŁĄD: zniekształcony literał tablicy: „”

Dodam to, mimo że ten wątek jest dość stary, ale natknąłem się na tę zgrabną sztuczkę, która działa całkiem dobrze na małych tablicach. Działa na Postgres 8.4+ bez dodatkowych bibliotek lub funkcji.

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

array_to_string()Metoda rzeczywiście pozbywa się null.

Jak zasugerowano w komentarzach, możesz napisać funkcję zastępującą wartości null w tablicy, jednak jak również wskazano w wątku połączonym z komentarzami, ten rodzaj pokonuje wydajność funkcji agregującej, jeśli musisz utworzyć agregację , podziel go, a następnie zsumuj ponownie.

Myślę, że przechowywanie wartości null w tablicy jest tylko (być może niepożądaną) funkcją Array_Agg. Aby tego uniknąć, możesz użyć podzapytań:

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

SQL FIDDLE

Jest to bardzo proste, po prostu najpierw utwórz nowy operator - (minus) dla tekstu [] :

CREATE OR REPLACE FUNCTION diff_elements_text
    (
        text[], text[] 
    )
RETURNS text[] as 
$$
    SELECT array_agg(DISTINCT new_arr.elem)
    FROM
        unnest($1) as new_arr(elem)
        LEFT OUTER JOIN
        unnest($2) as old_arr(elem)
        ON new_arr.elem = old_arr.elem
    WHERE old_arr.elem IS NULL
$$ LANGUAGE SQL IMMUTABLE;

CREATE OPERATOR - (
    PROCEDURE = diff_elements_text,
    leftarg = text[],
    rightarg = text[]
);

I po prostu odejmij tablicę [null]:

select 
    array_agg(x)-array['']
from
    (   select 'Y' x union all
        select null union all
        select 'N' union all
        select '' 
    ) x;

To wszystko:

{T, N}

Większe pytanie brzmi: dlaczego wyciągnąć wszystkie kombinacje użytkowników / grup naraz. Gwarantowane, że Twój interfejs użytkownika nie obsługuje wszystkich tych danych. Dodawanie stronicowania do zbyt dużych danych jest również złym pomysłem. Zachęć użytkowników do przefiltrowania zestawu, zanim zobaczą dane. Upewnij się, że zestaw opcji JOIN znajduje się na liście, aby mogli filtrować pod kątem wydajności, jeśli chcą. Czasami 2 zapytania uszczęśliwiają użytkowników, jeśli oboje są szybcy.