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


100

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 ()?


Zobacz ten wątek na liście dyskusyjnej PostgreSQL na ten temat: postgresql.1045698.n5.nabble.com/…
Craig Ringer

Przepraszam, nie sądzę, żeby było rozwiązanie w tym wątku ..
Daud,

W tym wątku są dwa rozwiązania. Jednym z nich jest utworzenie funkcji, a drugim (tylko zasugerowano, ale nie pokazano), na który odpowiedziałem.
Clodoaldo Neto

@Clodoaldo - wszystkie wiersze będą miały wartość kanoniczną in ('y', 'n') ... więc klauzula where wydaje się zbędna. Problem polega na tym, że wewnątrz grupy, jeśli wartością pola kanonicznego jest „Y”, a my zbieramy „N”, to zbierane jest też zero ..
Daud

Ok. Teraz rozumiem. Sprawdź odpowiedź dotyczącą aktualizacji.
Clodoaldo Neto

Odpowiedzi:


28

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


Dzięki. Ale jeśli główne zapytanie zwróci 1000 wierszy, wtedy 2 podzapytania (używając unnest) zostaną uruchomione raz dla każdego wiersza. Czy lepiej będzie tolerować wartości NULL niż wykonywanie 2000 dodatkowych zapytań wybierających?
Daud

@Daud Nowa wersja, która może być tańsza. Dla pewności skorzystaj z wyjaśnienia wyników obu.
Clodoaldo Neto

3
@Clodoaldo Jeśli używasz array_to_string(array_agg(...)), równie dobrze możesz użyć string_agg.
Craig Ringer

1
@Craig Problem w pytaniu to 8.4
Clodoaldo Neto

@Clodoaldo Gah, stare wersje. Dzięki.
Craig Ringer

255

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;

5
To działa i jest szybkie i eleganckie, rozwiązało mi problem podobny do OP. Powód do aktualizacji do wersji 9.3 dla tych, którzy jeszcze tego nie zrobili. +1
Pavel V.,

12
9.4 jest jeszcze bardziej elegancki. Działa jak urok
jmgarnier

2
Wariant 9.4 jest jeszcze lepszy, ponieważ w moim przypadku muszę odfiltrować wartości null.
coladict

Najpierw użyłem zaktualizowanej wersji, ale potem zdałem sobie sprawę, że muszę usunąć null i duplikaty, więc wróciłem do pierwszej sugestii. To duże zapytanie, ale ma na celu utworzenie zmaterializowanego widoku, więc nie jest to duży problem.
Relequestual

12

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;


12

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: „”


@VivekSinha jakiej wersji postgres używasz? Właśnie przetestowałem Twoje zapytanie i otrzymałem „{1,2,3}”. Używam 12.1.
Alexi Theodore

Ach, widzę @ alexi-theodore, co się dzieje na moim końcu. Używałem niestandardowego + zmodyfikowanego sterownika postgres. Kiedy odpytuję bezpośrednio w konsoli, widzę właściwy wynik! Przepraszam za zamieszanie. Usunięto wcześniejszy komentarz i zaopiniowano odpowiedź!
Vivek Sinha

Prawdopodobnie warto zauważyć, że array_remove jest obsługiwana od 9.3
Anatolij Rugalev

9

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.


3

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


Dzięki. Ale potrzebowałem „przypadku” do obsługi wierszy w ramach danej grupy, a podzapytania byłyby tam nieefektywne
Daud,

0

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}


1
array_agg(x) FILTER (WHERE x is not null)wydaje się dużo łatwiejsze: dbfiddle.uk/ ... i tak naprawdę nie potrzebujesz własnej funkcji, możesz po prostu użyć array_remove() dbfiddle.uk/ ...
a_horse_with_no_name

-6

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.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.