Search Postgresql Archives

Calculate Weighted Aggregation between Two Tables

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

 



Hi,

I have global national statistics (GDP, Fish Catch etc.), which I would like to aggregate to and calculate per Capita data for given regions (Africa, Europe...) and subregions (Western Africa, ....) on-the-fly.

From a statistical point of view it seems best to use something like this:

    given that the variable is "v" and the Total Population table is "tp":

   (country_1_of_region_1_for_v / country_1_of_region_1_for_tp) + (country_2_of_region_1_for_v / country_2_of_region_1_for_tp) + ....
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                            (sum_countries_all_of_region_1_for_v / sum_countries_all_of_region_1_for_tp)


    and then same thing for the other regions (in my case a total of 6 regions).


I have a table of countries with a field country_id and region_id (which specifies to which region this country belongs).
I have the table for the variable with a field country_id, which has kind of a "Excel design", i.e. columns for each year.
And I have a table of regions with a field region_id.

It seems like a rather complicated thing to do, as one needs first to get all the countries of a specific region for both tables (variable & pop_total), have the SUM(of all countries) available and run the mathematical procedure... 

Could someone give me a hint how this could be achieved?

Thanks for any help!

Stef


  _______________________________________

  

  Stefan Schwarzer

  

  Nature Photography: http://photoblog.la-famille-schwarzer.de

  UNEP GEO Data Portal: http://geodata.grid.unep.ch

  _______________________________________





[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