Re: array_agg and partition sorts

On 26/06/13, Rory Campbell-Lange (rory(at)campbell-lange(dot)net) wrote:
> I'm on Postgres 9.1 and I've come across an issue which shows I don't
> understand partition sorting:
> Returns:
>
> -[ RECORD 1 ]---------------------------------------------------------------------------------
> agg1 | {Canberra,Vienna,Baku,Nassau,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou}
> agg2 | {Nassau,Ouagadougou,Sofia,Brasilia,Thimphu,Porto-Novo,Dhaka,Baku,Vienna,Canberra}
> agg3 | {Canberra}
> row_number | 1
> -[ RECORD 2 ]---------------------------------------------------------------------------------
> agg1 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
> agg2 | {Gaborone,Belmopan,Brussels,Minsk,Bridgetown}
> agg3 | {Bridgetown}
> row_number | 1

I've solved this by reading the docs at
http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
more carefully, particulary about how sorts affect the frame.

select * from (
select
array_agg(t_name) over (partition by n_group) as agg1
, array_agg(t_name) over (partition by n_group order by t_additional desc
range between unbounded preceding and unbounded following) as agg2
, array_agg(t_name) over (partition by n_group order by t_additional asc
range between unbounded preceding and unbounded following) as agg3
, row_number() over (partition by n_group)
from test
) x;

produces the desired result:

...
-[ RECORD 10 ]--------------------------------------------------------------------------------
agg1 | {Canberra,Vienna,Baku,Nassau,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou}
agg2 | {Nassau,Ouagadougou,Sofia,Brasilia,Thimphu,Porto-Novo,Dhaka,Baku,Vienna,Canberra}
agg3 | {Canberra,Vienna,Baku,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou,Nassau}
row_number | 4
-[ RECORD 11 ]--------------------------------------------------------------------------------
agg1 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
agg2 | {Gaborone,Belmopan,Brussels,Minsk,Bridgetown}
agg3 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
row_number | 1

--
Rory Campbell-Lange
rory(at)campbell-lange(dot)net