как исключить нулевые значения в массиве agg, как в строке agg с помощью postgres?

если я использую 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 (в моем 9.1.2, он показывает NULL,Larry,Phil). как в этой скрипка

вместо этого, если я использую string_agg(), он показывает мне только имена (без пустых запятых или нулей), как здесь

проблема что у меня Postgres 8.4 установлен на сервере, и string_agg() не работает. есть ли способ сделать array_agg похожим на string_agg() ?

6 53
sql postgresql postgresql-9.1 postgresql-8.4

6 ответов:

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

или, проще и может быть дешевле, используя 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

SQL Fiddle

16

с 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;
154

при решении общего вопроса об удалении нулей из агрегатов массива существует два основных способа атаки на проблему: либо выполнение 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 = '{}'
);

вызов второго (естественно) немного приятнее, чем первый:

выберите array_agg_notnull (v) из x;

10

Я добавляю это, хотя этот поток довольно старый, но я столкнулся с этим аккуратным трюком, который хорошо работает на небольших массивах. Он работает на Postgres 8.4+ без дополнительных библиотек или функций.

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

The array_to_string() метод фактически избавляется от этих значений.

6

как было предложено в комментариях, вы можете написать функцию для замены нулей в массиве, однако, как также указано в потоке, связанном с комментариями, этот вид снижает эффективность агрегатной функции, если вам нужно создать агрегат, разделить его, а затем снова агрегировать его.

Я думаю, что сохранение нулей в массиве-это просто (возможно, нежелательная) функция 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 Скрипка

3

больший вопрос, почему тянуть все комбо пользователя / группы сразу. Гарантированный ваш пользовательский интерфейс не может обрабатывать все эти данные. Добавление подкачки к негабаритным данным также является плохой идеей. Попросите пользователей отфильтровать набор до того, как они увидят данные. Убедитесь, что ваш набор параметров соединения находится в списке, чтобы они могли фильтровать производительность, если захотят. Иногда 2 запроса сделать пользователей счастливее, если они оба быстро.

-3