Thread: is there a way to deliver an array over column from a query window?

Hi,

I really don't know how to ask for what I'm looking for; but I think,
may be, calling it an ARRAY yielding aggregate function for use within a
query WINDOW would do?

I'm looking for something like:

SELECT DISTINCT issuer,amount, array(REFERENCE) over (partition by
invoice_nr) from invoices;

where the invoices table contain the usual invoice data, like:
- amount
- time of issue
- the issuer ID
- and the above REFERENCE, is the reference to a bank transfer record in
another table.

The thing is, that I have multiple funds transfer records referring to a
single invoice; and (I currently think, that :) I need  to fetch them
all in a single column - so preferrably within an ARRAY of references
(or just transfer IDs) to table containing funds transfer records. In
other words, I wouldn't like to collapse the query at the application
level ... but "if everything else fails", that is the last resort I keep
in mind.

The reason I'm not taking the last resort now, is that I also have
multiple invoices to a single funds transfer record. And ultimately I'm
hoping to cook a query, that would window-and-balance the two tables
(invoices, and funds transfer) - so that most of the job is done by
SQL/funciton/view, not by the external application.

I've looked up postgres documentation, looking for a sort of "aggregate"
function, that would not compute anything, but just build an ARRAY from
all its input, but couldn't locate any.

Is there a way to achieve this in postgresql?

-R

maybe,

SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by invoice_nr) from invoices;

RIGHT. Thenx. (and the first thing I did, I've read the doc on array_agg().... what stress makes from people :(

thenx again,


-R

2013/3/24 Rafał Pietrak <rafal@zorro.isa-geek.com>

Hi,

I really don't know how to ask for what I'm looking for; but I think, may be, calling it an ARRAY yielding aggregate function for use within a query WINDOW would do?

I'm looking for something like:

SELECT DISTINCT issuer,amount, array(REFERENCE) over (partition by invoice_nr) from invoices;

where the invoices table contain the usual invoice data, like:
- amount
- time of issue
- the issuer ID
- and the above REFERENCE, is the reference to a bank transfer record in another table.

The thing is, that I have multiple funds transfer records referring to a single invoice; and (I currently think, that :) I need  to fetch them all in a single column - so preferrably within an ARRAY of references (or just transfer IDs) to table containing funds transfer records. In other words, I wouldn't like to collapse the query at the application level ... but "if everything else fails", that is the last resort I keep in mind.

The reason I'm not taking the last resort now, is that I also have multiple invoices to a single funds transfer record. And ultimately I'm hoping to cook a query, that would window-and-balance the two tables (invoices, and funds transfer) - so that most of the job is done by SQL/funciton/view, not by the external application.

I've looked up postgres documentation, looking for a sort of "aggregate" function, that would not compute anything, but just build an ARRAY from all its input, but couldn't locate any.

Is there a way to achieve this in postgresql?

-R


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

W dniu 03/24/2013 12:06 PM, Misa Simic pisze:

maybe,

SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by invoice_nr) from invoices;

RIGHT. Thenx. (and the first thing I did, I've read the doc on array_agg().... what stress makes from people :(

Actually, I have a problem with that (which I haven't noticed earlier because the data I'm having, don't have to many "duplicates" that cause it). The problem is, that:
--------------------------------------
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c )  FROM testy k where k.e <> 'email' and k.c='1035049' ;
  a   |    b     |    c    |           array_agg          
------+----------+---------+-------------------------------
 1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---------------------------------------

is _almost_ fine. But I actually need to have control over the order in which the array gathered its values. So I try:
------------------------------------
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)  FROM testy k where k.e <> 'email' and k.c='1035049' ;
  a   |    b     |    c    |           array_agg          
------+----------+---------+-------------------------------
 1035 | 10410053 | 1035049 | {5951948640868}
 1035 | 10410053 | 1035049 | {5951948640868,9902031328529}
(2 rows)
--------------------------------------

And this is not at all what I've expected - the aggerate function returned different values over the selected partition.

I understand, that this behavior (of changing the aggregate function return values) is there for the purpose of having sum() - and the like - aggregate functions return accumulating/averaged/etc values as of the example in postgres documentation ( http://www.postgresql.org/docs/9.1/static/tutorial-window.html)

But the array_agg() is significantly different from other aggregate functions - it maintains all the trasspassed values within; under such circumstances: is it reasonable to copy that functionality (of PARTITION OVER ... ORDER BY...) in it?
A particular value relevant to a particular row (when SELECT withiout DISTINCT) can be retrieved by RANK() function used as an index into the resulting array.

But, if (unfortunately) this functionality have to stay: Can somebody pls help me cooking an SQL that returns the same value of array_agg() over the entire partition, while letting me control the order of aggregated values, based on the order of column "E"?

My table for the showcase was:
-----------------------------------
SELECT * FROM testy;
  a   |    b     |    c    |          d           |   e  
------+----------+---------+----------------------+-------
 1035 | 10410053 | 1035049 | 9902031328529        | tel
 1035 | 10410053 | 1035049 | 5291286807@gmail.com | email
 1035 | 10410053 | 1035049 | 5951948640868        | tel2
(3 rows)
------------------------------------------

thx

-R

On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:
> W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:
>
> W dniu 03/24/2013 12:06 PM, Misa Simic pisze:
>
> maybe,
>
> SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
> invoice_nr) from invoices;
>
>
> RIGHT. Thenx. (and the first thing I did, I've read the doc on
> array_agg().... what stress makes from people :(
>
>
> Actually, I have a problem with that (which I haven't noticed earlier
> because the data I'm having, don't have to many "duplicates" that cause it).
> The problem is, that:
> --------------------------------------
> SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c )  FROM testy
> k where k.e <> 'email' and k.c='1035049' ;
>   a   |    b     |    c    |           array_agg
> ------+----------+---------+-------------------------------
>  1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
> ---------------------------------------
>
> is _almost_ fine. But I actually need to have control over the order in
> which the array gathered its values. So I try:
> ------------------------------------
> SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
> FROM testy k where k.e <> 'email' and k.c='1035049' ;

you are aware of in-aggregate ordering (not completely sure if it
meets your use case?

select array_agg(v order by v desc) from generate_series(1,3) v;

also, 'distinct'
select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;

merlin

On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:

W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:

W dniu 03/24/2013 12:06 PM, Misa Simic pisze:

maybe,

SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
invoice_nr) from invoices;


RIGHT. Thenx. (and the first thing I did, I've read the doc on
array_agg().... what stress makes from people :(


Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many "duplicates" that cause it).
The problem is, that:
--------------------------------------
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c )  FROM testy
k where k.e <> 'email' and k.c='1035049' ; a   |    b     |    c    |           array_agg
------+----------+---------+-------------------------------1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---------------------------------------

is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
------------------------------------
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
FROM testy k where k.e <> 'email' and k.c='1035049' ;

you are aware of in-aggregate ordering (not completely sure if it
meets your use case?

select array_agg(v order by v desc) from generate_series(1,3) v;

also, 'distinct'
select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;

No, I don't (manual: http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just one word "distinct" on that page, and it's not in the above context). And I cannot duplicate the above:
--------------------------------------------
# select array_agg(distinct v order by v desc) from (select generate_series(1,3) v union all select generate_series(1,3)) q;
ERROR:  syntax error at or near "order"
LINE 1: select array_agg(distinct v order by v desc) from (select ge...
--------------------------------------------

Did I miss something??

In the mean time, I was working towards:
--------------------------------------------
# with ktkt(b,l,s,t) as (SELECT a, b, c, array_agg(k.d)OVER (PARTITION BY k.c ORDER BY k.e)   FROM testy k where k.e <> 'email') select distinct on (b,l,s) b,l,s,t from ktkt k where  k.s='1035049' order by b,l,s,array_length(t,1) desc;
  b   |    l     |    s    |               t              
------+----------+---------+-------------------------------
 1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
(1 row)
-------------------------------------------

Which gives the "expected" result, not exactly, because:
.... my final goal is to select one contact information for an "entity", which is, say: telephone, and which is, say: mobile. Taking into account, that the main contact information table is roughly: CREATE TABLE testy (id_a, id_b, id_c, conact_value, contact_kind, primary key (id_a, id_b,id_c)). ... I cannot collapse the multiple identification columns - they collectively form a unique ID, of an entity. That main contact information table has associated tables like to "fixed/mobile" ("testy" has additional FK columns for that).

And the above "partial result" isn't working towards my final goal.

But, while writing this response, It occured to me, that, may be I shouldn't build the array  so early in the query, but start with a wider join (only reduced by the desired contact attributes) ..... OK. I'll do some testing with that.

Still, I'll be greatfull for some explanations why the "distinct" disdn't work for me. May be that would be a tool for this case.

thnx,

-R

On Thu, Apr 25, 2013 at 1:30 PM, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:
> W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze:
>
> On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak <rafal@zorro.isa-geek.com>
> wrote:
>
> W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:
>
> W dniu 03/24/2013 12:06 PM, Misa Simic pisze:
>
> maybe,
>
> SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
> invoice_nr) from invoices;
>
>
> RIGHT. Thenx. (and the first thing I did, I've read the doc on
> array_agg().... what stress makes from people :(
>
>
> Actually, I have a problem with that (which I haven't noticed earlier
> because the data I'm having, don't have to many "duplicates" that cause it).
> The problem is, that:
> --------------------------------------
> SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c )  FROM testy
> k where k.e <> 'email' and k.c='1035049' ;
>   a   |    b     |    c    |           array_agg
> ------+----------+---------+-------------------------------
>  1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
> ---------------------------------------
>
> is _almost_ fine. But I actually need to have control over the order in
> which the array gathered its values. So I try:
> ------------------------------------
> SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
> FROM testy k where k.e <> 'email' and k.c='1035049' ;
>
> you are aware of in-aggregate ordering (not completely sure if it
> meets your use case?
>
> select array_agg(v order by v desc) from generate_series(1,3) v;
>
> also, 'distinct'
> select array_agg(distinct v order by v desc) from (select
> generate_series(1,3) v union all select generate_series(1,3)) q;
>
>
> No, I don't (manual:
> http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just
> one word "distinct" on that page, and it's not in the above context). And I
> cannot duplicate the above:
> --------------------------------------------
> # select array_agg(distinct v order by v desc) from (select
> generate_series(1,3) v union all select generate_series(1,3)) q;
> ERROR:  syntax error at or near "order"
> LINE 1: select array_agg(distinct v order by v desc) from (select ge...
> --------------------------------------------
>
> Did I miss something??

This feature was added w/9.0.   This means you are on 8.4.  Time to upgrade...

merlin

W dniu 04/26/2013 12:25 AM, Merlin Moncure pisze:
> On Thu, Apr 25, 2013 at 1:30 PM, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:
[----------------------]
>>
>> No, I don't (manual:
>> http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just
>> one word "distinct" on that page, and it's not in the above context). And I
>> cannot duplicate the above:
>> --------------------------------------------
>> # select array_agg(distinct v order by v desc) from (select
>> generate_series(1,3) v union all select generate_series(1,3)) q;
>> ERROR:  syntax error at or near "order"
>> LINE 1: select array_agg(distinct v order by v desc) from (select ge...
>> --------------------------------------------
>>
>> Did I miss something??
> This feature was added w/9.0.   This means you are on 8.4.  Time to upgrade...

Yes, true. I kind of postpone that until debian-wheeze gets released as
stable.

But what about the postgres documentation. I haven't found that feature
in postgres-v9.1 docs (link above). Where can I read about it?

-R

On Fri, Apr 26, 2013 at 3:15 AM, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:
> W dniu 04/26/2013 12:25 AM, Merlin Moncure pisze:
>
>> On Thu, Apr 25, 2013 at 1:30 PM, Rafał Pietrak <rafal@zorro.isa-geek.com>
>> wrote:
>
> [----------------------]
>
>>>
>>> No, I don't (manual:
>>> http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just
>>> one word "distinct" on that page, and it's not in the above context). And
>>> I
>>> cannot duplicate the above:
>>> --------------------------------------------
>>> # select array_agg(distinct v order by v desc) from (select
>>> generate_series(1,3) v union all select generate_series(1,3)) q;
>>> ERROR:  syntax error at or near "order"
>>> LINE 1: select array_agg(distinct v order by v desc) from (select ge...
>>> --------------------------------------------
>>>
>>> Did I miss something??
>>
>> This feature was added w/9.0.   This means you are on 8.4.  Time to
>> upgrade...
>
>
> Yes, true. I kind of postpone that until debian-wheeze gets released as
> stable.
>
> But what about the postgres documentation. I haven't found that feature in
> postgres-v9.1 docs (link above). Where can I read about it?

see here:
http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-AGGREGATES

(you were looking at the window function specific docs, not the
general aggregate documentation).

merlin

W dniu 04/26/2013 12:25 AM, Merlin Moncure pisze:
[--------------------------]
>>
>> select array_agg(v order by v desc) from generate_series(1,3) v;
>>
>> also, 'distinct'
>> select array_agg(distinct v order by v desc) from (select
>> generate_series(1,3) v union all select generate_series(1,3)) q;
>>
[--------------------]
> This feature was added w/9.0.   This means you are on 8.4.  Time to upgrade...
>
>
I tried it, and it looks, like I can have just one column "processed" by
the contruct "distinct ... order..." written under array_agg(). So it
apears, that its "sole purpose" is to yield an array with distinct
values, should there be duplicates in the source (table or query).

Having distinct values is fine, but I actually need to have the
resulting array, contain values in sort-order, which is defined by
values in another column. like:

array_agg(distinct v order by v) -- works in postgres, but actually I need:
array_agg(distinct v order by v,x) -- which doesn't. (ERROR:
....expressions must appear in argument list), but that suggestion is
not possible here, e.g.:
array_agg(distinct v,x order by v,x) -- is actually a two argument
array_agg() function call.

Elaborating on the second example above: what I need is: "feed the
array_agg() only with distinct (by sorting for uniqness) v-values, but
feed them sorted according to x-values". This is what I need.

I'd apreciate other ideas (my yesterday plan to avoid array untill late
in the query failed). Any sugestions welcome. (Or may be I'm just plain
wrong about the above "distinct" construct - then will apreciate a
pointer to some documentation).

thnx,

-R

=?ISO-8859-2?Q?Rafa=B3_Pietrak?= <rafal@ztk-rp.eu> writes:
> array_agg(distinct v order by v) -- works in postgres, but actually I need:
> array_agg(distinct v order by v,x) -- which doesn't. (ERROR:
> ....expressions must appear in argument list),

Why do you think you need that?  AFAICS, the extra order-by column could
not in any way affect the result of the operation.

            regards, tom lane

W dniu 04/26/2013 05:25 PM, Tom Lane pisze:
> =?ISO-8859-2?Q?Rafa=B3_Pietrak?= <rafal@ztk-rp.eu> writes:
>> array_agg(distinct v order by v) -- works in postgres, but actually I need:
>> array_agg(distinct v order by v,x) -- which doesn't. (ERROR:
>> ....expressions must appear in argument list),
> Why do you think you need that?  AFAICS, the extra order-by column could
> not in any way affect the result of the operation.

In my particular case (e.g. not in general, since I assume, we all
agree, that people do sort things comming out of the query for one
purpose or another), is that:
1. the information i retrieve (the V), is a telephone number.
2. my database does keep numerous contact information (e.g. telephone
numbers, email, etc) for "entities" registered here - e.g
people/companies leave contact information of various relevance:
my-private, my-office, my-lawyer, etc.
3. when I need to get in touch with somebody, I need to choose the
number that is "most relevant" - one person leaves "my-private" phone,
and "my-lawyer"  phone; the other leaves "my-office", and "my-lawyer".
4. in the above example I'd like to peek: "my-private" for the first
person, and "my-office" for the other. I wouldn't like to relay on
randomness provided by the database query plan.
5. so I have "the other" column (the X, e.g "my-something"), that I'd
like to sort the array elements by. And peek just the first element of
the array.

BTW: I've just rid off the array, and cooked a plain table join with
"distinct on ()", which gives just what I needed. My initial plan of
using array was to reduce the intermediate row-sets as much as possible
as early as possible. Yet, in this case, plain old RDB joins proved to
be better (may be not faster - a big multitable join is formed along the
query, but conceptually cleaner, which works for me, the database isn't
terribly big).

So I have my problem solved, although I haven't figured out a way to
have controll over the sort order of array_agg() result - which might be
otherwise usefull.

thnx,

-R

 SELECT DISTINCT a, b, c, array_agg(d)  OVER (PARTITION BY c )  FROM
On Friday, April 26, 2013, Rafał Pietrak wrote:

W dniu 04/26/2013 05:25 PM, Tom Lane pisze:

Rafał Pietrak <rafal@ztk-rp.eu> writes:

array_agg(distinct v order by v) -- works in postgres, but actually I need:
array_agg(distinct v order by v,x) -- which doesn't. (ERROR:
....expressions must appear in argument list),

Why do you think you need that?  AFAICS, the extra order-by column could
not in any way affect the result of the operation.

In my particular case (e.g. not in general, since I assume, we all agree, that people do sort things comming out of the query for one purpose or another), is that:
1. the information i retrieve (the V), is a telephone number.
2. my database does keep numerous contact information (e.g. telephone numbers, email, etc) for "entities" registered here - e.g people/companies leave contact information of various relevance: my-private, my-office, my-lawyer, etc.
3. when I need to get in touch with somebody, I need to choose the number that is "most relevant" - one person leaves "my-private" phone, and "my-lawyer"  phone; the other leaves "my-office", and "my-lawyer".
4. in the above example I'd like to peek: "my-private" for the first person, and "my-office" for the other. I wouldn't like to relay on randomness provided by the database query plan.
5. so I have "the other" column (the X, e.g "my-something"), that I'd like to sort the array elements by. And peek just the first element of the array.

BTW: I've just rid off the array, and cooked a plain table join with "distinct on ()", which gives just what I needed. My initial plan of using array was to reduce the intermediate row-sets as much as possible as early as possible. Yet, in this case, plain old RDB joins proved to be better (may be not faster - a big multitable join is formed along the query, but conceptually cleaner, which works for me, the database isn't terribly big).

So I have my problem solved, although I haven't figured out a way to have controll over the sort order of array_agg() result - which might be otherwise usefull.

thnx,

-R



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

 SELECT DISTINCT a, b, c, array_agg(d)  OVER (PARTITION BY c )  FROM

(

SELECT a, b, c, d FROM  testy where e <> 'email' and c='1035049'  ORDER BY  a, b, c, e

) t

Doesnt give u desired result?

Hmm... actualy, it looks like it does. I wouldn't thought, that the sort order is maintaned from subquery, but if it does, this is just it.

It looks like I've just overdone the solution.

-R

On Friday, April 26, 2013, Rafał Pietrak wrote:

W dniu 04/26/2013 05:25 PM, Tom Lane pisze:

Rafał Pietrak <rafal@ztk-rp.eu> writes:

array_agg(distinct v order by v) -- works in postgres, but actually I need:
array_agg(distinct v order by v,x) -- which doesn't. (ERROR:
....expressions must appear in argument list),

Why do you think you need that?  AFAICS, the extra order-by column could
not in any way affect the result of the operation.

In my particular case (e.g. not in general, since I assume, we all agree, that people do sort things comming out of the query for one purpose or another), is that:
1. the information i retrieve (the V), is a telephone number.
2. my database does keep numerous contact information (e.g. telephone numbers, email, etc) for "entities" registered here - e.g people/companies leave contact information of various relevance: my-private, my-office, my-lawyer, etc.
3. when I need to get in touch with somebody, I need to choose the number that is "most relevant" - one person leaves "my-private" phone, and "my-lawyer"  phone; the other leaves "my-office", and "my-lawyer".
4. in the above example I'd like to peek: "my-private" for the first person, and "my-office" for the other. I wouldn't like to relay on randomness provided by the database query plan.
5. so I have "the other" column (the X, e.g "my-something"), that I'd like to sort the array elements by. And peek just the first element of the array.

BTW: I've just rid off the array, and cooked a plain table join with "distinct on ()", which gives just what I needed. My initial plan of using array was to reduce the intermediate row-sets as much as possible as early as possible. Yet, in this case, plain old RDB joins proved to be better (may be not faster - a big multitable join is formed along the query, but conceptually cleaner, which works for me, the database isn't terribly big).

So I have my problem solved, although I haven't figured out a way to have controll over the sort order of array_agg() result - which might be otherwise usefull.

thnx,

-R



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general