Skip site navigation (1) Skip section navigation (2)

Re: Aggregate "rollup"

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "mlw" <pgsql(at)mohawksoft(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Aggregate "rollup"
Date: 2003-03-06 19:02:21
Message-ID: 303E00EBDD07B943924382E153890E5434A926@cuthbert.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-hackers
> -----Original Message-----
> From: mlw [mailto:pgsql(at)mohawksoft(dot)com]
> Sent: Wednesday, March 05, 2003 3:47 PM
> To: pgsql-hackers(at)postgresql(dot)org
> Subject: [HACKERS] Aggregate "rollup"
> 
> I had written a piece of code about two years ago that used the
> aggregate feature of PostgreSQL to create an array of integers from an
> aggregate, as:
> 
> select int_array_aggregate( column ) from table group by column
> 

Do I understand correctly that this still follows the normal rules for
grouping, so that only like values are put in the array?

Example: column has values 1,1,1,2,2 spread over 5 rows.
Your query returns two rows with row1={1,1,1} and row2 = {2,2}...is this
correct?

Also, what if your aggregate column is different from the group column: 
Table t with columns c1, c2 with 5 rows:
C1 C2
1, 1
1, 2
1, 3
2, 1
2, 2

Does select C1, int_array_aggregate( C2 ) from table group by C1 return

1, {1, 2, 3}
2, {1, 2}
??

FWIW, I think that's a pretty cool function.  This allows the backend to
telescope 1 dimension (only) out of a dataset, the most detailed one.
In certain situations with large datasets over slow connections, this
could be a big payoff.

Also, all this talk about XML has got me thinking about how to allow
basic query features to provide simple nesting services.  consider:

select C1, C2 from t for xml;  returns:
<t> 
<C1>1</C1><C2>1</C2>
<C1>1</C1><C2>2</C2>
<C1>1</C1><C2>3</C2>
<C1>2</C1><C2>1</C2>
<C1>2</C1><C2>2</C2>
</t>
select C1, xml_aggregate(C2) from t for xml; returns:
<t>
<C1 value="1"><C2>1</C2><C2>2</C2><C2>3</C2></C1>
<C1 value="2"><C2>1</C2><C2>2</C2><C2>3</C2></C1>
</t> 

> create table fast_lookup as select reference,
> int_array_aggregate(result) from table group by result
> 
> The question is, would a more comprehensive solution be wanted?
> Possible? Something like:
> 
> 
> Any thoughts? I think I need to fix the code in the current
> /contrib/intagg anyway, so is it worth doing the extra work to
included
> multiple data types?

Yes.

Just a thought.
Merlin


Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2003-03-06 19:08:34
Subject: Re: I cant find it or I'm just lazy ?
Previous:From: Bruce MomjianDate: 2003-03-06 18:40:01
Subject: Re: TODO: DROP COLUMN .. CASCADE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group