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

Question 1

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 ()와 유사하게 작동시키는 방법이 있습니까?

Question 2

SQL 바이올린

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_stringnull을 제거하는 사용하면 더 간단하고 저렴할 수 있습니다 .

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 바이올린

Question 3

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;

Question 4

배열 집합체에서 null을 제거하는 일반적인 문제를 해결하는 데는 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)을 선택하십시오.

Question 5

이 스레드가 꽤 오래되었지만 이것을 추가하고 있지만 작은 배열에서 아주 잘 작동하는이 깔끔한 트릭을 만났습니다. 추가 라이브러리 나 기능없이 Postgres 8.4 이상에서 실행됩니다.

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

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

Question 6

배열에서 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을 사용하여 배정 밀도 숫자에 대한 테스트를 수행했습니다.

Question 7

주석에서 제안했듯이 배열의 null을 대체하는 함수를 작성할 수 있지만 주석에 연결된 스레드에서도 지적했듯이 이러한 종류는 집계를 만들어야하는 경우 집계 함수의 효율성을 떨어 뜨립니다. , 분할 한 다음 다시 집계합니다.

배열에 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 FIDDLE

Question 8

매우 간단합니다. 우선 text []에 대한 새 -(빼기) 연산자를 만듭니다 .

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}