Thread: Sorting items in aggregate function

Sorting items in aggregate function

From
Steven Murdoch
Date:
I would like to concatenate sorted strings in an aggregate function. I
found a way to do it without sorting[1], but not with.

Here is an example of a setup and what I could like to achieve. Does
anyone have suggestions on what is the best way to get the desired
result?

Thanks,
Steven.

CREATE TABLE a ( -- Names id INT PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE b ( -- Codes id INT PRIMARY KEY, code CHAR(2) NOT NULL);

CREATE TABLE ab ( -- m:n relationship between a and b id SERIAL PRIMARY KEY, a_id INT NOT NULL, b_id INT NOT NULL);

COPY a(id,name) FROM STDIN DELIMITER '|';
1|Alice
2|Bob
3|Charlie
\.

COPY b(id, code) FROM STDIN DELIMITER '|';
1|a
2|b
3|c
4|d
\.

COPY ab(a_id, b_id) FROM STDIN DELIMITER '|';
2|4
2|1
3|2
3|3
\.

-- Custom aggregate function which concatenates strings
CREATE AGGREGATE concat (   BASETYPE = text,   SFUNC = textcat,   STYPE = text,   INITCOND = '',
);

-- Current query
SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes
FROMa LEFT JOIN ab ON (a.id=ab.a_id)  LEFT JOIN  b ON (ab.b_id=b.id)
GROUP BY a.name
ORDER BY codes;

-- Actual output:
--
--   name   | codes
-- ---------+-------
--  Alice   |
--  Charlie | b c
--  Bob     | d a


-- Desired output:
--
--   name   | codes
-- ---------+--------
--  Alice   |
--  Bob     | a d
--  Charlie | b c

[1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html

-- 
w: http://www.cl.cam.ac.uk/users/sjm217/


Re: Sorting items in aggregate function

From
Michael Fuhr
Date:
On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote:
> Here is an example of a setup and what I could like to achieve. Does
> anyone have suggestions on what is the best way to get the desired
> result?

Use the aggregate over an ordered subquery:

SELECT name, trim(concat(code || ' ')) AS codes
FROM ( SELECT a.name, b.code FROM a LEFT JOIN ab ON a.id = ab.a_id LEFT JOIN b ON ab.b_id = b.id ORDER BY b.code
) AS s
GROUP BY name
ORDER BY name;

Here's a comment from Tom Lane, one of the core developers, on
feeding the aggregate based on the subquery's order:

http://archives.postgresql.org/pgsql-general/2005-09/msg00047.php

-- 
Michael Fuhr


Re: Sorting items in aggregate function

From
Volkan YAZICI
Date:
On Sep 12 04:46, Steven Murdoch wrote:
> I would like to concatenate sorted strings in an aggregate function. I
> found a way to do it without sorting[1], but not with.

If the array elements will be made of integers, then you can use sort()
procedure comes with intarray contrib module. For instance,
 SELECT concat(T.sorted_arr)   FROM (SELECT sort(arr) FROM tbl) AS T (sorted_arr);

If related column will also include text values, you can create a
suitable sort() procedure for text[] type and use it instead.


Regards.


Re: Sorting items in aggregate function

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote:
>> Here is an example of a setup and what I could like to achieve. Does
>> anyone have suggestions on what is the best way to get the desired
>> result?

> Use the aggregate over an ordered subquery:

> SELECT name, trim(concat(code || ' ')) AS codes
> FROM (
>   SELECT a.name, b.code
>   FROM a
>   LEFT JOIN ab ON a.id = ab.a_id
>   LEFT JOIN b ON ab.b_id = b.id
>   ORDER BY b.code
> ) AS s
> GROUP BY name
> ORDER BY name;

Note that if you need to GROUP in the outer query, it's best to sort the
inner query's output first by the outer query's grouping:

SELECT name, trim(concat(code || ' ')) AS codes
FROM ( SELECT a.name, b.code FROM a LEFT JOIN ab ON a.id = ab.a_id LEFT JOIN b ON ab.b_id = b.id ORDER BY a.name,
b.code         ^^^^^^^^^^^^^^
 
) AS s
GROUP BY name
ORDER BY name;

This way will still work if the planner decides to use a GroupAggregate
(which in fact it probably will, if it sees it can avoid another sort
step).  The way Michael showed will only work if the plan uses
HashAggregate --- if the planner decides it needs Sort+GroupAggregate
in the outer query, the re-sort will probably destroy the ordering
by b.code.
        regards, tom lane


Re: Sorting items in aggregate function

From
Osvaldo Rosario Kussama
Date:
Steven Murdoch escreveu:
> I would like to concatenate sorted strings in an aggregate function. I
> found a way to do it without sorting[1], but not with.
> 
> Here is an example of a setup and what I could like to achieve. Does
> anyone have suggestions on what is the best way to get the desired
> result?
> 
> Thanks,
> Steven.
> 
> CREATE TABLE a ( -- Names
>   id INT PRIMARY KEY,
>   name TEXT NOT NULL);
> 
> CREATE TABLE b ( -- Codes
>   id INT PRIMARY KEY,
>   code CHAR(2) NOT NULL);
> 
> CREATE TABLE ab ( -- m:n relationship between a and b
>   id SERIAL PRIMARY KEY,
>   a_id INT NOT NULL,
>   b_id INT NOT NULL);
> 
> COPY a(id,name) FROM STDIN DELIMITER '|';
> 1|Alice
> 2|Bob
> 3|Charlie
> \.
> 
> COPY b(id, code) FROM STDIN DELIMITER '|';
> 1|a
> 2|b
> 3|c
> 4|d
> \.
> 
> COPY ab(a_id, b_id) FROM STDIN DELIMITER '|';
> 2|4
> 2|1
> 3|2
> 3|3
> \.
> 
> -- Custom aggregate function which concatenates strings
> CREATE AGGREGATE concat (
>     BASETYPE = text,
>     SFUNC = textcat,
>     STYPE = text,
>     INITCOND = '',
> );
> 
> -- Current query
> SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes
> FROM
>  a LEFT JOIN ab ON (a.id=ab.a_id)
>    LEFT JOIN  b ON (ab.b_id=b.id)
> GROUP BY a.name
> ORDER BY codes;
> 
> -- Actual output:
> --
> --   name   | codes
> -- ---------+-------
> --  Alice   |
> --  Charlie | b c
> --  Bob     | d a
> 
> 
> -- Desired output:
> --
> --   name   | codes
> -- ---------+--------
> --  Alice   |
> --  Bob     | a d
> --  Charlie | b c
> 
> [1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html
> 

Look this message:
http://archives.postgresql.org/pgsql-sql/2006-05/msg00044.php


[]s
Osvaldo

    
_______________________________________________________ 
Você quer respostas para suas perguntas? Ou você sabe muito e quer compartilhar seu conhecimento? Experimente o Yahoo!
Respostas!
 
http://br.answers.yahoo.com/


Re: Sorting items in aggregate function (thanks)

From
Steven Murdoch
Date:
On Tue, Sep 12, 2006 at 04:37:55PM -0400, Tom Lane wrote:
> Note that if you need to GROUP in the outer query, it's best to sort the
> inner query's output first by the outer query's grouping:
...

Great - this works fine. Thanks also to the other people who replied.

Steven.

--
w: http://www.cl.cam.ac.uk/users/sjm217/