Thread: Putting many related fields as an array

Hi,

Currently doing some level of aggregrate tables for some data. These
data will be used for slice/dice activity and we want to be able to
play/manipulate the data such that I can get means and stddev data.

Eg: For each Original Column eg:

population_in_town : (I get derivatives)
- mean # of ppl in each town
- stddev # of ppl in each town (stdev calc already uses 2 extra columns
for # of ppl squared and qty of ppl)
- count of ppl
- count of # of ppl is < 100 (to get a percentage of population)
- count of # of ppl is < 500

Hence, I'm seeing a 1:5 column growth here if I put them as column
based.

eg:
| sum of count | sum_of_count_squared | qty | qty < 100 | qty < 500 |


I'm thinking of lumping them into 1 column via an array instead of into
5 different columns. Not sure how to go about this, hence the email to
the list.

something like {244,455,1234,43,23}

query can be done like

sum_of_count / qty = Ave
(sum_of_count_squared * sum_qty ) / (qty * (qty-1)) = STDEV
(sum_qty<100 / sum_qty) = % < 100
(sum_qty<500 / sum_qty) = % < 500


Then there's the issue of speed/responsiveness on doing it.


Help would be appreciated in this.

On Tue, May 12, 2009 at 01:23:14PM +0800, Ow Mun Heng wrote:
> | sum of count | sum_of_count_squared | qty | qty < 100 | qty < 500 |
>
>
> I'm thinking of lumping them into 1 column via an array instead of into
> 5 different columns. Not sure how to go about this, hence the email to
> the list.

The normal array constructor should work:

  SELECT ARRAY[MIN(v),MAX(v),AVG(v),STDEV(v)]
  FROM (VALUES (1),(3),(4)) x(v);

Not sure why this is better than using separate columns though.  Maybe a
new datatype and a custom aggregate would be easier to work with?

--
  Sam  http://samason.me.uk/

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
On Tue, May 12, 2009 at 01:23:14PM +0800, Ow Mun Heng wrote:
>> | sum of count | sum_of_count_squared | qty | qty < 100 | qty < 500 |
>>
>>
>> I'm thinking of lumping them into 1 column via an array instead of into
>> 5 different columns. Not sure how to go about this, hence the email to
>> the list.

>The normal array constructor should work:
>
>  SELECT ARRAY[MIN(v),MAX(v),AVG(v),STDEV(v)]
>  FROM (VALUES (1),(3),(4)) x(v);
>
>Not sure why this is better than using separate columns though.  Maybe a
>new datatype and a custom aggregate would be easier to work with?

The issue here is the # of columns needed to populate the table.

The table I'm summarizing has close to between 50 to 100+ columns, if the
1:5x is used as a yardstick, then the table will get awfully wide quickly.

I need to know how to do it first, then test accordingly for performance and
corner cases.

On Tue, May 12, 2009 at 08:06:25PM +0800, Ow Mun Heng wrote:
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> On Tue, May 12, 2009 at 01:23:14PM +0800, Ow Mun Heng wrote:
> >Not sure why this is better than using separate columns though.  Maybe a
> >new datatype and a custom aggregate would be easier to work with?
>
> The issue here is the # of columns needed to populate the table.
>
> The table I'm summarizing has close to between 50 to 100+ columns, if the
> 1:5x is used as a yardstick, then the table will get awfully wide quickly.
>
> I need to know how to do it first, then test accordingly for performance and
> corner cases.

Yes, those are going to be pretty wide tables!  Maybe if you can make
the source tables a bit "narrower" it will help things; PG has to read
entire rows from the table, so if your queries are only touching a few
columns then it's going to need a lot more disk bandwidth to get a
specific number of rows back from the table.

--
  Sam  http://samason.me.uk/