Firstly my thanks for the ongoing help on this. It is proving quite the crash course in MySQL...
So I paired back the query to just the average calculation.
To ensure the smoothed average is calculated properly I limited the # rows based on a year range (rather than the global_id).
I set the range to the smallest necessary to calculate a correct 5 year average (i.e. 2015 to 2011).
This returned a result (finally):
SELECT x.global_id, x.species_code_c, x.year_c, sum(y.qty_taken) / count(y.qty_taken) AS cap_avg
FROM fao_global AS x
JOIN fao_global AS y
USING (species_code_c)
WHERE x.year_c-y.year_c BETWEEN 0 AND 4
AND x.year_c = 2015
ORDER BY x.year_c DESC, x.species_code_c ASC;
Unfortunately adding the second OUTER JOIN back into the query still fails to return a result.
I decided to just create a table. Then use the global_id to map the average calculated back into the fao_global table.
But what I'm failing to grasp is the syntax for setting the x.year_c as a date range in the WHERE clause in the above JOIN so I can reset x.year_c and re-run the query across 5 year sets (or even 10, 25 if I can get away with it). If I have to run it for each year it will take 132 runs to populate the table.
I've tried the WHERE statement as:
1. WHERE x.year_c-y.year_c BETWEEN 0 AND 4
AND x.year_c = [value]
2. WHERE x.year_c-y.year_c BETWEEN 0 AND 4
AND x.year_c >= [value]
3. WHERE x.year_c-y.year_c BETWEEN 0 AND 4
AND x.year_c BETWEEN [value AND [value]
But none are correct.
Would welcome the help very much...
Otherwise the addition DB detail you asked to see:
mysql> SHOW ENGINE INNODB STATUS\G
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_size | 268435456 |
+-------------------------------------+----------------+
Revised Table Structure:
| fao_global | CREATE TABLE `fao_global` (
`global_id` int(10) NOT NULL AUTO_INCREMENT,
`capture_id` int(10) DEFAULT NULL,
`aquaculture_id` int(10) DEFAULT NULL,
`year_c` smallint(4) DEFAULT NULL,
`year_a` smallint(4) DEFAULT NULL,
`species_code_c` varchar(3) DEFAULT NULL,
`species_code_a` varchar(3) DEFAULT NULL,
`iso_num_code_c` smallint(3) DEFAULT NULL,
`iso_num_code_a` smallint(3) DEFAULT NULL,
`area_code_c` tinyint(2) DEFAULT NULL,
`area_code_a` tinyint(2) DEFAULT NULL',
`environ_code_c` tinyint(1) DEFAULT NULL,
`environ_code_a` tinyint(1) DEFAULT NULL,
`qty_taken` int(20) DEFAULT NULL,
`symbol_c` varchar(2) NOT NULL,
`qty_prod` int(20) DEFAULT NULL,
`symbol_a` varchar(2) NOT NULL,
`fao_total` int(20) DEFAULT NULL,
`value_capture` decimal(15,2) DEFAULT NULL,
`value_aqua` decimal(15,2) DEFAULT NULL,
`value_total` decimal(15,2) NOT NULL,
PRIMARY KEY (`global_id`),
KEY `species_c` (`species_code_c`),
KEY `species_a` (`species_code_a`),
KEY `species_year_c` (`year_c`,`species_code_c`),
KEY `species_year_a` (`year_a`,`species_code_a`)
) ENGINE=InnoDB AUTO_INCREMENT=904402 DEFAULT CHARSET=utf8 |