Sqlite Moving Average

Trying to get the moving average on a per X months basis using SQLite. Problem is i cannot seem to figure or find anything remotely useful on how to aggregate with a X month(s) loo

Solution 1:

The "correct" answer to this seems to be "don't".

Since i'm using sqlite + php i figured it was simpler and more perfomant to do this with PHP instead, so i created a simple helper function for it:

/**
*   Moving average calculations
*   @param $arr: array with princing data
*   @param $ma: moving average; no. of rows
*   @param $nav: key for pricing data
*/functionsma($arr, $ma=6, $nav='nav'){


    foreach( $arras$key => $val ){

        $avg = 0;
        $average = 0;

        // check if look-back period, else set 0if( $key-$ma > -2 ) {

            // create lookback period$range = range(0,$ma-1);

            foreach($rangeas$r){
                $tmp = $key - $r;
                $avg = $avg + $arr[$tmp][$nav];
            }

            // round with 2 decimal point$average = round( ($avg/$ma) * 100 ) / 100;         

        }

        // add to current key$newkey = 'sma'.$ma;
        $arr[$key][$newkey] = $average;

    }


    return$arr;

} // sma()

Since we're using arrays in PHP the performance hit is negliable and probably less then it would be to calculate it using SQLite.

Screen of result:

Solution 2:

First, filter out any rows that are not the last in the month. Then use a scalar subquery to compute the moving average; the condition with the m3 subquery ensures that the AVG() subquery does not find any rows if there are not exactly six.

WITH months(id, nav, date) AS (
  SELECT id, nav, MAX(date)
  FROM nav
  GROUPBY strftime('%Y-%m', date)
)
SELECT id,
       nav,
       strftime('%Y-%m', date),
       (SELECTAVG(nav)
        FROM months AS m2
        WHERE m2.date BETWEENdate(months.date, 'start of month', '-5 months')
                          AND months.date
          AND (SELECTCOUNT(*)
               FROM months AS m3
               WHERE m3.date BETWEENdate(months.date, 'start of month', '-5 months')
                                 AND months.date
              ) =6
       ) AS average_6m
FROM months;