Moving avg needs to be over 5yrs
I understand the BETWEEN 0 AND 4 is calculated something like:
2015-2015 = 0
2015-2014 = 1
2015-2013 = 2
2015-2012 = 3
2015-2011 = 4
My correlated SELECT returned a result (1hr 18mins)
Have been trying to re-cast the query to make it more efficient but also so the result (avg_total) is inserted into a column of the existing fao_global table
I have excluded the GROUP BY statement from the queries below. This may be critical?
My understanding is if there is more than one record for a given species in a given year GROUP BY will remove the duplicate records? But I need the duplicates to retain the granularity of the data e.g.:
id sp year avg_c iso area environ_c environ_a
231 ABX 2015 13498.10 158 61 4 2
232 ABX 2015 13498.10 158 61 4 3
Where I'm fuzzy is if the GROUP BY statement is critical to correctly calculating the moving avg by species.
Or if it is just an option and setting sp_x=sp_y in the ON statement is what matters.
I have a similar gap in my understanding on the avg() vs sum()/count(). Your use of avg() has thrown me a bit.
As an INSERT Query (using your suggested structure):
INSERT INTO `fao_global`(avg_total)
SELECT
AVG(y.fao_total) as avg_total
FROM fao_global x
JOIN fao_global y
ON x.species_code_c = y.species_code_c
AND y.year_c BETWEEN x.year_c AND x.year_c - 5
WHERE x.species_code_c is not null;
As an UPDATE Query:
UPDATE fao_global AS x
INNER JOIN
(
SELECT sum(y.fao_total) / count(y.fao_total) AS avg_total
FROM fao_global AS x
JOIN fao_global AS y
ON species_code_c = y.species_code_c
AND y.year_c BETWEEN x.year_c AND x.year_c - 5
) y
SET x.avg_total = y.avg_total;
I did not flip the index year/species order. Created as species_year.
Associated EXPLAIN TABLE (same for both Queries)
+----+-------------+-------+------------+------+------------------------------------------+----------------+---------+--------------------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------------------------------+----------------+---------+--------------------------+--------+----------+--------------------------+
| 1 | SIMPLE | y | NULL | ALL | species_year_c,species_c,species_total_c | NULL | NULL | NULL | 936765 | 100.00 | Using where |
| 1 | SIMPLE | x | NULL | ref | species_year_c,species_c,species_total_c | species_year_c | 12 | possbil.y.species_code_c | 650 | 11.11 | Using where; Using index |
+----+-------------+-------+------------+------+------------------------------------------+----------------+---------+--------------------------+--------+----------+--------------------------+