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 ()와 유사하게 작동시키는 방법이 있습니까?
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
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
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.
'programing' 카테고리의 다른 글
Angular.js 및 Bootstrap 양식 유효성 검사 스타일 조정 (0) | 2020.10.25 |
---|---|
Gson으로 JSON 배열을 구문 분석하는 방법 (0) | 2020.10.25 |
v2 Android지도의지도 중심을 얻는 방법은 무엇입니까? (0) | 2020.10.25 |
NULL 및 빈 문자열을 테스트하는 NSPredicate (0) | 2020.10.25 |
Android Studio-기기가 연결되었지만 '오프라인' (0) | 2020.10.25 |