MySQL moving average calculation using CASE

Question

How can i edit the ON operator part of my query below such that i would like the current code to work where id<4 (which is t2.id <= t1.id as shown below) so when t1 id=3, t2 is the cumulative id from id=1 to id=3 (as it is now).

but for id >3 I would like the ON operator to be (t2.id=t1.id>=t1.id-2 and <=t1.id) so when t1 id=4, t2.id should be between 2 and 4 inclusive. when t1 id =5, t2.id should be between 3 and 5 inclusive and so on.

I'm doing this because when i calculate col E for ids after id=3, i am only interested in getting the average of the previous 2 rows for C and D on a moving average.

Iam translating my excel formula into SQL so i know what is the correct values for col E.

My query has 2 sub queries and it updates column E. The table and correct data in EXCEL looks like this:

id  A     B      C      D       E
1  NULL NULL    NULL    NULL    NULL
2   4   6        1      1        1  
3   6   9      1.2     1.2      1.2
4   8   7      1.33    0.954    1.143
5   10  5      1.25    0.714    0.982
6   12  2      1.2     0.428    0.814

http://www.sqlfiddle.com/#!2/17a0ad/1

EXCEL formulas (notice that the formulas change after id=3 to a moving average):

id   C                     D                     E
2    =A2/AVERAGE(A1:A2)    =B2/AVERAGE(B1:B2)    =(C2+D2)/2
3    =A3/AVERAGE(A1:A3)    =B3/AVERAGE(B1:B3)    =(C3+D3)/2
4    =A4/AVERAGE(A2:A4)    =B4/AVERAGE(B2:B4)    =(C4+D4)/2
5    =A5/AVERAGE(A3:A5)    =B5/AVERAGE(B3:B5)    =(C5+D5)/2
6    =A6/AVERAGE(A4:A6)    =B6/AVERAGE(B4:B6)    =(C6+D6)/2

Here is my SQL query:

Update followers join 
(
SELECT t1.id ,ifnull(t1.A/AVG(t2.A),null) C ,ifnull(t1.B/AVG(t2.B),null) D
FROM    followers t1
JOIN    followers t2
ON  
case when t2.id < 4 then t2.id <= t1.id else t2.id<= t1.id and t2.id>=t1.id-2 end
group by t1.id 
) AS tt on(followers.id = tt.id)
SET E = (tt.C + tt.D)/2;

Although this query works, the numbers that i want for col E are not exactly correct. They are correct only for id<=4 but not for id=5 or id=6 in col E.

I believe my syntax for CASE by the ON operator might be wrong.

I ran this query in sql fiddle and got this result:

ID  A   B   E
1(null)(null)(null)
2   4   6   1
3   6   9   1.2
4   8   7   1.14
5   10  5   1.08
6   12  2   0.92

As we can see, the excel and sql output are different. Thanks,

Answer 1

I think the query that you want is a very slight modification:

Update followers join 
       (SELECT t1.id, ifnull(t1.A/AVG(t2.A),null) as C, ifnull(t1.B/AVG(t2.B),null) as D
        FROM followers t1 JOIN
             followers t2
             ON (case when t1.id < 4 then t2.id <= t1.id
----------------------------^ 
                      else t2.id<= t1.id and t2.id>=t1.id-2
                 end)
        group by t1.id 
       ) tt
       on followers.id = tt.id
    SET E = (tt.C + tt.D)/2;

You can express the on using basic boolean logic as:

on t2.id <= t1.id and (t1.id < 4 or t2.id >= t1.id - 2)