Thread: array_agg and partition sorts

I'm on Postgres 9.1 and I've come across an issue which shows I don't
understand partition sorting:

Given a table like this:

    select * from test;
     n_group |   t_name    | t_additional
    ---------+-------------+--------------
           1 | Canberra    | Australia
           1 | Vienna      | Austria
           1 | Baku        | Azerbaijan
           1 | Nassau      | The Bahamas
           1 | Dhaka       | Bangladesh
           1 | Porto-Novo  | Benin
           1 | Thimphu     | Bhutan
           1 | Brasilia    | Brazil
           1 | Sofia       | Bulgaria
           1 | Ouagadougou | Burkina Faso
           2 | Bridgetown  | Barbados
           2 | Minsk       | Belarus
           2 | Brussels    | Belgium
           2 | Belmopan    | Belize
           2 | Gaborone    | Botswana

Running this query:

    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) as agg2
            , array_agg(t_name) over (partition by n_group order by t_additional asc) as agg3
            , row_number() over (partition by n_group)
        from test
    ) x
    where
        x.row_number = 1;

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 don't understand why agg3 is missing values.

--
Rory Campbell-Lange
rory@campbell-lange.net

On 26/06/13, Rory Campbell-Lange (rory@campbell-lange.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@campbell-lange.net