how to find moving average for all the days for the past 30 days with gap in date set to 0

Question

I want to find moving average of the past 30 days. for example

Today's date is 17/11/15, I have data only the days

Original Data

date           qty      
06/11/15        5       
08/11/15        7       
09/11/15        8       
10/11/15        12      
11/11/15        34      
15/11/15        45      
16/11/15        12      
17/11/15        7

Find 30 day Moving Average Data - Need to get movingAvg just like in below table -movingAvg column

date        qty     movingAvg

17/10/15        0       0   
18/10/15        0       0    
19/10/15        0       0    
20/10/15        0       0    
21/10/15        0       0   
22/10/15        0       0   
23/10/15        0       0   
24/10/15        0       0   
25/10/15        0       0    
26/10/15        0       0    
27/10/15        0       0   
28/10/15        0       0   
29/10/15        0       0   
30/10/15        0       0   
31/10/15        0       0    
01/11/15        0       0    
02/11/15        0       0   
03/11/15        0       0   
04/11/15        0       0    
05/11/15        0       0    
06/11/15        5       0.14    
07/11/15        0       1.4    
08/11/15        7       0.4    
09/11/15        8       0.67    
10/11/15        12      1.06    
11/11/15        34      2.2    
12/11/15        0       2.2    
13/11/15        0       2.2    
14/11/15        0       2.2
15/11/15        45      3.7    
16/11/15        12      4.1   
17/11/15        7       4.33

Please help me out with the mysql query for this or if there is any alternate for this.

`select avg(movingAvg) from table where date_sub(date, INTERVAL 30 DAY)` or something similar? — ʰᵈˑ, Nov 17 '15 at 09:19
I want to find movingAvg of second from the first table with any mysql query or plsql program — Goutham P N, Nov 17 '15 at 09:29

Abhishek Sharma · Answer 1 · 2015-11-17T09:37:57.840

0

Use MySQL str_to_date or INTERVAL

select date,qty,avg(qty) from table    
WHERE str_to_date(date,'%d/%m/%y') >= DATE(NOW()) - INTERVAL 30 DAY 
GROUP BY date

edited Nov 17 '15 at 09:37

answered Nov 17 '15 at 09:25

Abhishek Sharma

6,689
1
14
20

if there is any gap in the dates then this query is not working. – Goutham P N Nov 17 '15 at 09:29

Strawberry · Accepted Answer · 2015-11-17T09:45:22.777

There are lots of ways to skin this cat. Here's one, which uses a calendar table with all plausible dates (a depressingly small data set)...

CREATE TABLE calendar (dt DATE NOT NULL PRIMARY KEY);
-- INSERT values for all plausible dates. Lots of tutorials on this. 

CREATE TABLE my_table 
(date DATE NOT NULL
,qty INT NOT NULL
,PRIMARY KEY (date)
);

INSERT INTO my_table VALUES
('2015/11/06',5),
('2015/11/08',7),
('2015/11/09',8),
('2015/11/10',12),
('2015/11/11',34),
('2015/11/15',45),
('2015/11/16',12),
('2015/11/17',7);


SELECT a.* 
     , AVG(b.qty) average
  FROM 
     ( SELECT c.dt
            , COALESCE(x.qty,0) qty 
         FROM calendar c 
         LEFT 
         JOIN my_table x 
           ON x.date = c.dt 
        WHERE c.dt BETWEEN (SELECT MAX(date) - INTERVAL 31 DAY FROM my_table) AND (SELECT MAX(date) FROM my_table)
     ) a
  JOIN
     ( SELECT c.dt
            , COALESCE(x.qty,0) qty 
         FROM calendar c 
         LEFT 
         JOIN my_table x 
           ON x.date = c.dt 
        WHERE c.dt BETWEEN (SELECT MAX(date) - INTERVAL 31 DAY FROM my_table) AND (SELECT MAX(date) FROM my_table)
     ) b
    ON b.dt <= a.dt
 GROUP 
    BY a.dt;
+------------+------+---------+
| dt         | qty  | average |
+------------+------+---------+
| 2015-10-15 |    0 |  0.0000 |
| 2015-10-16 |    0 |  0.0000 |
| 2015-10-17 |    0 |  0.0000 |
| 2015-10-18 |    0 |  0.0000 |
| 2015-10-19 |    0 |  0.0000 |
| 2015-10-20 |    0 |  0.0000 |
| 2015-10-21 |    0 |  0.0000 |
| 2015-10-22 |    0 |  0.0000 |
| 2015-10-23 |    0 |  0.0000 |
| 2015-10-24 |    0 |  0.0000 |
| 2015-10-25 |    0 |  0.0000 |
| 2015-10-26 |    0 |  0.0000 |
| 2015-10-27 |    0 |  0.0000 |
| 2015-10-28 |    0 |  0.0000 |
| 2015-10-29 |    0 |  0.0000 |
| 2015-10-30 |    0 |  0.0000 |
| 2015-10-31 |    0 |  0.0000 |
| 2015-11-01 |    0 |  0.0000 |
| 2015-11-02 |    0 |  0.0000 |
| 2015-11-03 |    0 |  0.0000 |
| 2015-11-04 |    0 |  0.0000 |
| 2015-11-05 |    0 |  0.0000 |
| 2015-11-06 |    5 |  0.2174 |
| 2015-11-07 |    0 |  0.2083 |
| 2015-11-08 |    7 |  0.4800 |
| 2015-11-09 |    8 |  0.7692 |
| 2015-11-10 |   12 |  1.1852 |
| 2015-11-11 |   34 |  2.3571 |
| 2015-11-12 |    0 |  2.2759 |
| 2015-11-13 |    0 |  2.2000 |
| 2015-11-14 |    0 |  2.1290 |
| 2015-11-15 |   45 |  3.4688 |
| 2015-11-16 |   12 |  3.7273 |
| 2015-11-17 |    7 |  3.8235 |
+------------+------+---------+

Hi Stawberry, I need an improvment on this query where in the outer query the AVG is taking the values of entires till that day only but I need to take SUM(values)/30 where these 'values' should be of last 30 days. — Goutham P N, Nov 20 '15 at 09:49
Currently, this query joins on `b.dt <= a.dt`. You now want it to join on `b.dt BETWEEN a.dt - INTERVAL 30 DAY AND a.dt` — Strawberry, Nov 20 '15 at 09:54

Kickstart · Answer 3 · 2015-11-17T13:38:16.600

0

Generating a range of dates, and joining that against the original data. Joining twice, once to get that days qty and once to get the average for the last 30 days.

SELECT sub0.aDate, IFNULL(od1.qty, 0), AVG(IFNULL(od2.qty, 0))
FROM
(
    SELECT DATE_SUB( CURRENT_DATE(), INTERVAL (units.a + tens.a * 10) DAY) AS aDate, DATE_SUB( CURRENT_DATE(), INTERVAL ((units.a + tens.a * 10) + 29) DAY) AS prevDate
    FROM
    (SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN
    (SELECT 0 a UNION SELECT 1 UNION SELECT 2) tens
) sub0
INNER JOIN
(
    SELECT DATE_SUB( CURRENT_DATE(), INTERVAL (units.a + tens.a * 10) DAY) AS aDate
    FROM
    (SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN
    (SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tens
) sub1
ON sub1.aDate BETWEEN sub0.prevDate AND sub0.aDate
LEFT OUTER JOIN original_data  od1 ON od1.`date` = sub0.aDate
LEFT OUTER JOIN original_data  od2 ON od2.`date` = sub1.aDate
GROUP BY aDate, od1.qty
ORDER BY aDate

edited Nov 17 '15 at 13:38

answered Nov 17 '15 at 12:14

Kickstart

21,403
2
21
33

Fixed the typos now, and it should give a rolling 30 day average for the last 30 days (including today - hence subtracting 29 from today) – Kickstart Nov 17 '15 at 13:42
That looks more like it; the OP is a little vague on exactly how to count the 30. IFNULL though? Well, I guess it saves on typing! – Strawberry Nov 17 '15 at 13:45
I have taken it as 30 days prior to each day. Think you have taken it as up to 30 days from the first day 30 days ago for the average (hence for 2015-11-06 you get 0.2174, which is 5 averaged over 23 days). As to IFNULL, if it is just a single possible column it is what I would normally use; to me it is more intuitive (and I severely doubt the performance is usefully different). – Kickstart Nov 17 '15 at 13:52
Re IFNULL - I know, but it's a redundant function. COALESCE is ANSI and extendable. I never understood why they bothered creating IFNULL. But whatever floats your boat ;-) – Strawberry Nov 17 '15 at 14:09

Ajay Makwana · Answer 4 · 2015-11-17T10:39:29.173

-2

Try this....

SELECT * FROM  your_table
WHERE date >= DATEADD(day,-30, now()) AND date <= now()

Hope this helps..

edited Nov 17 '15 at 10:39

answered Nov 17 '15 at 09:20

Ajay Makwana

2,330
1
17
32

getdate(); is function which you have to make for getting server's date. otherwise you can ue NOW() – Ajay Makwana Nov 17 '15 at 10:07

how to find moving average for all the days for the past 30 days with gap in date set to 0

4 Answers4