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

---

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

---

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;

---

배열 집합체에서 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)을 선택하십시오.

---

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

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

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

---

As has been suggested in the comments you can write a function to replace nulls in an array, however as also pointed out in the thread linked to in the comments, this kind of defeats the efficiency of the aggregate function if you have to create an aggregate, split it then aggregate it again.

I think keeping nulls in the array is just a (perhaps unwanted) feature of Array_Agg. You could use subqueries to avoid this:

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

---

A bigger question is why pull all user/group combos at once. Guaranteed your UI cant handle all that data. Adding paging to oversized data is also a bad idea. Get your users to filter the set before they see data. Make sure your JOIN option set is in the list so they can filter for performance if they want to. Sometimes 2 queries make users happier if they are both fast.

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