Search Postgresql Archives

Re: Find "smallest common year"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

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?

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.

Thanks for any recommendations!

Stef




Stefan Schwarzer wrote:
Sorry,

I forgot to mention my table design, which is like this:

     name             2001    2002   2003   2004   2005
-----------------------------------------------------------------
Afghanistan    ....
Albania            ....

(Yep, I know, bad table design.... :-)). I tried to change it to the more common "id | year | value" format, but there were too many SQL problems afterwards for the different queries/calculations we need to have....)


May I suggest that you concentrate on solving *those* problems instead of
the programmatically trivial computation of lowest common value?  Notice
that a *really trivial* programming exercise becomes highly involved in
your case --- if I'm understanding correctly what you have, I assume you'd
have to check one by one the fields for NULL or non-NULL values --- that's
intolerably ugly, IMHO, and it is a very simple and direct consequence of
an as-unsound-as-it-gets db/table design.




 ____________________________________________________________________

  

  Stefan Schwarzer
  
  Lean Back and Relax - Enjoy some Nature Photography: 
  
  Appetite for Global Data? UNEP GEP Data Portal:  
  ____________________________________________________________________






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux