Re: Aggregation question

On Sat, 7 Feb 2004, [ISO-8859-1] Hans-J?rgen Sch?nig wrote:

> if you want to put 1000 columns into one table, your data structure
> needs some further investigation. you are trying to solve the wrong problem.

Dilip,

you may try our contrib/hstore from http://www.sai.msu.su/~megera/postgres/gist/
which is sort of perl hash and could provide you some flexibility.
But you may indeed need to think if you really needed such structure :)

Oleg

>
> Regards,
>
> Hans
>
>
> Dilip Angal wrote:
> > Hi
> >
> > I have a situation that I need flexible number columns to model the
> > business requirements. It could go up to 1000 columns and will be a
> > sparse matrix. One option I was considering was to have a table with
> > col1......col1000
> > Other option I can consider is store all of them as name values in
> > single column as a string
> >
> > option 1
> > col1 | col2 |col 3 |........|col56|.....|col77|
> > 10 | 2 | | | 4 | | |
> > 2 | | 4 | | |.... |6 |
> >
> > option 2
> >
> > "col1=10,col2=2,col56=4"
> > "col1=2,col3=4,col77=6"
> >
> > I will have about 50Million such records in a table.
> >
> > I need to aggregate the column values for a given day by taking sum of
> > all the rows
> >
> > "col1=12,col2=2,col3=4,col56=4,col77=6"
> >
> > Second option looks very elegant but aggregation is hard
> > Besides, second option may have performance penalty (Especially when you
> > have 50 Million records)
> >
> > Any one can give any pointers or comments on how to model this an how to
> > aggregate it?
> >
> > Thanks
> > Dilip
> >
> >
>
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83