Re: [pgsql-ru-general] Агрегация массивов

Как обычно, доброго всем.

Встала задача агрегировать не одиночные значения, а массивы в один
результирующий массив.
Пока смог реализовать такой, неуклюжий вариант:

select id,

string_to_array(
array_to_string(
array_concat(
array_to_string(arr, ',')
)
, ',')
, ',')::int[]

from (
values
(0, array[0]),
(1, array[1,2]),
(1, array[3,4,5])
) v (id, arr)

group by id
;

Результат:
1;"{1,2,3,4,5}"
0;"{0}"

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

--
---
С уважением,
Михаил Наседкин

Привет,

CREATE TABLE att (id integer not null, dat integer[] not null);
INSERT INTO att(id, dat) SELECT 1, ARRAY[1,2];
INSERT INTO att(id, dat) SELECT 1, ARRAY[3,4,5];
INSERT INTO att(id, dat) SELECT 2, ARRAY[6];
INSERT INTO att(id, dat) SELECT 2, ARRAY[7,8,9,10];
SELECT id, array_agg(dat) FROM(SELECT id, unnest(dat) as dat from att) AS
foo GROUP BY id;

id | array_agg
----+--------------
1 | {1,2,3,4,5}
2 | {6,7,8,9,10}
(2 rows)

Надеюсь, что понял Вас правильно.

12 декабря 2010 г. 14:24 пользователь Mihail Nasedkin
<m(dot)nasedkin(at)gmail(dot)com>написал:

> Как обычно, доброго всем.
>
> Встала задача агрегировать не одиночные значения, а массивы в один
> результирующий массив.
> Пока смог реализовать такой, неуклюжий вариант:
>
> select id,
>
> string_to_array(
> array_to_string(
> array_concat(
> array_to_string(arr, ',')
> )
> , ',')
> , ',')::int[]
>
> from (
> values
> (0, array[0]),
> (1, array[1,2]),
> (1, array[3,4,5])
> ) v (id, arr)
>
> group by id
> ;
>
> Результат:
> 1;"{1,2,3,4,5}"
> 0;"{0}"
>
> Результат тот, который требуется, но можно ли сделать агрегацию
> массивов в одной функции?
>
> --
> ---
> С уважением,
> Михаил Наседкин
>
> --
> Sent via pgsql-ru-general mailing list (pgsql-ru-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-ru-general
>

--
// Dmitriy.

Да, Дмитрий, спасибо, Вы правильно поняли, то что нужно.
Стандартная фунция и описана в документации?

--
---
С уважением,
Михаил Наседкин

array_agg и unnest появились в Постресе относительно
недавно в релизе 8.4. Они же описаны и в стандарте SQL.

12 декабря 2010 г. 15:06 пользователь Mihail Nasedkin
<m(dot)nasedkin(at)gmail(dot)com>написал:

> Да, Дмитрий, спасибо, Вы правильно поняли, то что нужно.
> Стандартная фунция и описана в документации?
>
>
> --
> ---
> С уважением,
> Михаил Наседкин
>

--
// Dmitriy.

А все же, наверное есть еще решение, чтоб агрегат кушал сразу массивы?

--
---
С уважением,
Михаил Наседкин

array_agg определяет тип возвращаемого значения (массива)
по своему аргументу, который массивом быть не может.

12 декабря 2010 г. 15:14 пользователь Mihail Nasedkin
<m(dot)nasedkin(at)gmail(dot)com>написал:

> А все же, наверное есть еще решение, чтоб агрегат кушал сразу массивы?
>
> --
> ---
> С уважением,
> Михаил Наседкин
>

--
// Dmitriy.

Буду проверять затратность механизма array_agg(...) ... unnest(...) на
больших массивах.

--
---
С уважением,
Михаил Наседкин

Вот к чему стремился, нарыл в интернете из разных примеров:

CREATE OR REPLACE FUNCTION "сцепить2массива"(anyarray, anyarray)
RETURNS anyarray AS
' BEGIN RETURN $1 || $2; END;'
LANGUAGE 'plpgsql';

CREATE AGGREGATE agregate_array2array (
sfunc = array_concat,
basetype = anyarray,
stype = anyarray,
initcond = '{}'
);

select id,
agregate_array2array(arr)

from (
values
(0, array[0]),
(1, array[1,2]),
(1, array[3,4,5])
) v (id, arr)

group by id
;

Вообще понимания агрегации у меня нету.

12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
> Как обычно, доброго всем.
>
> Встала задача агрегировать не одиночные значения, а массивы в один
> результирующий массив.
> Пока смог реализовать такой, неуклюжий вариант:
>
> select id,
>
> string_to_array(
> array_to_string(
> array_concat(
> array_to_string(arr, ',')
> )
> , ',')
> , ',')::int[]
>
> from (
> values
> (0, array[0]),
> (1, array[1,2]),
> (1, array[3,4,5])
> ) v (id, arr)
>
> group by id
> ;
>
> Результат:
> 1;"{1,2,3,4,5}"
> 0;"{0}"
>
> Результат тот, который требуется, но можно ли сделать агрегацию
> массивов в одной функции?
>
> --
> ---
> С уважением,
> Михаил Наседкин
>

--
---
С уважением,
Михаил Наседкин

А ошибка в имени функции:

CREATE AGGREGATE agregate_array2array (
sfunc = "сцепить2массива",
basetype = anyarray,
stype = anyarray,
initcond = '{}'
);

12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
> Вот к чему стремился, нарыл в интернете из разных примеров:
>
> CREATE OR REPLACE FUNCTION "сцепить2массива"(anyarray, anyarray)
> RETURNS anyarray AS
> ' BEGIN RETURN $1 || $2; END;'
> LANGUAGE 'plpgsql';
>
> CREATE AGGREGATE agregate_array2array (
> sfunc = array_concat,
> basetype = anyarray,
> stype = anyarray,
> initcond = '{}'
> );
>
> select id,
> agregate_array2array(arr)
>
> from (
> values
> (0, array[0]),
> (1, array[1,2]),
> (1, array[3,4,5])
> ) v (id, arr)
>
> group by id
> ;
>
> Вообще понимания агрегации у меня нету.
>
> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
>> Как обычно, доброго всем.
>>
>> Встала задача агрегировать не одиночные значения, а массивы в один
>> результирующий массив.
>> Пока смог реализовать такой, неуклюжий вариант:
>>
>> select id,
>>
>> string_to_array(
>> array_to_string(
>> array_concat(
>> array_to_string(arr, ',')
>> )
>> , ',')
>> , ',')::int[]
>>
>> from (
>> values
>> (0, array[0]),
>> (1, array[1,2]),
>> (1, array[3,4,5])
>> ) v (id, arr)
>>
>> group by id
>> ;
>>
>> Результат:
>> 1;"{1,2,3,4,5}"
>> 0;"{0}"
>>
>> Результат тот, который требуется, но можно ли сделать агрегацию
>> массивов в одной функции?
>>
>> --
>> ---
>> С уважением,
>> Михаил Наседкин
>>
>
>
>
> --
> ---
> С уважением,
> Михаил Наседкин
>

--
---
С уважением,
Михаил Наседкин

В последний мой вариант отличается от первого включением в
результирующий массив всех (и null-значений элементов).

Связка string_to_array(array_to_string(...), ...) полезна в случае,
если нужно отбросить null-значения. Для второго варианта эту связку
также можно использовать в функции "сцепить2массива".

Быстродействие этих двух вариантов у меня не отличалось.

12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
> А ошибка в имени функции:
>
> CREATE AGGREGATE agregate_array2array (
> sfunc = "сцепить2массива",
> basetype = anyarray,
> stype = anyarray,
> initcond = '{}'
> );
>
> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
>> Вот к чему стремился, нарыл в интернете из разных примеров:
>>
>> CREATE OR REPLACE FUNCTION "сцепить2массива"(anyarray, anyarray)
>> RETURNS anyarray AS
>> ' BEGIN RETURN $1 || $2; END;'
>> LANGUAGE 'plpgsql';
>>
>> CREATE AGGREGATE agregate_array2array (
>> sfunc = array_concat,
>> basetype = anyarray,
>> stype = anyarray,
>> initcond = '{}'
>> );
>>
>> select id,
>> agregate_array2array(arr)
>>
>> from (
>> values
>> (0, array[0]),
>> (1, array[1,2]),
>> (1, array[3,4,5])
>> ) v (id, arr)
>>
>> group by id
>> ;
>>
>> Вообще понимания агрегации у меня нету.
>>
>> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
>>> Как обычно, доброго всем.
>>>
>>> Встала задача агрегировать не одиночные значения, а массивы в один
>>> результирующий массив.
>>> Пока смог реализовать такой, неуклюжий вариант:
>>>
>>> select id,
>>>
>>> string_to_array(
>>> array_to_string(
>>> array_concat(
>>> array_to_string(arr, ',')
>>> )
>>> , ',')
>>> , ',')::int[]
>>>
>>> from (
>>> values
>>> (0, array[0]),
>>> (1, array[1,2]),
>>> (1, array[3,4,5])
>>> ) v (id, arr)
>>>
>>> group by id
>>> ;
>>>
>>> Результат:
>>> 1;"{1,2,3,4,5}"
>>> 0;"{0}"
>>>
>>> Результат тот, который требуется, но можно ли сделать агрегацию
>>> массивов в одной функции?
>>>
>>> --
>>> ---
>>> С уважением,
>>> Михаил Наседкин
>>>
>>
>>
>>
>> --
>> ---
>> С уважением,
>> Михаил Наседкин
>>
>
>
> --
> ---
> С уважением,
> Михаил Наседкин
>

--
---
С уважением,
Михаил Наседкин

Кстати, в Постгресе есть функция array_cat, которая делает
то же, что и Ваша "специть2массива".

Я полагаю, что array_agg реализован эффективнее, чем собственно
созданный аггрегат, вызывающий ту или иную функцию конкатенации.

Впрочем, это надо проверять. Решать Вам.

12 декабря 2010 г. 21:00 пользователь Mihail Nasedkin
<m(dot)nasedkin(at)gmail(dot)com>написал:

> В последний мой вариант отличается от первого включением в
> результирующий массив всех (и null-значений элементов).
>
> Связка string_to_array(array_to_string(...), ...) полезна в случае,
> если нужно отбросить null-значения. Для второго варианта эту связку
> также можно использовать в функции "сцепить2массива".
>
> Быстродействие этих двух вариантов у меня не отличалось.
>
> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
> > А ошибка в имени функции:
> >
> > CREATE AGGREGATE agregate_array2array (
> > sfunc = "сцепить2массива",
> > basetype = anyarray,
> > stype = anyarray,
> > initcond = '{}'
> > );
> >
> > 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
> >> Вот к чему стремился, нарыл в интернете из разных примеров:
> >>
> >> CREATE OR REPLACE FUNCTION "сцепить2массива"(anyarray, anyarray)
> >> RETURNS anyarray AS
> >> ' BEGIN RETURN $1 || $2; END;'
> >> LANGUAGE 'plpgsql';
> >>
> >> CREATE AGGREGATE agregate_array2array (
> >> sfunc = array_concat,
> >> basetype = anyarray,
> >> stype = anyarray,
> >> initcond = '{}'
> >> );
> >>
> >> select id,
> >> agregate_array2array(arr)
> >>
> >> from (
> >> values
> >> (0, array[0]),
> >> (1, array[1,2]),
> >> (1, array[3,4,5])
> >> ) v (id, arr)
> >>
> >> group by id
> >> ;
> >>
> >> Вообще понимания агрегации у меня нету.
> >>
> >> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
> >>> Как обычно, доброго всем.
> >>>
> >>> Встала задача агрегировать не одиночные значения, а массивы в один
> >>> результирующий массив.
> >>> Пока смог реализовать такой, неуклюжий вариант:
> >>>
> >>> select id,
> >>>
> >>> string_to_array(
> >>> array_to_string(
> >>> array_concat(
> >>> array_to_string(arr, ',')
> >>> )
> >>> , ',')
> >>> , ',')::int[]
> >>>
> >>> from (
> >>> values
> >>> (0, array[0]),
> >>> (1, array[1,2]),
> >>> (1, array[3,4,5])
> >>> ) v (id, arr)
> >>>
> >>> group by id
> >>> ;
> >>>
> >>> Результат:
> >>> 1;"{1,2,3,4,5}"
> >>> 0;"{0}"
> >>>
> >>> Результат тот, который требуется, но можно ли сделать агрегацию
> >>> массивов в одной функции?
> >>>
> >>> --
> >>> ---
> >>> С уважением,
> >>> Михаил Наседкин
> >>>
> >>
> >>
> >>
> >> --
> >> ---
> >> С уважением,
> >> Михаил Наседкин
> >>
> >
> >
> > --
> > ---
> > С уважением,
> > Михаил Наседкин
> >
>
>
> --
> ---
> С уважением,
> Михаил Наседкин
>
> --
> Sent via pgsql-ru-general mailing list (pgsql-ru-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-ru-general
>

--
// Dmitriy.

Да, я тоже позже заметил, что создал дубликат функции.

Проверял эффективность трех вариантов агрегации массивов в массив
упомянутых в данном обсуждении на своих наборах данных (не более
миллиона идентификаторов во всех массивах):
1. string_to_array(
array_to_string(
array_concat(
array_to_string(
.........
2. Вариант Дмитрия array_agg(...) ... unnest(...)
3. Агрегат на основе array_cat

Так вот отличий не увидел. Чисто эстетически нравится последний вариант.

13.12.10, Dmitriy Igrishin<dmitigr(at)gmail(dot)com> написал(а):
> Кстати, в Постгресе есть функция array_cat, которая делает
> то же, что и Ваша "специть2массива".
>
> Я полагаю, что array_agg реализован эффективнее, чем собственно
> созданный аггрегат, вызывающий ту или иную функцию конкатенации.
>
> Впрочем, это надо проверять. Решать Вам.
>
> 12 декабря 2010 г. 21:00 пользователь Mihail Nasedkin
> <m(dot)nasedkin(at)gmail(dot)com>написал:
>
>> В последний мой вариант отличается от первого включением в
>> результирующий массив всех (и null-значений элементов).
>>
>> Связка string_to_array(array_to_string(...), ...) полезна в случае,
>> если нужно отбросить null-значения. Для второго варианта эту связку
>> также можно использовать в функции "сцепить2массива".
>>
>> Быстродействие этих двух вариантов у меня не отличалось.
>>
>> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
>> > А ошибка в имени функции:
>> >
>> > CREATE AGGREGATE agregate_array2array (
>> > sfunc = "сцепить2массива",
>> > basetype = anyarray,
>> > stype = anyarray,
>> > initcond = '{}'
>> > );
>> >
>> > 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
>> >> Вот к чему стремился, нарыл в интернете из разных примеров:
>> >>
>> >> CREATE OR REPLACE FUNCTION "сцепить2массива"(anyarray, anyarray)
>> >> RETURNS anyarray AS
>> >> ' BEGIN RETURN $1 || $2; END;'
>> >> LANGUAGE 'plpgsql';
>> >>
>> >> CREATE AGGREGATE agregate_array2array (
>> >> sfunc = array_concat,
>> >> basetype = anyarray,
>> >> stype = anyarray,
>> >> initcond = '{}'
>> >> );
>> >>
>> >> select id,
>> >> agregate_array2array(arr)
>> >>
>> >> from (
>> >> values
>> >> (0, array[0]),
>> >> (1, array[1,2]),
>> >> (1, array[3,4,5])
>> >> ) v (id, arr)
>> >>
>> >> group by id
>> >> ;
>> >>
>> >> Вообще понимания агрегации у меня нету.
>> >>
>> >> 12.12.10, Mihail Nasedkin<m(dot)nasedkin(at)gmail(dot)com> написал(а):
>> >>> Как обычно, доброго всем.
>> >>>
>> >>> Встала задача агрегировать не одиночные значения, а массивы в один
>> >>> результирующий массив.
>> >>> Пока смог реализовать такой, неуклюжий вариант:
>> >>>
>> >>> select id,
>> >>>
>> >>> string_to_array(
>> >>> array_to_string(
>> >>> array_concat(
>> >>> array_to_string(arr, ',')
>> >>> )
>> >>> , ',')
>> >>> , ',')::int[]
>> >>>
>> >>> from (
>> >>> values
>> >>> (0, array[0]),
>> >>> (1, array[1,2]),
>> >>> (1, array[3,4,5])
>> >>> ) v (id, arr)
>> >>>
>> >>> group by id
>> >>> ;
>> >>>
>> >>> Результат:
>> >>> 1;"{1,2,3,4,5}"
>> >>> 0;"{0}"
>> >>>
>> >>> Результат тот, который требуется, но можно ли сделать агрегацию
>> >>> массивов в одной функции?
>> >>>
>> >>> --
>> >>> ---
>> >>> С уважением,
>> >>> Михаил Наседкин
>> >>>
>> >>
>> >>
>> >>
>> >> --
>> >> ---
>> >> С уважением,
>> >> Михаил Наседкин
>> >>
>> >
>> >
>> > --
>> > ---
>> > С уважением,
>> > Михаил Наседкин
>> >
>>
>>
>> --
>> ---
>> С уважением,
>> Михаил Наседкин
>>
>> --
>> Sent via pgsql-ru-general mailing list (pgsql-ru-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-ru-general
>>
>
>
>
> --
> // Dmitriy.
>

--
---
С уважением,
Михаил Наседкин