Rolling cumulative sum on MySQL query

Question

I have for example the following table :

date           sales
2016-02-01     2
2016-02-02     4
2016-02-03     4
2016-02-04     7
2016-02-05     3
2016-02-06     1
2016-02-07     6
2016-02-08     3

I would like to obtain a column that makes a rolling cumulative sum of the sales over x days. For example, over 3 days we would obtain :

date           sales    rolling_cumul_3_days
2016-02-01     2        2
2016-02-02     4        6
2016-02-03     4        10
2016-02-04     7        15
2016-02-05     3        14
2016-02-06     1        11
2016-02-07     6        10
2016-02-08     3        10

Is it possible to obtain in a single query, or do I have to run a cumulative sum for each date over the past x days and then aggregate the result ?

This is a general idea I have for this query (but definitely not correct...) :

    /* Initiate variables */
    SET @csum := 0;
    SET @date_cursor := '2016-02-01'

    /* Query for rolling result */
    SELECT date, sales, MAX(
        /* Query that cumulate sales on a three day interval for each date */
        SELECT (@csum := @csum + sales) as cumul_3_days
        FROM table 
        WHERE date <= @date_cursor
        AND date >= DATE_SUB(@date_cursor, INTERVAL -2 DAY);

        /* Reset variables for next date */
        SET @csum := 0;
        SET @date_cursor := DATE_ADD(@date_cursor, INTERVAL 1 DAY);
    ) AS rolling_cumul_3_days
    FROM table

You would have to have 3 variables: 2 for the rolling sums, and a counter to determine which variable to replace with the current amount. — Shadow, Feb 24 '16 at 16:49

score 0 · Accepted Answer · answered Feb 24 '16 at 16:48

Without variables:

 SELECT x.*
      , SUM(y.sales) 
   FROM my_table x 
   JOIN my_table y 
     ON y.date BETWEEN x.date - INTERVAL 3-1 DAY 
    AND x.date GROUP BY x.date;

If performance is an issue, then no doubt a variables-type solution will be forthcoming.

Rolling cumulative sum on MySQL query

1 Answers1