Question 239217 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to perform 5 year and 10 year moving average in sql?

MonthName Calendar_Year Total Number    Moving Average 5 Year   Moving Average 10 Year
April   2000    135     
April   2001    76      
April   2002    33      
April   2003    31      
April   2004    228     
April   2005    200 101 
April   2006    350 114 
April   2007    126 168 
April   2008    102 187 
April   2009    126 201 
April   2010    333 181 141
April   2011    137 207 161
April   2012    121 165 167
April   2013    36  164 175
April   2014    79  151 176
April   2015    272 141 161
April   2016    282 129 168
April   2017    96  158 161
April   2018    93  153 158
April   2019    181 164 158
April   2020    64  185 163
April   2021    144 143 136
April   2022    126 116 137
April   2023    236 122 137
August  2000    66      
August  2001    83      
August  2002    118     
August  2003    236     
August  2004    117     
August  2005    84  124 
August  2006    151 128 
August  2007    157 141 
August  2008    221 149 
August  2009    178 146 
August  2010    171 158 141
August  2011    154 176 152
August  2012    267 176 159
August  2013    164 198 174
August  2014    249 187 166
August  2015    149 201 180
August  2016    122 197 186
August  2017    247 190 183
August  2018    160 186 192
August  2019    73  185 186
August  2020    176 150 176
August  2021    164 156 176
August  2022    275 164 177
August  2023    52  170 178

The above table is obtained from excel, how to perform 5 year and 10 year moving average in sql, For example the 5 year moving April 2005 is the average of (April 2000 – April 2004) and for the April 2006 is the average of (April 2001 – 2005), the same for 10 year moving average, where for August 2010 is the average (Aug 2000 – 2000) I want to replicate the above format in sql query, with blanks for the ones that doesn’t have previous 5 or 10 year data?

Tags:

2

Answers


  1. 0

    You can try using
    avg() over(partition by month name order by year asc range between 5 preceding and 1 preceding)

    Login or Signup to reply.
  2. 0

    SQL has something called lag which is a godsend to tackle questions like moving averages, but since you’ve mentioned that you need to calculate the moving averages based on a specific month within each year, you need to ensure that the data is correctly aligned; for this, we use avg().

    I will give a rough idea on how to write the queries with the column names provided –

    WITH MovingAverages AS (
  SELECT
    MonthName,
    Calendar_Year,
    TotalNumber,
    AVG(TotalNumber) OVER (
      PARTITION BY MonthName
      ORDER BY Calendar_Year
      ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS MovingAverage5Year,
    AVG(TotalNumber) OVER (
      PARTITION BY MonthName
      ORDER BY Calendar_Year
      ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS MovingAverage10Year
  FROM YourTable
)
SELECT
  MonthName,
  Calendar_Year,
  TotalNumber,
  CASE
    WHEN MovingAverage5Year IS NOT NULL THEN ROUND(MovingAverage5Year, 2)
    ELSE ''
  END AS "Moving Average 5 Year",
  CASE
    WHEN MovingAverage10Year IS NOT NULL THEN ROUND(MovingAverage10Year, 2)
    ELSE ''
  END AS "Moving Average 10 Year"
FROM MovingAverages;

    We use a table expression MovingAverages to make a window function for the avg() and then partition the data by the month name and order it by the years, ROWS BETWEEN denotes the range of the sliding window.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search