Calculate Weighted Aggregation between Two Tables

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/19/07 04:13, Stefan Schwarzer wrote:

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.

Well that's a Very Bad Thing. (Wasn't there a thread about this
last week?)

Since the "years" are columns, are the "variables" in rows?

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?

This would be bog-simple if your tables were structured like this:

CREATE TABLE T_REGION (
REGION_ID CHAR(8) PRIMARY KEY,
DESCRIP TEXT);

CREATE TABLE T_COUNTRY (
COUNTRY_ID CHAR(8) PRIMARY KEY,
REGION_ID CHAR(8) FOREIGN KEY REFERENCES (T_REGION.REGION_ID),
DESCRIP TEXT);

CREATE TABLE T_NATIONAL_STATISTICS (
COUNTRY_ID CHAR(8),
STAT_YEAR SMALLINT CHECK VALUES BETWEEN(1900 AND 2020),
FORECAST BOOL,
GOVT_TYPE CHAR(4) FOREIGN KEY REFERENCES (T_GOVERNMENT.GOVT_TYPE),
POPULATION BIGINT,
GDP BIGINT,
FISH_CATCH BIGINT,
FISH_CONSUMED BIGINT,
WHEAT_HARVEST BIGINT,
WHEAT_CONSUMED BIGINT
CRUDE_PROD_BBL BIGINT, -- crude oil production,
CRUDE_CONSUM_BBL BIGINT, -- crude oil consumtion,
etc,
etc,
etc,
PRIMARY_KEY (COUNTRY_ID, STAT_YEAR));

(Syntax is a bit off, but you get the meaning...)
(I like human-readable keys.)

Whenever you find a new statistic you want to track, you add a new
column to T_NATIONAL_STATISTICS.

So, to (I think) get info on per-capita fish "usage" by year, by
country, for West Africa, you do:

SELECT NS.STAT_YEAR,
NS.COUNTRY_ID,
CAST(CAST(NS.FISH_CATCH AS DOUBLE)/NS.POPULATION
AS NUMERIC(8,2)) AS CATCH_PERCAP,
CAST(CAST(NS.FISH_CONSUMED AS DOUBLE)/NS.POPULATION
AS NUMERIC(8,2)) AS EATEN_PERCAP,
CAST(CAST(NS.FISH_CATCH AS DOUBLE)/NS.POPULATION) -
CAST(NS.FISH_CATCH AS DOUBLE)/NS.POPULATION)
AS NUMERIC(8,2)) AS FIST_SELF_SUFFICIENCY
FROM T_REGION R,
T_COUNTRY C,
T_NATIONAL_STATISTICS NS
WHERE R.REGION_ID = 'WESTAFRI'
AND C.REGION_ID = R.REGION_ID
AND NS.COUNTRY_ID = C.COUNTRY_ID;

You might want to left outer-join NS to C to indicate any west
African countries with missing statistics.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG8QGvS9HxQb37XmcRAprKAKCiDphSaLe1nTIf8tfaIwNCx/zZngCgpfeo
QF+ojV0/jYlLbLP+BDV86p4=
=7o+G
-----END PGP SIGNATURE-----