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

Aggregate "rollup"

From: mlw <pgsql(at)mohawksoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Aggregate "rollup"
Date: 2003-03-05 20:47:18
Message-ID: 3E666256.9050001@mohawksoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

While it seems pointless to create an array on a select, it has a 
purpose in OLAP. For instance, suppose you do this:

create table fast_lookup as select reference, 
int_array_aggregate(result) from table group by result

The "fast_lookup" table now has all the result entries as an array in a 
single row. In the systems that I have used this, it has provided a 
dramatic improvement, especially when you have a high number of 
identical "reference" entries in a classic "one to many" table.

The question is, would a more comprehensive solution be wanted? 
Possible? Something like:

create table fast_lookup as select reference, aggregate_array( field ) 
from table group by field

Where the function aggregate_array takes any number of data types.

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?


Responses

pgsql-hackers by date

Next:From: Joe ConwayDate: 2003-03-05 21:10:46
Subject: Re: Aggregate "rollup"
Previous:From: mlwDate: 2003-03-05 20:05:03
Subject: Re: Best setup for RAM drive

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