Re: Find min year and min value
От | Stefan Schwarzer |
---|---|
Тема | Re: Find min year and min value |
Дата | |
Msg-id | 22CCDB5A-D156-4EFE-AD39-A8042A8C59EB@grid.unep.ch обсуждение исходный текст |
Ответ на | Re: Find min year and min value (Michael Glaesemann <grzm@seespotcode.net>) |
Ответы |
Re: Find min year and min value
|
Список | pgsql-general |
>> As others have noted, the query *can* be written. But it appears >> to me >> that you are struggling against your table layout. > > The current schema he has is commonly called EAV (entity-attribute- > value) and is generally frowned upon. Now, in his particular case > it may be justified if the "value" column values are actually all > of the same type, such as currency amounts for each category. If > this is the case, I suggest renaming the column to be more > descriptive of what is actually stored: likewise the id_variable > column. Hmmm.... I am somewhat surprised to here so. After being told in this forum how "bad" my old table design was, I changed it to the current (which is less than alpha). Perhaps to summarize: 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. The design was a table like this for each variable: id_country | 1970 | 1971 | ... | 2004 | 2005 ------------------------------------------------------------------- 1 | NULL | 36 | ... | 42 | 45 2 ...... The new like this: id_variable | year | value | id_country --------------------------------------- 1 | 2001 | 123 | 1 1 | 2002 | 125 | 1 1 | 2003 | 128 | 1 1 | 2004 | 132 | 1 1 | 2005 | 135 | 1 1 | 2001 | 412 | 2 1 | 2002 | 429 | 2 1 | 2003 | 456 | 2 1 | 2004 | 465 | 2 1 | 2005 | 477 | 2 .... 2 | 1980 | 83 | 1 2 | 1981 | 89 | 1 .... 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. So I thought the most "common" way would be to have this central table. But I am at a stage where I still can change - and would very much like to get your advice. Thanks a lot! Stef ____________________________________________________________________ Stefan Schwarzer Lean Back and Relax - Enjoy some Nature Photography: http://photoblog.la-famille-schwarzer.de Appetite for Global Data? UNEP GEO Data Portal: http://geodata.grid.unep.ch ____________________________________________________________________
В списке pgsql-general по дате отправления: