From: | Hans-Jürgen Schönig <postgres(at)cybertec(dot)at> |
---|---|
To: | Dilip Angal <dilip_angal(at)yahoo(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Aggregation question |
Date: | 2004-02-07 17:27:41 |
Message-ID: | 4025200D.4000901@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
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
>
>
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2004-02-07 18:09:49 | Re: [HACKERS] Aggregation question |
Previous Message | Anton Nikiforov | 2004-02-07 16:13:03 | Re: PL/Ruby |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2004-02-07 18:09:49 | Re: [HACKERS] Aggregation question |
Previous Message | ziga | 2004-02-07 15:04:22 | Renaming tables to other schemas |