MySQL Forums
Forum List  »  Newbie

Re: Aggregate (GROUP BY) Function - Clarification?
Posted by: Peter Brawley
Date: November 09, 2017 05:20PM

Perhaps you've confused yourself. I think this query exposes what you're interested in ...

SELECT  
  x.year, 
  AVG(y.value) AS avg, 
  COUNT(*) AS n_years, 
  GROUP_CONCAT( Concat(x.id,'-',y.id) ) AS 'x-y IdPairs',
  GROUP_CONCAT( y.year ) As YrsAveraged
FROM test x
JOIN test y ON x.year BETWEEN y.year AND y.year + 5
GROUP BY x.year;
+------+--------+---------+-------------------------+-------------------------------+
| year | avg    | n_years | x-y IdPairs             | YrsAveraged                   |
+------+--------+---------+-------------------------+-------------------------------+
| 1996 | 7.0000 |       1 | 1-1                     | 1996                          |
| 1997 | 6.5000 |       2 | 2-1,2-2                 | 1996,1997                     |
| 1998 | 6.0000 |       3 | 3-1,3-2,3-3             | 1996,1997,1998                |
| 1999 | 5.5000 |       4 | 4-3,4-4,4-1,4-2         | 1998,1999,1996,1997           |
| 2000 | 5.0000 |       5 | 5-2,5-3,5-4,5-5,5-1     | 1997,1998,1999,2000,1996      |
| 2001 | 4.5000 |       6 | 6-1,6-2,6-3,6-6,6-4,6-5 | 1996,1997,1998,2001,1999,2000 |
| 2002 | 3.5000 |       6 | 7-4,7-5,7-7,7-2,7-3,7-6 | 1999,2000,2002,1997,1998,2001 |
| 2003 | 2.5000 |       6 | 8-3,8-6,8-4,8-5,8-7,8-8 | 1998,2001,1999,2000,2002,2003 |
+------+--------+---------+-------------------------+-------------------------------+

Notice that Between is inclusive, so ...x.yr Between y.yr And y.yr+5... gives a six-year moving average.

Options: ReplyQuote


Subject
Written By
Posted
Re: Aggregate (GROUP BY) Function - Clarification?
November 09, 2017 05:20PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.