postgres를 사용하여 string_agg와 같이 array_agg의 null 값을 제외하는 방법은 무엇입니까?

postgres를 사용하여 string_agg와 같이 array_agg의 null 값을 제외하는 방법은 무엇입니까?

사용할 경우array_agg이름을 수집하기 위해, 나는 쉼표로 내 이름을 구분하지만, 만약을 위해.null값. 이 null은 집계에서 이름으로도 사용됩니다.예:

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;

그것은 돌아옵니다.,Larry,Phil의 대신에Larry,Phil(내 9.1.2에서, 그것은 보여줍니다.NULL,Larry,Phil).

대신에, 만약 내가string_agg()이름만 표시됩니다(공백 쉼표 또는 null 없음).

문제는 제가 가지고 있다는 것입니다.Postgres 8.4서버에 설치되어 있습니다.string_agg()거기서 작동하지 않습니다.array_agg를 string_agg()와 비슷하게 작동시키는 방법이 있습니까?

postgresql-9.3을 사용하면 이를 수행할 수 있습니다.

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;

업데이트: 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;

업데이트(2022-02-19): postgresql-9.4도 포함;

따라서 배열의 모든 값이 null을 반환하는 대신 null인 경우 배열이 비어 있습니다.

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

배열에서 NULL을 제거하는 방법에 대한 일반적인 질문에 대한 최신 답을 찾고 있다면 다음과 같습니다.

array_remove(your_array, NULL)

저는 특히 성능에 대해 궁금했고, 이를 가능한 최상의 대안과 비교하고 싶었습니다.

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
;

pgbench 테스트를 수행한 결과 array_remove()가 두 배 이상 빠르다는 것이 (자신 있게) 입증되었습니다.저는 다양한 배열 크기(10, 100 및 1000 요소)와 그 사이에 랜덤 NULL이 있는 이중 정밀도 수치에 대해 테스트를 수행했습니다.

---

또한 빈칸을 제거하는 데 사용할 수 있습니다('!= NULL).하지만 두 번째 매개 변수는 다음을 허용합니다.anyelement문자열 리터럴로 공백을 나타낼 가능성이 높기 때문에 원하는 형식(일반적으로 배열이 아닌 형식)으로 캐스팅해야 합니다.

예:

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

시도하는 경우:

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

"를 TEXT[](array)라고 가정하고 다음 오류를 발생시킵니다.

오류: 잘못된 형식의 배열 리터럴: "

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

또는, 더 단순하고 더 저렴할 수 있습니다.array_to_string이것은 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

어레이 애그리게이트에서 널을 제거하는 일반적인 문제를 해결하는 데는 array_agg(unnest(array_agg(x))를 수행하거나 사용자 지정 애그리게이트를 생성하는 두 가지 주요 공격 방법이 있습니다.

첫 번째는 위에 표시된 형태입니다.

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

두 번째:

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

두 번째를 부르는 것이 첫 번째보다 (자연스럽게) 조금 더 멋져 보입니다.

x에서 array_agg_notnull(v)을 선택합니다;

이 스레드가 꽤 오래된 것임에도 불구하고 저는 이것을 덧붙입니다. 하지만 저는 작은 배열에서 꽤 잘 작동하는 이 깔끔한 속임수를 우연히 만났습니다.추가 라이브러리나 함수 없이 Postgres 8.4+에서 실행됩니다.

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

그array_to_string()메소드는 실제로 null을 제거합니다.

당신은 당신의 것을 포장해야 합니다.array_aggarray_remove를 사용합니다.

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;

주석에서 제안했듯이 배열에서 널을 대체하는 함수를 작성할 수 있지만 주석에 연결된 스레드에서도 지적했듯이 집계를 생성하고 분할한 다음 다시 집계해야 하는 경우 이러한 유형의 집계 함수의 효율성이 저하됩니다.

어레이에 null을 유지하는 것은 Array_Agg의 (아마도 원하지 않는) 기능이라고 생각합니다.하위 쿼리를 사용하여 이 문제를 방지할 수 있습니다.

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 피들

이것은 매우 간단합니다. 우선 텍스트에 대해 - (마이너스) 연산자를 새로 만듭니다[]:

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[]
);

배열[null]을 빼면 됩니다.

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

이상입니다.

{Y, N}

나는 했습니다.array_except은 셉은컨입니다.array_except(x,y).

array_except(array_agg(x), array_agg(case when x is null then x end))

언급URL : https://stackoverflow.com/questions/13122912/how-to-exclude-null-values-in-array-agg-like-in-string-agg-using-postgres