Обсуждение: Sorting items in aggregate function
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/
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
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.
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
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/
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/