Re: Find min year and min value - Mailing list pgsql-general

>> Having 500 statistical global national variables for about 240
>> countries/territories. Need to do regional aggregations, per
>> Capita calculations and some completeness computations on-the-fly.
>
>
>> id_variable   |    year    |    value     |    id_country
>
> Both Steve and I have given you alternatives and reasons for
> choosing alternative schema. You haven't provided any additional
> information to really help us guide you in any particular direction
> from what we already have. For example, in the section from me
> which you quoted above, I wrote that this schema may be appropriate
> if the "value" column values are actually all of the same type
> (e..g, all currency amounts, all masses, all counts). You haven't
> said whether or not this is the case. We can't read your mind :)
>
> Again, one thing that would help is if you use a more descriptive
> column name than "value" that gives an indication of what *kind* of
> values are in the column.
>
>> I thought (and did ask) about the possibility to put nevertheless
>> - with the new table design - the variables into different tables,
>> but nobody really got my on a track for that.
>
> Steve first suggested it and I provided an example of what that
> would look like (using "gdp" and "fish_catches" tables) in the same
> post you quoted from above.
>
> http://archives.postgresql.org/pgsql-general/2007-10/msg00108.php
>
> Is this not what you mean?

Hmm... sorry that I didn't give you more info. I will see it I can do
better now.

So, as mentioned above, we have 500 different variables (such as GDP,
Fish Catch, Population, Forest Cover, Fertility Rate etc.). Many of
them have indeed different units (Metric Tons, Thousand People, Sqkm,
Persons per Year etc.). And many do cover different time periods
(1970-2005; 2000-2002; 1970-75, 1975-80,..., 2000-05; ...).

So, "value" is something general - it can be (measured in) (thousand)
Dollars, (Million) People, (Hundred) Hectares etc...

We update the variables something like once a year or every two
years, in an ongoing manner. Updating means especially adding new
years (as they are being published by World Bank, UNSD, etc.). So, no
real "hard core" editing/adding, I'd say. A slow, but steady progress.

Queries, as mentioned too, are mainly simple "SELECt * FROM
specific_variable_like_gdp". But as well on-the-fly aggregations to
subregions and regions (such as West Africa, and Africa). And some
calculations which enables to indicate the "completeness" of the
aggregations (parameters being used are "Number of countries included
in aggregation in respect to Number of Countries, Total Population,
Land Area, GDP).

Does this help?

Thanks anyway already for all your feedback. Your really great people
out there!!

Best wishes,

Stef