Re: SQL question on chunking aggregates

Merlin Moncure-2 wrote
> On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett <

> owen@

> > wrote:
>
>> It looks like I should be able to use the window function to do this,
>> but
>> I've been unsuccessful. The following runs, but doesn't seem to have any
>> effect:
>>
>> select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
>> revalbuildingvalues) from parcel group by owner_id
>> window mywindow as (rows between current row and 5 following);
>>
>> Does anyone have any suggestions on what I should try?
>>
>> -Owen
>
> I didn't test it, but something along the lines of:
>
> select
> owner_id,
> array_agg(maplot),
> array_agg(totalvalues)
> from
> (
> select
> owner_id,
> trim(maplot) as maplot,
> revallandvalue + revalbuildingvalues as totalvalues,
> row_number() over (partition by owner_id) as n
> from parcel
> ) q
> group by owner_id, (n - 1)/5;

Yeah, a window cannot work because it cannot be defined to provide disjoint
subsets.

In most cases multiple invocations of array_agg(...) - at the same level in
a query - will see the same row order but that is not something that it is
wise to rely upon. Any time you want to have synchronized array_agg(...)
calls you should add identical explicit ORDER BY clauses to them; or better
yet combine that data into a custom datatype and then store that in the
array.

The solution is as Merlin presents; you need to use integer division to
bucket the rows and then call the array_agg(...) using those groups. I like
to keep the bucket ID around in order to capture the original order but as
shown it is not a requirement.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-question-on-chunking-aggregates-tp5794680p5794694.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.