From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Stefan Schwarzer" <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Find "smallest common year" |
Date: | 2007-09-27 15:13:58 |
Message-ID: | dcc563d10709270813y442888c1g2d30d93692c699db@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/27/07, Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> wrote:
>
> I really tried it out. I changed my whole database to the "id-country | year
> | value" - format. And then tried to build my SQL queries. But it was
> really, really difficult, and sometimes impossible (for me) to get what I
> wanted.
>
> Actually, it remains quite difficult for me to remember the actual problems
> I had with it.
>
> But, for example, I need to aggregate the national data on-the-fly to their
> regions. I need to calculate per Capita data on-the-fly for each variable.
> Although, one would say this should be simple to accomplish, me and a
> semi-professional database expert could hardly solve these things.
You should have brought your problem here. You'd be surprised what a
fresh set of eyes can see.
> In one case we came up with as many sub-selects as years were available (or
> selected by the user) (that can be up to 60 years). Is this "efficient" SQL
> programming?
Probably not. But that doesn't mean it was the right approach either.
There well might have been a more efficient approach you didn't think
of.
> What would you recommend for say, 500 global national statistical variables,
> 500 regional and 500 subregional and 500 global aggregations? Years being
> covered having something between 10 and 60 years for each of these
> variables. All available for 240 countries/territories.
I generally approach such problems by putting the data right
(normalized) at the start, then munging the data into summary tables
to handle the problems you're seeing now.
I find it far easier to maintain normalized tables that produced
non-normalized ones (for things like data warehousing) than it is to
maintain non-normalized tables and trying to produce normalized data
from that.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2007-09-27 18:31:17 | Re: DAGs and recursive queries |
Previous Message | Stefan Schwarzer | 2007-09-27 14:54:52 | Re: Find "smallest common year" |