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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2003-03-06 19:08:34 | Re: I cant find it or I'm just lazy ? |
Previous Message | Bruce Momjian | 2003-03-06 18:40:01 | Re: TODO: DROP COLUMN .. CASCADE |