TIP : Calculate Moving Average

I had recent need to calculate a moving average from values in a SQLite table
and thought I’d share a method to do it

given:

  • SQLite table [myTable]
  • containing a Date or DateTime in SQLDate format [myDate]
  • a field to calculate average [myField]
SELECT myDate, 
       myField,
        (SELECT AVG(myField) FROM myTable b WHERE DATE(b.myDate,'-30 days')<=a.myDate) AS myAvg
    FROM myTable a

change ‘-30 days’ to whatever range is desired

this would make a good “just code” challenge app…

It’s one line of SQL… If you’re talking about the whole sugar tracking app, he admitted a few weeks ago that it won’t be of any use to Type 1 diabetics as a food log is out of the question

The code from Dave didn’t give me the results I expected, but this code did:

SELECT myDate, myField, (SELECT AVG(myField) FROM myTable b WHERE myDate BETWEEN date(a.myDate, '-30 days') AND date(a.myDate)) AS myAvg From myTable a

Some notes:

  • using ‘-30 days’ will give you an average of 31 days
  • the least you could have is an average of 2 days (using ‘-1 day’)
  • sorry if I made a mistake changing my real values to generic ones

Alberto… you may have needed to change it as you did, if your date field is not in YYYY-MM-DD format, then the DATE() function compensates for that (to a point)

Thank you Dave, as you know I’m new to programming.

As far as I can tell, my sqlite database was created with Fecha as DATE and the values are in YYYY-MM-DD format. I will keep looking as why your code didn’t work for me.

Dave, are you referring to the AND date(a.myDate) ?

This also works for me:

SELECT myDate, myField, (SELECT AVG(myField) FROM myTable b WHERE myDate BETWEEN date(a.myDate, '-30 days') AND a.myDate) AS myAvg From myTable a

Alberto… yes I was… DATE(a.myDate) would be redundant if myDate was already in SQLDate format.

But actually my code IS WRONG, and yours IS RIGHT …

Mine would use ANY date less that “a.myDate”… but not limited to the correct range.

See… even someone “new” can teach an old dog

along with the rest of the app : have a list of datas, store them in a sqlite database, and make a moving average live.

which is quite complex… it has a database with 8 tables, and 12 views, 16 screens, it creates user customized PDF reports along with configurable graphs, so it is way more that a “code challenge” :slight_smile: