MySQL : How to average the one column based on first 5 records of every each date of the record?

Question

Example :

Source columns are fld_daily_val, fld_date

Table Name : tbl_five_days_avg

id fld_daily_val    fld_date

1  3.3658974569     2016-02-01                                                                                   
2  2.215478659      2016-02-02                                                             
3  1.25984453412    2016-02-03                                               
4  0.2589631477     2016-02-04                                                       
5  9.2587894561     2016-02-05                                                                                                          
6  7.2587415935     2016-02-06                                                    
7  6.589789789      2016-02-07                                                        
8  5.3654951357     2016-02-08

Output Column name is fld_five_days_avg. We want the output's are the following

id    fld_daily_val    fld_date      fld_five_days_avg

1     3.3658974569     2016-02-01    3.3658974569   --No data before this date.                                                                 
2     2.215478659      2016-02-02    2.79068805795000 --id(1+2)/2                                                                        
3     1.25984453412    2016-02-03    2.280406883340000 --id(1+2+3)/3                                                                     
4     0.2589631477     2016-02-04    1.775045949430000 --id(1+2+3+4)/4                                                                        
5     9.2587894561     2016-02-05    3.271794650764000  --id(1+2+3+4+5)/5
6     7.2587415935     2016-02-06    7.2587415935 --should consider this is the first record after five record average completed.                                                            
7     6.589789789      2016-02-07    6.92426569125000 --id(6+7)/2                                                      
8     5.3654951357     2016-02-08    6.40467550606667 --id(6+7+8)/3

help us to improve the knowledge. Thanks in advance

Thanks,

Possible duplicate of [How to do a moving average on a range of dates in SQL?](http://stackoverflow.com/questions/13280677/how-to-do-a-moving-average-on-a-range-of-dates-in-sql) — Dave Cross, Aug 31 '16 at 09:09
I think it is possible Dave. Please give your valuable suggestion. — Logu, Aug 31 '16 at 09:35
I know it's possible. The post I linked to in my comment shows exactly how it is done. — Dave Cross, Aug 31 '16 at 09:44
Close enough that a decent programmer will be able to work it out though, right? :-) — Dave Cross, Aug 31 '16 at 10:08
You should get familiar with windowing concept. In MySQL there is no windwing function, but there is a possibility to act like this function does, using [GROUP_CONCAT](http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat) function that can [emulate window functions](http://dba.stackexchange.com/questions/40130/mysql-and-window-functions). Another concept that can be of use for you is [partitioning](http://dev.mysql.com/doc/refman/5.7/en/partitioning.html). Furthermore you may need laging columns concept (when you duplicate a column and move the values from the d — vlad.rad, Aug 31 '16 at 09:31

James Jithin · Answer 1 · 2016-09-01T11:18:06.393

2

Try this out:

SELECT 
    id,
    fld_daily_val,
    fld_date,
    @idcount:=CASE
        WHEN @idcount = 5 THEN 1
        ELSE @idcount + 1
    END,
    (@sumavg:=CASE
        WHEN @idcount = 1 THEN fld_daily_val
        ELSE fld_daily_val + @sumavg
    END) / @idcount fld_five_days_avg
FROM
    (SELECT @idcount :=0, @sumavg := 0.0) a,
    tbl_five_days_avg;

Output:

1   3.365897456900000   2016-02-01  1   3.3658974569
2   2.215478659000000   2016-02-02  2   2.7906880579499997
3   1.259844534120000   2016-02-03  3   2.28040688334
4   0.258963147700000   2016-02-04  4   1.77504594943
5   9.258789456100000   2016-02-05  5   3.271794650764
6   7.258741593500000   2016-02-06  1   7.2587415935
7   6.589789789000000   2016-02-07  2   6.92426569125
8   5.365495135700000   2016-02-08  3   6.404675506066667

For updated Logic

SELECT 
    id,
    fld_daily_val,
    fld_date,
    @idcount:=@idcount + 1,
    @firstval:=@secondval,
    @secondval:=@thirdval,
    @thirdval:=@fourthval,
    @fourthval:=@fifthval,
    @fifthval:=fld_daily_val,
    (@firstval + @secondval + @thirdval + @fourthval + @fifthval) / (CASE
        WHEN @idcount > 5 THEN 5
        ELSE @idcount
    END) fld_five_days_avg
FROM
    (SELECT 
        @idcount:=0,
            @firstval:=0.0,
            @secondval:=0.0,
            @thirdval:=0.0,
            @fourthval:=0.0,
            @fifthval:=0.0
    ) a,
    tbl_five_days_avg;

edited Sep 01 '16 at 11:18

answered Aug 31 '16 at 09:37

James Jithin

10,183
5
36
51

The output is exactly as needed by Logu, but with minor precision changes. – James Jithin Aug 31 '16 at 09:51
I think you need an ORDER BY clause – Strawberry Aug 31 '16 at 09:53
@James, Please see my following post in this – Logu Sep 01 '16 at 07:16
@Logu, you may update the question and not post an answer regarding the change in logic. Please find the updated answer with changes. – James Jithin Sep 01 '16 at 11:19
Hi James. I have posted the output(See Answer portion below) regarding the change in logic. Please see this link http://stackoverflow.com/questions/39268474/mysql-to-calculate-the-5-days-average-of-every-each-dates – Logu Sep 01 '16 at 11:59
James, Thank you. I will check and let you know. – Logu Sep 01 '16 at 12:34

MySQL : How to average the one column based on first 5 records of every each date of the record?

1 Answers1